Hi, I know you can do this in a form but I want to know if you can base one 
lookup column on another in a table: I have a table that associates 
businessIDs with categories (tblAssocCat).  In this same table I have 2 
lookup columns: main category (from another table, tblMainCategories) and sub 
category (from tblSubCategories), where I want only the sub categories of the 
chosen main category to show.  I figured it would be easier to do this in a 
table and include it as a subform in my data entry form because some 
businesses are classified under several categories.

C_Lowe wrote:
> Hi, I know you can do this in a form but I want to know if you can base one 
> lookup column on another in a table: I have a table that associates 
> businessIDs with categories (tblAssocCat).  In this same table I have 2 
> lookup columns: main category (from another table, tblMainCategories) and sub 
> category (from tblSubCategories), where I want only the sub categories of the 
> chosen main category to show.  I figured it would be easier to do this in a 
> table and include it as a subform in my data entry form because some 
> businesses are classified under several categories.

While I don't know whether it is actually possible or not in a table, 
the most common recommendation, is to use forms. See for instance 
http://www.mvps.org/access/lookupfields.htm for some reasons why most 
prefer using combos in forms in stead of table level lookups.

-- 
Roy-Vidar

Yeah looking at most of the responses in the forum and searching the web, I 
know alot of people have issues with using lookup fields!  But knowing my end 
users, it just makes more sense to see and use them in a table to avoid 
spelling mistakes, mis-matching of main categories/sub categories, etc.

I guess another way to go about it is to have the combo boxes set up on the 
form, and then have a submit button associated with a macro to update the 
tblAssocCat.  Any other suggestions?

"RoyVidar" wrote:

> C_Lowe wrote:
> > Hi, I know you can do this in a form but I want to know if you can base one 
> > lookup column on another in a table: I have a table that associates 
> > businessIDs with categories (tblAssocCat).  In this same table I have 2 
> > lookup columns: main category (from another table, tblMainCategories) and sub 
> > category (from tblSubCategories), where I want only the sub categories of the 
> > chosen main category to show.  I figured it would be easier to do this in a 
> > table and include it as a subform in my data entry form because some 
> > businesses are classified under several categories.
> 
> While I don't know whether it is actually possible or not in a table, 
> the most common recommendation, is to use forms. See for instance 
> http://www.mvps.org/access/lookupfields.htm for some reasons why most 
> prefer using combos in forms in stead of table level lookups.
> 
> -- 
> Roy-Vidar
> 

On Mon, 13 Aug 2007 10:58:06 -0700, C_Lowe <C_Lowe@discussions.microsoft.com>
wrote:

>I want to know if you can base one 
>lookup column on another in a table

No.

Just one of the many, many limitations of lookup fields.

             John W. Vinson [MVP]

On Mon, 13 Aug 2007 13:48:02 -0700, C_Lowe <CLowe@discussions.microsoft.com>
wrote:

>I guess another way to go about it is to have the combo boxes set up on the 
>form, and then have a submit button associated with a macro to update the 
>tblAssocCat.  Any other suggestions?

Yes; simply use a bound form and have a combo box bound to the field and
update it directly. You don't NEED a macro or any code to do this. 

If you like the appearance, you can use a Datasheet form, or a Continuous form
made to look very much like a datasheet. But if you're assuming that a form
can't directly update a table with combo boxes, try using a form... it really
does work.

             John W. Vinson [MVP]

On Wed, 15 Aug 2007 14:20:01 -0700, C_Lowe <CLowe@discussions.microsoft.com>
wrote:

>> If you like the appearance, you can use a Datasheet form, or a Continuous 
>form
>> made to look very much like a datasheet. But if you're assuming that a form
>> can't directly update a table with combo boxes, try using a form... it really
>> does work.
>
>
>Ok, I've tried using split forms and datasheet forms, and I've gotten the 
>combo boxes to work with both of them.  However I've now run into two 
>problems:

I don't have A2007 installed, so I can't really advise on "split forms". 

>1)  Whenever I choose the main category, I'll get the appropriate 
>subcategories that go with it.  But when I move on to another record that has 
>a different main category from the previous one, the subcategory entry 
>dissappears.  It still does record correctly in the bound table 
>(tblAssociatedCategories), it just dissappears in the form!

This is because there's really only one combo box, displayed many times; when
you change its rowsource (with the dependent combo box code) it changes all of
the instances of the combo. One (kludgy, unfortunately) solution is to
carefully superimpose a textbox over the text area of the combo. Make it
disabled, locked, not a tab stop, and set its control source to a DLookUp
expression looking up the text value from the table. The combo will "come in
front" when it's dropped down to let the user select a new value, but
otherwise the textbox will show that record's data.

>2)  When I use this form as a subform in another data entry form, everytime 
>I go to choose the subcategory, the criteria entered to make it a cascading 
>combo box pops up as a parameter query (eg: 
>[forms]![subfrmAssocCategories]![MainCategory]).  Should I be changing the 
>"[subfrmAssocCategories]" part to the name of the form the subform is now 
>residing in?

Yes.

>I have tried creating just a simple form with a main category and a sub 
>category cascading combo boxes along with a subform just showing the table 
>they will populate.  The main category combo box will populate the field in 
>the table no problem, but the subcategory combo box won't.  I'm probably 
>overthinking things here... could you explain your simple way? 

Since I have no idea what the Rowsource, Control Source, bound column, etc. of
the combo, nor the structure of the table that the subform is bound to...
'fraid not! More info?

             John W. Vinson [MVP]

Ok, I got it to work using a continuous form... sort of.. the combo boxes 
will update the first data entry for that business, but no other new entries. 
 I'll do a search in the forums/help file to see if anything can be done, 
thanks for all the help!

"John W. Vinson" wrote:

> On Wed, 15 Aug 2007 14:20:01 -0700, C_Lowe <CLowe@discussions.microsoft.com>
> wrote:
> 
> >> If you like the appearance, you can use a Datasheet form, or a Continuous 
> >form
> >> made to look very much like a datasheet. But if you're assuming that a form
> >> can't directly update a table with combo boxes, try using a form... it really
> >> does work.
> >
> >
> >Ok, I've tried using split forms and datasheet forms, and I've gotten the 
> >combo boxes to work with both of them.  However I've now run into two 
> >problems:
> 
> I don't have A2007 installed, so I can't really advise on "split forms". 
> 
> >1)  Whenever I choose the main category, I'll get the appropriate 
> >subcategories that go with it.  But when I move on to another record that has 
> >a different main category from the previous one, the subcategory entry 
> >dissappears.  It still does record correctly in the bound table 
> >(tblAssociatedCategories), it just dissappears in the form!
> 
> This is because there's really only one combo box, displayed many times; when
> you change its rowsource (with the dependent combo box code) it changes all of
> the instances of the combo. One (kludgy, unfortunately) solution is to
> carefully superimpose a textbox over the text area of the combo. Make it
> disabled, locked, not a tab stop, and set its control source to a DLookUp
> expression looking up the text value from the table. The combo will "come in
> front" when it's dropped down to let the user select a new value, but
> otherwise the textbox will show that record's data.
> 
> >2)  When I use this form as a subform in another data entry form, everytime 
> >I go to choose the subcategory, the criteria entered to make it a cascading 
> >combo box pops up as a parameter query (eg: 
> >[forms]![subfrmAssocCategories]![MainCategory]).  Should I be changing the 
> >"[subfrmAssocCategories]" part to the name of the form the subform is now 
> >residing in?
> 
> Yes.
> 
> >I have tried creating just a simple form with a main category and a sub 
> >category cascading combo boxes along with a subform just showing the table 
> >they will populate.  The main category combo box will populate the field in 
> >the table no problem, but the subcategory combo box won't.  I'm probably 
> >overthinking things here... could you explain your simple way? 
> 
> Since I have no idea what the Rowsource, Control Source, bound column, etc. of
> the combo, nor the structure of the table that the subform is bound to...
> 'fraid not! More info?
> 
>              John W. Vinson [MVP]
>