relate response to one combo box (category) to secondary lists

[Guest]

I have a form already created. There is an entry where I can choose a
category from a combo box. I would like each of those categories bring up a
specific secondary category choice box. For example, if I choose the
"Personnel" category, I want a list of "personnel" options to choose from in
the next box (or something similar). So I would have primary categories of
"personnel," or "methods," etc., then under "personnel" would be "selection,"
"training," etc. "Methods" would have it's own secondary list to choose from.
Is there a relatively simple way to do this? Sorry if this is unclear, it's
been a really long day...

 

[Rick Brandt]

I have a form already created. There is an entry where I can choose a
category from a combo box. I would like each of those categories bring up a
specific secondary category choice box. For example, if I choose the
"Personnel" category, I want a list of "personnel" options to choose from in
the next box (or something similar). So I would have primary categories of
"personnel," or "methods," etc., then under "personnel" would be "selection,"
"training," etc. "Methods" would have it's own secondary list to choose from.
Is there a relatively simple way to do this? Sorry if this is unclear, it's
been a really long day...

The RowSource of your second ComboBox is a query that uses a reference to the
first ComboBox as filter criteria. The SQL would be something like...

SELECT SomeField FROM SomeTable
WHERE Category = Forms!NameOfForm!NameOfComboBox

You would issue a Requery on the second ComboBox in the AfterUpdate event of the
first to force the RowSource to refresh itself.

 

[Guest]

Thanks Rick, but that is still too cryptic for me. I am a novice at Access,
have not written any code, and am otherwise working from (useless) reference
books and making it up as i go along. My best guess is that I need to set up
a Parameter query within the form, so that when I select "personnel" in the
Category field combo box, a pop-up list or another combo box of "personnel"
subcategories opens to fill a subsequent Subcategory field??? Once I go to
build the query I'm lost.

 

[Rick Brandt]

Thanks Rick, but that is still too cryptic for me. I am a novice at
Access, have not written any code, and am otherwise working from
(useless) reference books and making it up as i go along. My best
guess is that I need to set up a Parameter query within the form, so
that when I select "personnel" in the Category field combo box, a
pop-up list or another combo box of "personnel" subcategories opens
to fill a subsequent Subcategory field??? Once I go to build the
query I'm lost.

Is the second list always pulling from the same source table and just
displaying a different subset of data based on the selection in the first
list or do you need the selection in the first list to produce secondary
lists that pull from completely different sources?

ComboBoxes have a RowSource property that is typically a query. This is
what provides the values you can choose from in the list. We basically need
that query to be one that responds differently depending on the choice made
in the other list. If the answer to my first question above is that the
choices in the second list are just different subsets from the same table
then this is fairly easy to set up. If you need the second list to
dynamically pull from completely different source tables then it gets a bit
more complicated.