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

[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:

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:

 
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,

[ADezii]

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:

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:

 
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:

Private Sub txtSendKeys_GotFocus()
Private Sub cboWrkReg_AfterUpdate()
  With Me![CboCreditReg]
    If IsNull(Me!CboWrkReg) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [CreditRegion] FROM TblCreditRegion WHERE [WrkRegID]=" & _
                    DLookup("[WrkRegID]", "tblWrkRegion", "[WrkRegionName] = '" & Me![cboWrkReg] & "'")
    End If
    Call .Requery
  End With
End Sub
End Sub

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

[ADezii]

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]

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]

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?

If Me![cboThree].ListCount > 0 Then
  Me![cboThree] = Me![cboThree].Column(0, 0)
End If

[kcdoell]

Is this what you are requesting?

If Me![cboThree].ListCount > 0 Then
  Me![cboThree] = Me![cboThree].Column(0, 0)
End If

ADezii:

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

  With Me![cboCreditReg]
   If IsNull(Me!cboWrkReg) Then
      .RowSource = ""
 
    Else
   .RowSource = "SELECT DISTINCT tblCreditRegion.CreditRegID, " & _
         "tblCreditRegion.CreditRegionName " & _
         "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
 

Thanks a million!

Keith.

[ADezii]

ADezii:

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

  With Me![cboCreditReg]
   If IsNull(Me!cboWrkReg) Then
      .RowSource = ""
 
    Else
   .RowSource = "SELECT DISTINCT tblCreditRegion.CreditRegID, " & _
         "tblCreditRegion.CreditRegionName " & _
         "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
 

Thanks a million!

Keith.

You are quite welcome, Keith.