Cascading Combo Box - Another angle on the subject.....

[kcdoell]

I have 5 cascading combo boxes on a form. Below is a sample of my vb in the first combo box:

Private Sub CboDivision_AfterUpdate()
 
'When the Division is selected, the appropriate Segment list will
'display in the drop down list of CboSegment
 
  With Me![cboSegment]
     If IsNull(Me!cboDivision) Then
        .RowSource = ""
      Else
        .RowSource = "SELECT DISTINCT tblSegment.SegmentID, " & _
         "tblSegment.SegmentName " & _
         "FROM TblLocationsMM INNER JOIN tblSegment " & _
        "ON TblLocationsMM.SegmentIDFK = tblSegment.SegmentID " & _
         "WHERE [DivisionIDFK]=" & Me!cboDivision
 
      End If
     Call .Requery
 
    'Have the first dropdown list of cboSegment visible
            If Me![cboSegment].ListCount > 0 Then
                Me![cboSegment] = Me![cboSegment].Column(0, 0)
 
    End If
        End With
 
End Sub 

I follow this method for the 4 other cascading combo boxes that are displayed on my form. Each selection adds an additional parameter to set the row source for the next combo box. I achieve this though my “Where” statement (See below):

 Private Sub cboSegment_AfterUpdate()
 
'First Make sure other combo boxes are null
Me!cboWrkReg = Null
Me!cboCreditReg = Null
Me!cboBrokerType = Null
 
'When the Segment is selected, the appropriate Working Region list will
'display in the drop down list of CboWrkReg
 
With Me![cboWrkReg]
   If IsNull(Me!cboSegment) Then
      .RowSource = ""
 
    Else
   .RowSource = "SELECT DISTINCT tblWrkRegion.WrkRegID, " & _
         "tblWrkRegion.WrkRegionName " & _
         "FROM TblLocationsMM INNER JOIN tblWrkRegion " & _
         "ON TblLocationsMM.WrkRegIDFK = tblWrkRegion.WrkRegID " & _
         "WHERE [DivisionIDFK]=" & Me!cboDivision & _
         "And [SegmentIDFK]=" & Me!cboSegment
 
    End If
    Call .Requery
 
    'Have the first dropdown list of cboWrkReg visible
            If Me![cboWrkReg].ListCount > 0 Then
                Me![cboWrkReg] = Me![cboWrkReg].Column(0, 0)
   End If
      End With
End Sub 

In the above example, the user would make a selection on my first combo box [cboDivision], now the second combo box [cboSegment] displays the first on the list based on the selection made in [cboDivision]. If the user does not actual click/select into [cboSegment] than my third combo box [cboWrkReg] is not going to have the correct drop down list.

If I do click/select [cboSegment] even though the first on the list is my choice the list choices in [cboWrkReg] are correct.

Now to my question, is there a simple code that I can put into my cboDivision_AfterUpdate that will not only display the first on the list but will trigger a click/select action so that the appropriate lists for subsequent combo boxes on the form will be correct?

I am thinking the solution I would apply to my other combo box AfterUpdate events but I am lost on how to solve...

Thanks for any ideas you may have.

Keith.

[PianoMan64]

You can simply use the OnChange Event for each of your combo boxes, as you change them, then you can trigger the event that will update the next set of control(s) on your form.

Hope that helps,

Joe P.

[kcdoell]

You can simply use the OnChange Event for each of your combo boxes, as you change them, then you can trigger the event that will update the next set of control(s) on your form.

Hope that helps,

Joe P.

So would I need the same coding in both the AfterUpdate and in the OnChange Event or eliminate the AfterUpdate coding and just simply move it to a OnChange event?

Thanks for the idea.

Keith.

[kcdoell]

In the end I used another technique by disabling the combo boxes (setfocus.enable = False), except for the first, until a selection had been made in the first.......................

I included a command button I called "Reset Selections" to give the user the ability to reset previous selections if they wanted to make other choices.

Keith.