Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    9

    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)
    End Sub

    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.

    Did I made any mistakes here?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by ngtb View Post
    Did I made any mistakes here?
    Just a little one. I suppose that Customer is a Text value, then:
    Code:
    Me.ProductOrdered.RowSource = "SELECT ProductName FROM" & _
    " tblProducts WHERE Customer = '" & _
    Me.CustomerName & _
    "' ORDER BY ProductName"
    Have a nice day!

  3. #3
    Join Date
    May 2011
    Posts
    9
    Finally get the thing work! Thank you so much

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •