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

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • kcdoell
    New Member
    • Dec 2007
    • 230

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

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

    [code=vb]
    Private Sub CboDivision_Aft erUpdate()

    'When the Division is selected, the appropriate Segment list will
    'display in the drop down list of CboSegment

    With Me![cboSegment]
    If IsNull(Me!cboDi vision) Then
    .RowSource = ""
    Else
    .RowSource = "SELECT DISTINCT tblSegment.Segm entID, " & _
    "tblSegment.Seg mentName " & _
    "FROM TblLocationsMM INNER JOIN tblSegment " & _
    "ON TblLocationsMM. SegmentIDFK = tblSegment.Segm entID " & _
    "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 [/code]

    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):

    [code=vb] Private Sub cboSegment_Afte rUpdate()

    'First Make sure other combo boxes are null
    Me!cboWrkReg = Null
    Me!cboCreditReg = Null
    Me!cboBrokerTyp e = 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!cboSe gment) Then
    .RowSource = ""

    Else
    .RowSource = "SELECT DISTINCT tblWrkRegion.Wr kRegID, " & _
    "tblWrkRegion.W rkRegionName " & _
    "FROM TblLocationsMM INNER JOIN tblWrkRegion " & _
    "ON TblLocationsMM. WrkRegIDFK = tblWrkRegion.Wr kRegID " & _
    "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 [/code]

    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_Aft erUpdate 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.
    Last edited by kcdoell; Jun 17 '08, 03:53 PM. Reason: Title change
  • PianoMan64
    Recognized Expert Contributor
    • Jan 2008
    • 374

    #2
    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.

    Comment

    • kcdoell
      New Member
      • Dec 2007
      • 230

      #3
      Originally posted by 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.
      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.

      Comment

      • kcdoell
        New Member
        • Dec 2007
        • 230

        #4
        In the end I used another technique by disabling the combo boxes (setfocus.enabl e = 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.

        Comment

        Working...