Conditional Selections based on a Field

[TheDrescher]

I'm trying to create a field that populates a choice of selections based on
what was selected in a prior field. For examples sake: categories and
sub-categories. I'm having trouble with coding the form to say "based on
your selection in the category field, your options in the sub-category field
are..." How would I go about this? Thanks!

 

[Jack Leach]

You mean like a combo box? Well... for the subcatagory, each possible choice
is ideally stored in a table that has a field that also references when the
catagory is. For instance:

tblCatagories might have a field called fldCatagory with these records:
Fruits
Veggies
Grains

and tblSubCats would have two fields, a link to it's parent (fldCat) and the
selection item (fldFood)...
Fruits|Apples
Fruits|Oranges
Veggies|Tomatoes
Veggies|Carrots
Grains|Bread
Grains|Oats

So if you have two combo boxes, the first has a recordsource directly
related to tblCatagories.

The second one is dynamic based on the first combo... it's recordsource will
be tblSubCats, but you want to filter them based on the selected catagory.

To do this, set the AfterUpdate event of the first combo to requery the
second combo based on the selection (you'll also want this in the Current
event of the form so the selections are correct for each record you navigate
to).


Private Sub cboCatagories_AfterUpdate()
Me.cboSubCat.RowSource = _
"SELECT * FROM tblSubCats " & _
"WHERE fldCat = """ & Nz(Me.cboCat, "") & """"
End Sub


something along those lines...

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[TheDrescher]

Jack,

Thanks for the information! I'm running into a snag when I test the form:
When I select a category, I recieve a "Compile error: method or data member
not found".
The tables and info I created are the same as in your example. The code as
written in my VBA window is:

Private Sub Categories_AfterUpdate()
Me.cboSubCat.RowSource = "SELECT * FROM tblSubCats " & "WHERE FldCat = """
& Nz(Me.cboCat, "") & """"

End Sub

When the error populates, it highlights the .cboCat near the end. Any
suggestions?

 

[TheDrescher]

Okay, I think I've worked most of way through the code to get 90% there, but
when I bring up the category, I now get this error:
"The record source 'SELECT*FROM tblSubCats WHERE FldCategory = "1"'
specified on this form or report does not exist."

I've tried doing some renaming and shuffling, but I keep coming back with
the same error. I even created a relationship between the category fields of
the two tables and i'm still getting the same message. The two related
tables are 'Categories' and 'SubCats' with the related field between the two
simply named 'Category". Is there something else I'm missing? Thanks again
for your help.