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! 

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" wrote:

> 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,

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? 

"Jack Leach" wrote:

> 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" wrote:
> 
> > 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! 

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.  

"TheDrescher" wrote:

> 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? 
> 
> "Jack Leach" wrote:
> 
> > 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" wrote:
> > 
> > > 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!