Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tab Orders (A2k SR1a)

    I have a funny situation with three combo's with their taborders.
    for arguments sake I shall call them:
    cmb1
    cmb2
    cmb3

    The problem lies between cmb 2 & 3

    cmb2 has a not in list event which opens another form.
    cmb3 has an "If IsNull ([FieldName]) with a msgbox statement on enter or has focus (I've tried both).

    The tab orders are:
    cmb1 = 1
    cmb2 = 2
    cmb3 = 3
    If cmb2 recognises data, the tab or focus automatically goes to cmb3
    But
    If cmb2 doesn't recognise the data, it opens the Not in list form correctly ready for data input but, the msgbox from cmb3 fires because the tab order is the next in line.

    Hope I'm making sense.

    Is their a way to pause the tab order or should I create some code on combo3 to check if the not in list form has opened. ??
    Attached Images Attached Images

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tab Orders (A2k SR1a)

    What I think is happening is that cbo2 identifies that it needs to open the form when you have already tried to move to cbo3. You probably put the code to open the other form in the lost focus event of cbo2.

    Put a DoCmd.CancelEvent immediately after opening the other form.
    David Grugeon
    Brisbane Australia

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Tab Orders (A2k SR1a)

    If you take this out of combo3 does it work OK?
    <pre>cmb3 has an "If IsNull ([FieldName]) with a msgbox statement on enter or has focus .</pre>

    What is the above line there for?
    Can you find another way to achieve whatever it is doing?
    Regards
    John



  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Orders (A2k SR1a)

    Here's the set order in which every thing gets fired.

    The user will create a new record which prompts "What kind of record is it going to be.
    "Customer","Insurer","Salvage" etc etc.
    From selecting "Insurer" the database will expect an Insurer address from cmb3 when it receives the focus or on entering.
    cmb1 has the focus first which expects a client.
    cmb2 has the focus next which expects a vehicle registration.
    cmb3 finally receives focus and expects (if "Insurer was selected") an Insurer.

    If a Vehicle registration already exist's for this client, the cmb populates and go's onto the next cmb which is cmb3.
    The "Is Null" event prompts the user for a parameter which is either "Fault" or "non Fault" (a different action will happen dependant on the user's selection. In the perfect scenario, this works perfect.
    Here's where the problem happens.

    If their is no vehicle registration, the "not in list"event for cmb2 opens a popup form for the user to enter details, hopefully to setfocus on cmb3 when closed.
    The popup form opens and the "Is Null" msgbox fires also.
    Although cmb3 hasn't received the focus, the tab order has.
    If I set the tab order for cmb3 to no, the actions in all situations are correct.
    I thought of leaving the tab to no, but it would cause problems later.
    So "Yes", if I remove the "Is Null" event, All would be ok.
    Here's the code from all combo's if its any help.
    ########################## CMB1 ################################
    Private Sub cmbClientCode_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String, x As Integer
    Dim LinkCriteria As String
    Dim strMsgTitle As String
    strMsgTitle = "!!"
    x = MsgBox("Do You Want To Add This Client To The Database?", vbYesNo, strMsgTitle)
    If x = vbYes Then
    strSQL = "Insert Into tblclientcode ([clientcode]) values ('" & NewData & "')"
    'MsgBox strSQL
    CurrentDb.Execute strSQL, dbFailOnError
    LinkCriteria = "[clientcode] = '" & Me!cmbClientCode.Text & "'"
    DoCmd.OpenForm "frmclientcodepopup", , , LinkCriteria
    Forms!frmClientCodePopup!txtLoadFrom = "frmDetails"
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    End If
    End Sub
    ################################################## ##

    ################# CMB2 #############################
    Private Sub cmbRegistration_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String, x As Integer
    Dim LinkCriteria As String
    Dim strMsgTitle As String
    strMsgTitle = "!!"
    x = MsgBox("Do You Want To Add This Vehicle To The Database?", vbYesNo, strMsgTitle)
    If x = vbYes Then
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmVehicleSelection"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    strSQL = "Insert Into tblVehicleDetails ([Registration]) values ('" & NewData & "')"
    'MsgBox strsql
    CurrentDb.Execute strSQL, dbFailOnError
    LinkCriteria = "[Registration] = '" & Me!CmbRegistration.Text & "'"
    DoCmd.OpenForm "frmVehicleAdmin", , , LinkCriteria
    Forms!frmVehicleAdmin!EstimateNo = EstimateNo
    Forms!frmVehicleAdmin!Supp = Supp
    Forms!frmVehicleAdmin.Visible = False
    Forms!frmVehicleSelection.SetFocus
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    End If
    End Sub
    ################################################## ##
    ############ CMB3 ###############################
    Private Sub cmbInsurerCode_Enter()

    If Me.JobType = "Customer" Then GoTo finish
    If IsNull([cmbInsurerCode]) Then
    Dim intButSelected As Integer, intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String

    strMsgPrompt = "Is This A None Fault Claim"
    strMsgTitle = "!!"

    intButType = vbYesNo + vbDefaultButton1
    intButSelected = MsgBox(strMsgPrompt, intButType, strMsgTitle)

    If intButSelected = vbYes Then
    Me!cmbInsurerCode.Locked = False
    Me!cmbInsurerCode.SelStart = Me!cmbInsurerCode.SelLength
    Me.NoneFault.Visible = True
    Me.NoneFault = True
    ' YES button code...
    Else
    Me!cmbInsurerCode.SelStart = Me!cmbInsurerCode.SelLength
    ' NO button code...
    End If
    End If
    finish:
    Exit Sub
    End Sub
    ################################################## #######
    ##################### CMB3 AGAIN ########################

    Private Sub cmbInsurerCode_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String, x As Integer
    Dim LinkCriteria As String
    Dim strMsgTitle As String
    Dim stDocName As String
    strMsgTitle = "!!"
    stDocName = "frmInsurerQuickFind"
    x = MsgBox("Do You Want To Add This Insurer To The Database?", vbYesNo, strMsgTitle)
    If x = vbYes Then
    strSQL = "Insert Into tblinsurer ([insurercode]) values ('" & NewData & "')"
    'MsgBox strsql
    CurrentDb.Execute strSQL, dbFailOnError
    LinkCriteria = "[insurercode] = '" & Me!cmbInsurerCode.Text & "'"
    DoCmd.OpenForm "frminsureradmin", , , LinkCriteria
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    DoCmd.OpenForm stDocName
    End If
    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tab Orders (A2k SR1a)

    Hi Dave,

    Try opening the forms (frmVehicleSelection etc.) with the WindowMode argument set to acDialog. Then, the user has to close the form before other things will be processed. For example:

    stDocName = "frmVehicleSelection"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog

    or, if you use named arguments, you don't have to put in the extra commas:

    stDocName = "frmVehicleSelection"
    DoCmd.OpenForm FormName:=stDocName, WhereCondition:=stLinkCriteria, WindowMode:=acDialog

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Orders (A2k SR1a)

    Hi Hans.

    Your acdialog command seems to work (pretty much like Modal) but caused a different problem
    "one step forward, 10 back"
    So I moved the combo's around so as not cause the clash of codes.
    My frmVehicle was nested with frmVehicleSelection and the acDialog was blocking the opening of one of them.

    The code below as you can see is highlited in yellow because after the acDialog.
    I have ( as you can see ) put the acDialog elsewhere, where their is only one form to pop up.
    This is the only stumbling block now.
    What do you think.
    Attached Images Attached Images

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Tab Orders (A2k SR1a)

    It would seem that the form frmClientCodePopup is no longer in memory.
    Also what is the following command doing?
    Forms![frmClientCodePopup!txtLoadFrom= "FrmDetails"
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Tab Orders (A2k SR1a)

    Dave
    what about just reordering it so that the insurer is put in before the client, and the rego!
    <pre>The user will create a new record which prompts "What kind of record is it going to be.
    "Customer","Insurer","Salvage" etc etc.
    From selecting "Insurer" the database will expect an Insurer address from cmb3 when it receives the focus
    or on entering.
    cmb3 first receives focus and expects (if "Insurer was selected") an Insurer.
    cmb1 has the focus first which expects a client.
    cmb2 has the focus next which expects a vehicle registration.

    </pre>

    Hopefully what then comes after cmb2 does not have an onenter event!
    Regards
    John



  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tab Orders (A2k SR1a)

    The purpose of WindowMode:=acDialog was to prevent the code from running on while the popup form was open. As a consequence, the instruction
    Forms!frmClientCodePopup!txtLoadFrom = ...
    doesn't get executed until the popup form has been closed. Therefore, a reference to frmClientCodePopup is not valid any more. Possible workarounds:
    1. <LI>Pass the name of the calling form in the OpenArgs argument of DoCmd.OpenForm:

      DoCmd.OpenForm "frmClientCodePopup", , , stLinkCriteria, , acDialog, "frmDetails"

      In the code behind frmClientCodePopup, you can retrieve this using Me.OpenArgs. You can use this directly, or you can set txtLoadFrom in the OnLoad or OnOpen event of the popup form:

      Me.txtLoadFrom = Me.OpenArgs

      <LI>Alternatively, you can declare a global variable of type string in a standard module, and set this to "frmDetails" just before opening the popup form. Then, you can use the value of the global variable in the code behind the popup form.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Orders (A2k SR1a)

    Thank you All for support and comments.
    I will be trying once more today to solve this dilemma, along with all your views.
    I'll let you know how I get on.

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Orders (A2k SR1a)

    Thanks Hans.
    Hmmmmmmm, plenty to think about now.
    The text "txtLoadFrom" is used for the following reason.
    The form frmClientCodePopUp needs to be used twice loaded from different forms.
    The other form which calls it will put frmFaultDetails in the unbound txtLoadFrom.
    So when the frmClientCodePopUp closes, it sets focus back to the correct form, dependant on what is in the text:-
    Simple but effective.

    Private Sub Form_Close()
    On Error GoTo errtrap
    If Me.txtLoadFrom = "frmDetails" Then
    Forms!frmDetails.SetFocus
    Forms!frmDetails.Refresh
    Forms!frmDetails!cmbInsurerCode.SetFocus
    End If

    If Me.txtLoadFrom = "frmFaultDetails" Then
    Forms!frmFaultDetails.SetFocus
    Forms!frmFaultDetails.Refresh
    Forms!frmFaultDetails!EstimateNo.SetFocus
    End If
    Exit Sub
    errtrap:
    End Sub

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tab Orders (A2k SR1a)

    Hi Dave,

    You can use either of the two workarounds I mentioned in my previous reply for this. Both give you a way of passing the name of the "calling form".

    Caveat: In Access 97, the OpenArgs argument may be cleared by the time the Close event of the form occurs. I don't know if this is still true in Access 2000. So I usually store the value of OpenArgs in a variable or control in the Open or Load event of the form.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tab Orders (A2k SR1a)

    I developed that habit as well in 97, although it wasn't usually necessary, just more reliable. The OpenArgs seems to be more stable in the later versions, but it will of course be cleared by any unhandled error, just as the variables will be.
    Charlotte

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Orders (A2k SR1a)

    Well I've given up, I've taken a different approach.

    On my main form, I have created two check box's
    NoneFault
    Fault.

    On the cmbInsurerCode's exit I want to make one of them visible dependant on selection.
    Off the fly, does the below code look ok


    'Check to see if one of the chks is true
    If me.NoneFault=False & me.Fault=False then
    msgBox "Is This a None Fault Claim" (vbYesNo)
    If vbYes Then
    Me.NoneFault=true
    Me.NoneFault.Visible=true
    Me.Fault=false
    Me.Fault.visible=false
    End If
    End If

    If vbNo Then
    Me.Fault = true
    Me.Fault.visible=true
    me.NoneFault=false
    Me.NoneFault.visible=false
    End If
    ' If At least one of the chks is true then supress msgbox

  15. #15
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Orders (A2k SR1a)

    This seems to have done the trick:
    Thanks all for the help:

    Private Sub cmbInsurerCode_Exit(Cancel As Integer)
    If Me.NoneFault = False And Me.Fault = False Then

    Dim intButSelected As Integer, intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String

    strMsgPrompt = "Is This A None Fault Claim"
    strMsgTitle = "!!"

    intButType = vbYesNo + vbDefaultButton1
    intButSelected = MsgBox(strMsgPrompt, intButType, strMsgTitle)

    If intButSelected = vbYes Then
    Me.NoneFault = True
    Me.NoneFault.Visible = True
    Me.Fault = False
    Me.Fault.Visible = False

    ' YES button code...
    Else
    Me.Fault = True
    Me.Fault.Visible = True
    Me.NoneFault = False
    Me.NoneFault.Visible = False

    ' NO button code...
    End If
    End If
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •