Basing one lookup column on another

[Guest]

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.

 

[RoyVidar]

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.

 

[Guest]

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?

 

[John W. Vinson]

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]

 

[John W. Vinson]

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]

 

[Guest]

Thanks John, I'll try your suggestions! I may need to bug again if I can't
figure things out.

 

[Guest]

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:

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!

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; 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.

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?

 

[John W. Vinson]

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]

 

[Guest]

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!

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]