Cascading to a third combo box.... on a form

**ADezii** · Mar 11 '08, 02:27 AM

Originally posted by kcdoell

I have three tables:

One for the Division location:

tblDivision
DivisionID = Autonumber
DivisionName = Text

One for the Working Region:
tblWrkRegion
WrkRegID = Autonumber
WrkRegionName = Text
DivisionID = Number (This is for my one to many relationship; a Division can have many different working regions)

One for the Credit Region:
tblCreditRegion
CreditRegID = Autonumber
CreditRegion = Text
WrkRegID = Number (This is for my one to many relationship; a working region can have many different credit regions)

I followed the cascading script method posted on this website and created a form, put 3 combo boxes on it:

cboDivision (bound on column 1, row source tblDivision, column count set to 2)
cboWrkReg (bound on column 1, row source blank, column count set to 1)
cboCreditReg (bound on column 1, row source blank, column count set to 1)

In the after update event of my cboDivision I placed the following code:

Code:

Private Sub CboDivision_AfterUpdate()

'When the Division is selected, the appropriate Working Region list will
'display in the drop down list of CboWrkReg

    With Me![CboWrkReg]
      If IsNull(Me!CboDivision) Then
        .RowSource = ""
      Else
        .RowSource = "SELECT [WrkRegionName] " & _
                     "FROM TblWrkRegion " & _
                     "WHERE [DivisionID]=" & Me!CboDivision
      End If
     Call .Requery
End With

End Sub

This worked great. The Divisions would populate and when I clicked on cboWrkReg the working region list would be there. So….. I needed my last combo box to act and behave in the same way so In the after update event of my cboWrkReg I placed the following code:

Code:

 
Private Sub cboWrkReg_AfterUpdate()
'When the Working Region is selected, the appropriate Credit Region list will
'display in the drop down list of CboCredit Reg

    With Me![CboCreditReg]
      If IsNull(Me!CboWrkReg) Then
        .RowSource = ""
      Else
        .RowSource = "SELECT [CreditRegion] " & _
                     "FROM TblCreditRegion " & _
                     "WHERE [WrkRegID]=" & Me!CboWrkReg
      End If
     Call .Requery
End With
End Sub

My problem is that when I click on the cboCreditReg I get a pop up dialogue box that is asking for me to input a number for the selection that was made in cboWrkReg. I have noticed that if I selected Atlanta in cboWrkReg; the row source is set to the following: SELECT [CreditRegion] FROM TblCreditRegion WHERE [WrkRegID]=Atlanta

If I key in the ID number for Atlanta the drop down box (cboCreditReg) will populate with the appropriate list. If I do nothing it will be blank or give me a syntax error message.

I have tried several things like changing bound columns with no luck. Does anybody have any idea where I am going wrong? I thought I solved this issue before I introduced a third cascading combo box.

Any help would be greatly appreciated.

Thanks,

I think I see the problem in that Me![cboWrkReg] returns the Work region Name and Not the Work Region ID, try:
[CODE=vb]
Private Sub txtSendKeys_Got Focus()
Private Sub cboWrkReg_After Update()
With Me![CboCreditReg]
If IsNull(Me!CboWr kReg) Then
.RowSource = ""
Else
.RowSource = "SELECT [CreditRegion] FROM TblCreditRegion WHERE [WrkRegID]=" & _
DLookup("[WrkRegID]", "tblWrkRegi on", "[WrkRegionName] = '" & Me![cboWrkReg] & "'")
End If
Call .Requery
End With
End Sub
End Sub[/CODE]

**kcdoell** · Mar 11 '08, 03:03 AM

Unbelievable, that was it. I really want to thank you. This problem was driving me crazy and I could not get myself out of this hole until you came around.

Thanks a million!

Keith.

**ADezii** · Mar 11 '08, 12:36 PM

Originally posted by kcdoell

Unbelievable, that was it. I really want to thank you. This problem was driving me crazy and I could not get myself out of this hole until you came around.

Thanks a million!

Keith.

You are quite welcome, Keith.

**kcdoell** · Apr 24 '08, 02:12 PM

Originally posted by ADezii

You are quite welcome, Keith.

ADezii:

I am not too sure you will get this message but one of my users has asked me to do something else with the cascading combo box that I thought you may have some insight...

Basically, everything works great but the list in the third box only displays when the user clicks into the combo box. In some cases, the third combo box may only have one choice to choose from. Is there a way that I can get the first on the list to display without the user having to click the third combo box to see visually the dropdown list/choices to pick?

Hope all is well,

Keith.

**ADezii** · Apr 24 '08, 04:55 PM

Originally posted by kcdoell

ADezii:

I am not too sure you will get this message but one of my users has asked me to do something else with the cascading combo box that I thought you may have some insight...

Basically, everything works great but the list in the third box only displays when the user clicks into the combo box. In some cases, the third combo box may only have one choice to choose from. Is there a way that I can get the first on the list to display without the user having to click the third combo box to see visually the dropdown list/choices to pick?

Hope all is well,

Keith.

Assuming the name of the 3rd Combo Box is cboThree (ingenious name, heh), place the following code in the AfterUpdate() Event of cboTwo, after the Row Source for cboThree has been created. If cboThree contains at least 1 entry, then the 1st entry in cboThree (1st Column, 1st Row) will be visible in the Text protion of the Combo Box. Is this what you are requesting?
[CODE=vb]
If Me![cboThree].ListCount > 0 Then
Me![cboThree] = Me![cboThree].Column(0, 0)
End If[/CODE]

**kcdoell** · Apr 24 '08, 08:14 PM

Originally posted by ADezii

Is this what you are requesting?
[CODE=vb]
If Me![cboThree].ListCount > 0 Then
Me![cboThree] = Me![cboThree].Column(0, 0)
End If[/CODE]

ADezii:

That was exactly what I was looking for! You were spot on!! Here was my end solution:

[code=vb]
With Me![cboCreditReg]
If IsNull(Me!cboWr kReg) Then
.RowSource = ""

Else
.RowSource = "SELECT DISTINCT tblCreditRegion .CreditRegID, " & _
"tblCreditRegio n.CreditRegionN ame " & _
"FROM TblLocationsMM INNER JOIN tblCreditRegion " & _
"ON TblLocationsMM. CreditRegIDFK = tblCreditRegion .CreditRegID " & _
"WHERE [WrkRegIDFK]=" & Me!cboWrkReg

End If

Call .Requery

'Have the first dropdown list of cboCreditReg visible
If Me![cboCreditReg].ListCount > 0 Then
Me![cboCreditReg] = Me![cboCreditReg].Column(0, 0)
End If
End With
[/code]

Thanks a million!

Keith.

**ADezii** · Apr 25 '08, 01:24 AM

Originally posted by kcdoell

ADezii:

That was exactly what I was looking for! You were spot on!! Here was my end solution:

[code=vb]
With Me![cboCreditReg]
If IsNull(Me!cboWr kReg) Then
.RowSource = ""

Else
.RowSource = "SELECT DISTINCT tblCreditRegion .CreditRegID, " & _
"tblCreditRegio n.CreditRegionN ame " & _
"FROM TblLocationsMM INNER JOIN tblCreditRegion " & _
"ON TblLocationsMM. CreditRegIDFK = tblCreditRegion .CreditRegID " & _
"WHERE [WrkRegIDFK]=" & Me!cboWrkReg

End If

Call .Requery

'Have the first dropdown list of cboCreditReg visible
If Me![cboCreditReg].ListCount > 0 Then
Me![cboCreditReg] = Me![cboCreditReg].Column(0, 0)
End If
End With
[/code]

Thanks a million!

Keith.

You are quite welcome, Keith.

Cascading to a third combo box.... on a form

Cascading to a third combo box.... on a form

Comment

Comment

Comment

Comment

Comment

Comment

Comment