469,609 Members | 2,241 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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

kcdoell
230 100+
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CboDivision_AfterUpdate()
  2.  
  3. 'When the Division is selected, the appropriate Working Region list will
  4. 'display in the drop down list of CboWrkReg
  5.  
  6.     With Me![CboWrkReg]
  7.       If IsNull(Me!CboDivision) Then
  8.         .RowSource = ""
  9.       Else
  10.         .RowSource = "SELECT [WrkRegionName] " & _
  11.                      "FROM TblWrkRegion " & _
  12.                      "WHERE [DivisionID]=" & Me!CboDivision
  13.       End If
  14.      Call .Requery
  15. End With
  16.  
  17. End Sub
  18.  

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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cboWrkReg_AfterUpdate()
  3. 'When the Working Region is selected, the appropriate Credit Region list will
  4. 'display in the drop down list of CboCredit Reg
  5.  
  6.     With Me![CboCreditReg]
  7.       If IsNull(Me!CboWrkReg) Then
  8.         .RowSource = ""
  9.       Else
  10.         .RowSource = "SELECT [CreditRegion] " & _
  11.                      "FROM TblCreditRegion " & _
  12.                      "WHERE [WrkRegID]=" & Me!CboWrkReg
  13.       End If
  14.      Call .Requery
  15. End With
  16. End Sub
  17.  
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,
Mar 11 '08 #1
7 2288
ADezii
8,800 Expert 8TB
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CboDivision_AfterUpdate()
  2.  
  3. 'When the Division is selected, the appropriate Working Region list will
  4. 'display in the drop down list of CboWrkReg
  5.  
  6.     With Me![CboWrkReg]
  7.       If IsNull(Me!CboDivision) Then
  8.         .RowSource = ""
  9.       Else
  10.         .RowSource = "SELECT [WrkRegionName] " & _
  11.                      "FROM TblWrkRegion " & _
  12.                      "WHERE [DivisionID]=" & Me!CboDivision
  13.       End If
  14.      Call .Requery
  15. End With
  16.  
  17. End Sub
  18.  

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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cboWrkReg_AfterUpdate()
  3. 'When the Working Region is selected, the appropriate Credit Region list will
  4. 'display in the drop down list of CboCredit Reg
  5.  
  6.     With Me![CboCreditReg]
  7.       If IsNull(Me!CboWrkReg) Then
  8.         .RowSource = ""
  9.       Else
  10.         .RowSource = "SELECT [CreditRegion] " & _
  11.                      "FROM TblCreditRegion " & _
  12.                      "WHERE [WrkRegID]=" & Me!CboWrkReg
  13.       End If
  14.      Call .Requery
  15. End With
  16. End Sub
  17.  
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtSendKeys_GotFocus()
  2. Private Sub cboWrkReg_AfterUpdate()
  3.   With Me![CboCreditReg]
  4.     If IsNull(Me!CboWrkReg) Then
  5.       .RowSource = ""
  6.     Else
  7.       .RowSource = "SELECT [CreditRegion] FROM TblCreditRegion WHERE [WrkRegID]=" & _
  8.                     DLookup("[WrkRegID]", "tblWrkRegion", "[WrkRegionName] = '" & Me![cboWrkReg] & "'")
  9.     End If
  10.     Call .Requery
  11.   End With
  12. End Sub
  13. End Sub
Mar 11 '08 #2
kcdoell
230 100+
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.
Mar 11 '08 #3
ADezii
8,800 Expert 8TB
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.
Mar 11 '08 #4
kcdoell
230 100+
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.
Apr 24 '08 #5
ADezii
8,800 Expert 8TB
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?
Expand|Select|Wrap|Line Numbers
  1. If Me![cboThree].ListCount > 0 Then
  2.   Me![cboThree] = Me![cboThree].Column(0, 0)
  3. End If
Apr 24 '08 #6
kcdoell
230 100+
Is this what you are requesting?
Expand|Select|Wrap|Line Numbers
  1. If Me![cboThree].ListCount > 0 Then
  2.   Me![cboThree] = Me![cboThree].Column(0, 0)
  3. End If
ADezii:

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

Expand|Select|Wrap|Line Numbers
  1.   With Me![cboCreditReg]
  2.    If IsNull(Me!cboWrkReg) Then
  3.       .RowSource = ""
  4.  
  5.     Else
  6.    .RowSource = "SELECT DISTINCT tblCreditRegion.CreditRegID, " & _
  7.          "tblCreditRegion.CreditRegionName " & _
  8.          "FROM TblLocationsMM INNER JOIN tblCreditRegion " & _
  9.          "ON TblLocationsMM.CreditRegIDFK = tblCreditRegion.CreditRegID " & _
  10.          "WHERE [WrkRegIDFK]=" & Me!cboWrkReg
  11.  
  12.     End If
  13.  
  14.     Call .Requery
  15.  
  16.     'Have the first dropdown list of cboCreditReg visible
  17.             If Me![cboCreditReg].ListCount > 0 Then
  18.                 Me![cboCreditReg] = Me![cboCreditReg].Column(0, 0)
  19.     End If
  20.       End With
  21.  
Thanks a million!

Keith.
Apr 24 '08 #7
ADezii
8,800 Expert 8TB
ADezii:

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

Expand|Select|Wrap|Line Numbers
  1.   With Me![cboCreditReg]
  2.    If IsNull(Me!cboWrkReg) Then
  3.       .RowSource = ""
  4.  
  5.     Else
  6.    .RowSource = "SELECT DISTINCT tblCreditRegion.CreditRegID, " & _
  7.          "tblCreditRegion.CreditRegionName " & _
  8.          "FROM TblLocationsMM INNER JOIN tblCreditRegion " & _
  9.          "ON TblLocationsMM.CreditRegIDFK = tblCreditRegion.CreditRegID " & _
  10.          "WHERE [WrkRegIDFK]=" & Me!cboWrkReg
  11.  
  12.     End If
  13.  
  14.     Call .Requery
  15.  
  16.     'Have the first dropdown list of cboCreditReg visible
  17.             If Me![cboCreditReg].ListCount > 0 Then
  18.                 Me![cboCreditReg] = Me![cboCreditReg].Column(0, 0)
  19.     End If
  20.       End With
  21.  
Thanks a million!

Keith.
You are quite welcome, Keith.
Apr 25 '08 #8

Post your reply

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

Similar topics

6 posts views Thread by visionstate | last post: by
reply views Thread by Solution2021 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.