How to get a 2nd cascading combo box to be in synch with the 1st ? 1

[actionjack]

I have 2 cascading combo boxes which together create an Account Number which is the value of:

combo box 1 + combo box 2
---------------- --------------------
Office Number + CustomerNumber

In the form load event, I used the following RowSource values:

If (Me.Frame380.Value) = 1 Then
strRowSource = "SELECT distinct left([Acct Number],4) as officenumber " _
& "from tblFlACSE order by left([Acct Number],4)"
Else
strRowSource = "SELECT distinct left([Acct Number],4) as officenumber " _
& "from tblSpACSE order by left([Acct Number],4)"
End If

Me.cmbOffice.RowSource = strRowSource

If (Me.Frame380.Value) = 1 Then
strRowSource1 = "SELECT distinct right([Acct Number],6), left([Acct Number],4) from tblFlACSE " _
& " WHERE NOT EXISTS (SELECT * FROM tblHistEx WHERE tblHistEx.[Acct Number]=tblFlACSE.[Acct Number] " _
& " AND tblHistEx.[Prop CD]=tblFlACSE.[Prop CD] AND tblHistEx.[User 1]=tblFlACSE.[User 1]) " _
& " order by left([Acct Number],4), right([Acct Number],6); "
Else
strRowSource1 = "SELECT distinct right([Acct Number],6), left([Acct Number],4) from tblSpACSE " _
& " WHERE NOT EXISTS (SELECT * FROM tblHistEx WHERE tblHistEx.[Acct Number]=tblSpACSE.[Acct Number] " _
& " AND tblHistEx.[Prop CD]=tblSpACSE.[Prop CD] AND tblHistEx.[User 1]=tblSpACSE.[User 1]) " _
& " order by left([Acct Number],4), right([Acct Number],6); "
End If

Me.cmbCustomer.RowSource = strRowSource1
----------------------------------------------------------
In the cmbOffice After Update command, I used the following code:

Private Sub cmbOffice_AfterUpdate()

If Len(Nz(Me.cmbOffice)) > 0 Then
strComboOffice = cmbOffice
Me.cmbCustomer.Requery
End If

If Len(Nz(Me.cmbCustomer)) > 0 Then
strComboCustomer = cmbCustomer
End If

End Sub
------------------------------------------------------------------------------------------------------------------------------
My problem is that when the 1st combo box, cmbOffice is selected, the choices are for example Office Numbers:

101
102
106

Then, if you select for ex: Office Number 101 in the first combo box,

Then make a selection from the 2nd combo box of Customer Numbers, the choices are as follows:

023853
040661
123626
124589
169425
020561
018098
083785

Thus, the choices for the Customer Number showing in the 2nd combo box are showing all the Customer Numbers for every office.

I am trying to figure out how to display ONLY the Customer Numbers for the Office selected in comb box 1.
Office 101
-----------
023853
040661
123626
124589
169425

Office 102
------------
020561

Office 106
--------------
018098
083785

 

[ClydeData]

See this sample

http://www.rogersaccesslibrary.com/download3.asp?SampleName=CascadingComboBoxes.mdb

Hope this helps
Jimmy