943,733 Members | Top Members by Rank

Web Development > Databases > MS Access and FileMaker Pro > Lookup column based on selection
Ad:
Permalink
Sep 20th, 2009
0

Lookup column based on selection

Expand Post »
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!
Reputation Points: 45
Solved Threads: 28
Posting Whiz in Training
Dukane is offline Offline
282 posts
since Oct 2006
Permalink
Sep 20th, 2009
0

Re: Lookup column based on selection

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.
Reputation Points: 27
Solved Threads: 29
Posting Whiz
timothybard is offline Offline
317 posts
since Mar 2007
Permalink
Sep 20th, 2009
0

Re: Lookup column based on selection

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)?
Reputation Points: 45
Solved Threads: 28
Posting Whiz in Training
Dukane is offline Offline
282 posts
since Oct 2006
Permalink
Sep 20th, 2009
0

Re: Lookup column based on selection

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.
Reputation Points: 45
Solved Threads: 28
Posting Whiz in Training
Dukane is offline Offline
282 posts
since Oct 2006
Permalink
Sep 20th, 2009
0

Re: Lookup column based on selection

What I had in mind was to use the On Change event of Combo182 to update the Row Source of the Model combo box.
Reputation Points: 27
Solved Threads: 29
Posting Whiz
timothybard is offline Offline
317 posts
since Mar 2007
Permalink
Sep 20th, 2009
0

Re: Lookup column based on selection

Hmmm...Thank you for the suggestions so far. How do I go about doing that?
Reputation Points: 45
Solved Threads: 28
Posting Whiz in Training
Dukane is offline Offline
282 posts
since Oct 2006
Permalink
Sep 20th, 2009
1

Re: Lookup column based on selection

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.
Reputation Points: 27
Solved Threads: 29
Posting Whiz
timothybard is offline Offline
317 posts
since Mar 2007
Permalink
Sep 22nd, 2009
0

Re: Lookup column based on selection

Thank you, you have been a huge help!
Reputation Points: 45
Solved Threads: 28
Posting Whiz in Training
Dukane is offline Offline
282 posts
since Oct 2006

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MS Access and FileMaker Pro Forum Timeline: AutoNymber datatype in ACCESS
Next Thread in MS Access and FileMaker Pro Forum Timeline: mulitple search queries to display records from access database


Tags
access, lookup



About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC