Lookup column based on selection

[Dukane]

Hi, I am new to Access 2007. I have two columns in my table, Make and Model. The Make column is a lookup from a Makes table which has different makes of computer (Dell, Toshiba, etc...) in it. I also have a Model column adjacent to it which is set up in a similar way, it looks up to a table named Models and has different model names (Satellite, Dimension, etc...).

Is there any way so that when I select a Make, that when I go to choose the Model, only the models from the particular Make are displayed? For example, if I select DELL as the Make, only the Dell models are available as a choice in the Model column?

I have tried to make a query, but I don't think it is what I am going for.

Thank you for your help in advance!

[timothybard]

From your post, it sounded as if you used the table directly to enter and view data. I would recommend that you use a form to modify and view the data.

With the form, you can add a make combo box and a model combo box. When someone selected the make, you can change the row source of the model combo box to display only the units by that company. That should get you in the right direction; let me know if you need additional details.

[Dukane]

Thank you, this clarifies this a lot! I will try your suggestion out and see how it goes.

How do I change the row source of the Model combo box dynamically (that is, when the Make combo box changes)?

[Dukane]

Here is my SQL for the Makes combo box:

SELECT Models.[Model Name]
FROM Models
WHERE (((Models.Make)=[Combo182]))
ORDER BY Models.[Model Name];

Combo182 is the combo box on my form which corresponds to the Make field.

[timothybard]

What I had in mind was to use the On Change event of Combo182 to update the Row Source of the Model combo box.

[Dukane]

Hmmm...Thank you for the suggestions so far. How do I go about doing that?

[timothybard]

Are you familiar with VBA? If so, the code you need should be something along the lines of this:

Private Sub Combo182_Change()
Combo183.rowsource = "SELECT Models.[Model Name] FROM Models WHERE (((Models.Make)=" & Me.Combo182 & ")) ORDER BY Models.[Model Name]"
End Sub

Please note that this code assumes the combobox for the model is combo183.

[Dukane]

Thank you, you have been a huge help!