Unanswered: Failed to Synchronizing Combo Boxes on Forms
Hi can anyone tell me whats wrong with my VB code?
I am trying to Synchronizing 2 Combo Boxes on Form. i did some research online and follow exactly the instructions but still can't get the result.
First I got 2 tables: tblCustomers and tblProducts.
Table 1 tblCustomers contain 2 columns: Customer ID and Company
Table 2 tblProducts contain 3 columns: Product ID, Customer and ProductName. In this table, the column Customer is set to lookup value from tblCustomers, mean whenever i add a new product, I can select the name of the customer (Company) for that particular product.
Then I created 3rd table: tblOrders, which contain 2 columns, one called: CustomerName and another called: ProductOrdered. I convert both column to combo box in a form, and set the row source for CustomerName to as below:
SELECT [tblCustomers].[Customer ID], [tblCustomers].[Company] FROM tblCustomers ORDER BY [Company]
Then I go to the "After Update" event procedures of CustomerName and write the VB code as below:
Option Compare Database
Private Sub CustomerName_AfterUpdate()
' Update the row source of the ProductOrdered combo box
' when the user makes a selection in the CustomerName
' combo box.
Me.ProductOrdered.RowSource = "SELECT ProductName FROM" & _
" tblProducts WHERE Customer = " & _
Me.CustomerName & _
" ORDER BY ProductName"
Me.ProductOrdered = Me.ProductOrdered.ItemData(0)
I expect when I select the CustomerName, for example ABC company, I should be able to limit the list of products appear in the ProductOrdered combo box to those only related to ABC company, but actually I see nothing in the ProductOrdered combo box, it's blank.