How to create dependent form in MS Access based on Drop Down List

Question

I want help in creating a pickup list in which there is a list of choices you pick and corresponding to the choice you choose the secondary form loads with its own listed fields. EG. I have created 3 tables (Brand, Toyota, BMW), and each table has its own set of fields in them. I want to Open Main Form(Brand) and create a drop-down list that shows Toyota and BMW). when I choose Toyota it should open the Toyota form as a subform inside the Brand Form and the same goes for when you choose BMW. I am new to access and would really appreciate anyone's valuable input.

Answer 1

I also reccomend "normalizing" to a single table with a "makeId" column and a "make" table. But it is possible to do what you are asking in Access if both the "Toyota" table and the "BMW" table have the same columns:

1. Create a datasheet form with the wizard based on either table.
2. Create a blank form with a combo box and subform control.
3. Set the source of the combo box to your "Brand" table.
4. Set the source of the subform to your datasheet form.
5. Change the recordsource of the subform on the combobox change event:

    Private Sub brandBox_Change()
        subform.form.RecordSource = "SELECT * FROM " & brandBox.Value
        subform.Requery
    End sub

I don't recommend it but is possible.