Linking list/combo boxes to limit input options

[etislost]

Is there a way to link/connect two list/combo boxes so that the values available in the second depend upon the option chosen in the first? In other words, if the first has two options, "a" and "b", and the second box has other values, some associated with "a" and some with "b", is there a way to get only the ones for "a" to show up in the second box if "a" is chosen in the first box?

like a = 1 or 2
b = 3,4, or 5.
I don't want someone able to enter a in the first box and 4 in the second. How do you limit this?

Thank you for the help,
Steve P.

 

[gol4]

see this faq

faq702-681

 

[Islwyn]

Hi,
I've had a similar problem and used something like this in the "row source" for the second box (the numbers):

SELECT [YourNumberAndLetterList].[Number] FROM YourNumberAndLetterList WHERE (([YourNumberAndLetterList].[YourLetter])=[Forms]![TheNameOfYourForm]![Number]);

where the [Forms]![TheNameOfYourForm]![Number] refers to the name of the Number box (not necessarily the field name);

Then use a requery macro to refresh the data in the Number box when necessary

Hope this works or helps
Cheers
Islwyn

 

[etislost]

My table is titled "cardtrial". My form is titled "NEWCMNTCRD."

My first combo box is bound to a field named "Project" with choices of Lewisville Lake and Grapevine Lake. The second is bound to a field named "Park Name" with choices of Hickory Creek Park, Oakland Park, Westlake Park, Murrell Park, and Rockledge Park. The first three relate to Lewisville; Murrell and Rockledge relate to Grapevine.

Please help with the SQL code for this. Also, does the SQL code go in the Row Source line or do I have to write it somewhere after you click the three dots and get the query maker thing?

I am relatively new to Access.

Thank you for your help,
Steve P.

 

[etislost]

I forgot to add that I have no experience whatsoever with SQL and don't understand where the variables in the code come from.

Thanks,
Steve P.

 

[Islwyn]

I know what it feels like to be a beginner!

If you look at the properties of the combo boxes in design view (click on them then right click properties) you'll see a load of options. Under data you can see row source. Just insert the following code into the row source box

SELECT [cardtrial].[Park Name] FROM cardtrial WHERE (([cardtrial].[Project])=[Forms]![NEWCMNTCRD.]![Project]);

You may need to check thet the "Project" combo box is actually called Project rather than something else. Do this by clicking on the properties of that box and check the "Name" on the "All" panel. If it is different then change the last word in the code above to that Name.

To update the data in the second box select event on the properties pannel, then OnGotFocus then macrobuilder. Place requery in the Action box and put the name of the combo box (as above) in the Control Name box. This should update the second box whenever you go to it
Hope this works and helps

Cheers

Islwyn