By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 862 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

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

kcdoell
100+
P: 230
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
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 374
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
100+
P: 230
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
100+
P: 230
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.