Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Question Unanswered: How to make relation between multiple dropdown list

    Hi there,

    I am using two Lookup fields to get values for multiple dropdown list from a table. An example of the source table is:

    ID , Manufacturer , Model
    1 , Toyota , Corolla
    2 , Toyota , Camry
    ... , ..... , ....
    41 , Ford , Focus
    42 , Ford , Galaxy
    ... , ...... , ....

    Similarly, there will be another table for states and cities within the same state.

    In a different table I use the values from above table in a form of Lookup. The user first select the manufacturer from the 'manufacturer' dropdown and then can only see and select the models from the already selected manufacturer is the previous column.

    My table is currently showing all the models regardless of what manufacturer is selected and it also shows duplicated manufacturer. Is there a way to limit second column to show only the related models and remove the duplicates for the manufacturer (another word, user should see only one Toyota entry in second table)?

    Many thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First never open a table directly for managing the data it contains, always use forms as interfaces between users and the tables.

    What you ask for is called "cascading comboboxes" and many solutions are available here and in many other places. Basically, you change the Rowsource property of the second (dependent) combobox according to the selection made in the first. There are other methods to achieve the same goal but this is probably the easiest, both to understand and to implement.

    Tbl_Manufacturers:
    ID (AutoNumber, Primary Key)
    Manufacturer (Text)

    Tbl_Models:
    ID (AutoNumber, Primary Key)
    FK_Manufacturer (Long integer, Foreign Key to Tbl_Manufacturers.ID)
    Model (Text)

    RowSource property for Combo_1:
    Code:
    SELECT ID, Manufacturer FROM Tbl_Manufacturers
    AfterUpdate event handler for Combo_1 (the bound column is the first one: ID)
    Code:
    Private Sub Combo_1_AfterUpdate()
    
        Me.Combo_2.RowSource = "SELECT ID, Model FROM Tbl_Models WHERE FK_Manufacturer = " & Me.Combo_1.Value
        
    End Sub
    Have a nice day!

Tags for this Thread

Posting Permissions

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