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.
[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.
Comment