How to synchronize 2 combo boxes to filter data in 2nd depending on value in 1st ?

[Yousaf Shah]

Hello everybody
Thanks to Mr Lysander who guided me in building my data base. I have tried to synchronize two combo boxes named “Province” and “City” by method provided in Access Help so that I could filter the entries of ‘City’ when ‘Province’ were selected in “Province” combo box but I did not succeed.
I have got Table “City” with fields ‘City ID’, ‘ProvinceID’ (Linked with ID of Table “Province”) and ‘City’; and a Table “Province” with fields ‘ID’ and ‘Province’. In the form for data entry, I put the code in AfterUpdate property of “Province” following code;

Private Sub Province_AfterUpdate()

' Update the row source of the City combo box
' when the selection is made in the Province
' combo box.
Me.City.RowSource = "SELECT City FROM" & _
" City WHERE Province = " & Me.Province & _
" ORDER BY City"

Me.City = Me.City.ItemData(0)
End Sub

But I get an error. I derived this code from Access Help as given below;

Access Developer Reference
How to: Synchronize Two Combo Boxes on a Form
"This topic illustrates how to synchronize two combo boxes so that when you select an item in the first combo box, the selection limits the choices in the second combo box. For example, you may want the products displayed in a combo box to be limited to the category selected in another combo box.
In this example, the second combo box is filled with the results of an SQL statement. This SQL statement finds all the products that have a CategoryID that matches the category selected in the first combo box.
Whenever a category is selected in the first combo box, its AfterUpdate event procedure sets the second combo box's RowSourceType property. This refreshes the list of available products in the second combo box. Without this procedure, the contents of the second combo box would not change.
Private Sub cboCategories_AfterUpdate()

' Update the row source of the cboProducts combo box
' when the user makes a selection in the cboCategories
' combo box.
Me.cboProducts.RowSource = "SELECT ProductName FROM" & _
" tblProducts WHERE CategoryID = " & Me.cboCategories & _
" ORDER BY ProductName"

Me.cboProducts = Me.cboProducts.ItemData(0)
End Sub
"

Can anybody help me out solve this issue???

[beacon]

Hi Yousaf,

Do you have a fields in your City and Province tables called City and Province or are the fields CityID and ProvinceID?

If you don't have a field called City in your City table or a field called Province in your Province table, then I think the problem lies with the SQL query you're using for the row source of the combo box. Instead of:

Me.City.RowSource = "SELECT City FROM" & _
" City WHERE Province = " & Me.Province & _
" ORDER BY City"
 

...it should be this:

Me.City.RowSource = "SELECT CityID FROM" & _
" City WHERE ProvinceID = " & Me.Province & _
" ORDER BY CityID"
 

Hope this helps and good luck,
beacon