dynamic query?

[Kent McPherson]

I have database in which 1 field called sector has multiple values (e.g. A,
B, C, D) and a field call industry which is a subfield to sector. So sector
A can have mulitiple industries as can sector B and sector C etc. I have a
table defined where the data looks like:
Sector Industry
A industry_1
A industry_2
A industry_3
B industry_1
B industry_2
B industry_3
C industry_1
C industry_2
C industry_3

On a data entry form, when sector A is chosen in the first field, I want the
combo box that displays the industry to only show industries valid for
sector A. I've been scanning the help files and an Access book I have but
haven't found the magic yet. Any help would be appreciated. Thanks!

 

[Jeff Boyce]

Kent

Do a search on "Cascading Comboboxes".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Kent McPherson]

Thanks for the pointer. That led me to this document on MS's website called
Basing One Combo Box on Another Sample Database.

http://www.microsoft.com/downloads/...A1-3C4E-4371-81E4-F9347B7A1DD7&displaylang=en

I downloaded this database to see how they did it. They used SQL Select
statements to dynamically select the data. I tried to mimic what was done in
this database and I'm close to what I want. But I have a problem where the
2nd combo box doesn't update after I change the selection in the 1st combo
box unless I either go to the properties of the 2nd combo box and click on
the "..." of the Row Source property to bring up the SQL Statement Query
Builder box and then select the View button or I can close the form
altogether and reopen it and the 2nd combo box updates.

In the Sample Database, the 2nd combo box updates dynamically as soon as you
change the choice in the 1st box. I'm not sure why mine is not working.
Any ideas on what to look for?

 

[Kent McPherson]

Well I figured it out with the help of my brother who knows Access a lot
better than I. I needed to add a refresh to the after event processing of
the 1st combo box so that the query on the 2nd would rerun and update.