How do I write a Record / Row specific query ?

[Wease]

In my form I have Make & Model combo boxes. What I want to have happen is when a user selects a make (i.e. Honda or Yamaha), the model combo box will list only models associated with the Make selected. I have built a table called Make_Model that lists all the combinations of makes and models. I then have a query built that returns the models listed in my Make_Model database only if the Make Combo box matches the Make row in the Make_Model database:

SELECT MAKE_MODEL.MODEL
FROM MAKE_MODEL INNER JOIN TCTRI_MEMBERS ON MAKE_MODEL.MAKE = TCTRI_MEMBERS.PRIMARY_ATV_MAKE
ORDER BY MAKE_MODEL.MODEL;

When I have only one record in my database with the Make row populated, the query correctly returns the appropriate model. When I update the second record's Make, the query matches the models for each Make row (2) stored in my database. I want the query to only run against the Make row for which the form is updating. So If my form is updating the 2nd row in my database, the query should only return a match for the Make in the 2nd row. Instead, while I'm updating the 2nd row, the query runs against the value stored in all rows of the database. How can I fix this ?

 

[JoeMiller]

You need to add criteria into the query returning the Makes to the 2nd combo box on your form. The structure of the criteria is this:

[Forms]![MyFormName]![MyModelComboBoxName]

Now the last thing you have to do is make the Make combobox update with the correct Makes when the model combobox changes. What we're going to do is add a quick statement in VBA to do it. Click on the [MyModelComboBoxName] and go to its properties. Click on the Event tab, then click the three dots next to "After Update"; select Code Builder. Now we're going to issue a Requery command to Make combobox with this code:

Me.[MyMakeComboBoxName].Requery

Obviously you'll have to change my control name's to match yours. But that's basically all you have to do. I also suggest that you read RickSpr's FAQ on basing one combo box on another.

How do I limit the contents of one combo box based on the selection in another?
faq702-681 Joe Miller
joe.miller@flotech.net