By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,856 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,856 IT Pros & Developers. It's quick & easy.

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

P: 23
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???
Feb 27 '11 #1
Share this Question
Share on Google+
1 Reply


beacon
100+
P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. Me.City.RowSource = "SELECT City FROM" & _
  2. " City WHERE Province = " & Me.Province & _
  3. " ORDER BY City"
  4.  
...it should be this:
Expand|Select|Wrap|Line Numbers
  1. Me.City.RowSource = "SELECT CityID FROM" & _
  2. " City WHERE ProvinceID = " & Me.Province & _
  3. " ORDER BY CityID"
  4.  
Hope this helps and good luck,
beacon
Feb 27 '11 #2

Post your reply

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