468,234 Members | 1,684 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,234 developers. It's quick & easy.

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

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub CboDivision_AfterUpdate()
  2.  
  3. 'When the Division is selected, the appropriate Segment list will
  4. 'display in the drop down list of CboSegment
  5.  
  6.   With Me![cboSegment]
  7.      If IsNull(Me!cboDivision) Then
  8.         .RowSource = ""
  9.       Else
  10.         .RowSource = "SELECT DISTINCT tblSegment.SegmentID, " & _
  11.          "tblSegment.SegmentName " & _
  12.          "FROM TblLocationsMM INNER JOIN tblSegment " & _
  13.         "ON TblLocationsMM.SegmentIDFK = tblSegment.SegmentID " & _
  14.          "WHERE [DivisionIDFK]=" & Me!cboDivision
  15.  
  16.       End If
  17.      Call .Requery
  18.  
  19.     'Have the first dropdown list of cboSegment visible
  20.             If Me![cboSegment].ListCount > 0 Then
  21.                 Me![cboSegment] = Me![cboSegment].Column(0, 0)
  22.  
  23.     End If
  24.         End With
  25.  
  26. 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):

Expand|Select|Wrap|Line Numbers
  1.  Private Sub cboSegment_AfterUpdate()
  2.  
  3. 'First Make sure other combo boxes are null
  4. Me!cboWrkReg = Null
  5. Me!cboCreditReg = Null
  6. Me!cboBrokerType = Null
  7.  
  8. 'When the Segment is selected, the appropriate Working Region list will
  9. 'display in the drop down list of CboWrkReg
  10.  
  11. With Me![cboWrkReg]
  12.    If IsNull(Me!cboSegment) Then
  13.       .RowSource = ""
  14.  
  15.     Else
  16.    .RowSource = "SELECT DISTINCT tblWrkRegion.WrkRegID, " & _
  17.          "tblWrkRegion.WrkRegionName " & _
  18.          "FROM TblLocationsMM INNER JOIN tblWrkRegion " & _
  19.          "ON TblLocationsMM.WrkRegIDFK = tblWrkRegion.WrkRegID " & _
  20.          "WHERE [DivisionIDFK]=" & Me!cboDivision & _
  21.          "And [SegmentIDFK]=" & Me!cboSegment
  22.  
  23.     End If
  24.     Call .Requery
  25.  
  26.     'Have the first dropdown list of cboWrkReg visible
  27.             If Me![cboWrkReg].ListCount > 0 Then
  28.                 Me![cboWrkReg] = Me![cboWrkReg].Column(0, 0)
  29.    End If
  30.       End With
  31. 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.
Jun 17 '08 #1
3 3535
PianoMan64
374 Expert 256MB
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.
Jun 17 '08 #2
kcdoell
230 100+
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.
Jun 19 '08 #3
kcdoell
230 100+
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.
Jun 19 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by cognoscento | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.