Possible to link fields in a form to narrow selections?

[Mel]

I'm working on making a form more user friendly -
What I'd like to do is to link multiple fields and to narrow selections in
field2 and field3 based off of what a user selects in field1.

Example:
I have field1 Principal with 10 options.
I have field2 PrimaryCategory with 20 options, however, there are only 2
valid options per Principal.
I have field3 Brand with 50 options, however, there are only 2-5 valid
options per Principal.
Right now, all options for field2 and field3 show up in the combo box
regardless of what is chosen in field1.
What I'd like is once user makes selection in field1, for field2 and field3
to automatically show ONLY the valid options for that specific selection in
field1.

Is this possible? Can anyone give me a starting point for figuring out how
to set this up? Would an ifthen statement work here - and if so, do I put
that in the code on field2 and field3?

I'm working in both Access 2003 and 2007, depending on if I'm doing the work
at the office or at home.

I appreciate any help!

 

[Tom van Stiphout]

On Sat, 24 Apr 2010 07:48:03 -0700, Mel

Absolutely this is possible.
The query for the second dropdown should "look back" at the first one
to see which Principal was selected, and only select rows for that
principal. Something like:
select CategoryID, CategoryName
from tblCategories
where PrincipalID = Forms!myForm!myPrincipalDropdown

Then in the Principal dropdown's AfterUpdate event write one line:
myCategoryDropdown.Requery

(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

 

[Beetle]

This is commonly known as "Cascading Combo Boxes".

A google search on that term will return a multitude of links
to pages explaining how to set them up.

 

[Mel]

Thanks so much to both of you!
Knowing what this is called should help me find resources to accomplish this
- and your example should help as well, Tom.

 

[Mel]

I have successfully set this up for my table -
However, I do have one glitch that I'm hoping for some advise on.

When I am in the form and make my selection for "Principal", I am
immediately sent to the very first form record.
If I navigate back to the new record and do not change the "Principal"
selection, then I can continue to enter data and the two fields that update
from "Principal" do show the correct selections.
If the "Principal" selection is changed here, then I am sent back to the
first record once again.

In the After Update line of Principal, I have simply "Requery".
(I have tried with no luck to go into the "Requery" macro and specify the
specific fields that I want to requery - not sure if this is related to my
issue here.)

Thanks again for any suggestions/tips/etc.