Filter lookup based on field value in current record

[Sam]

Hi,

I have a Items table with fields: ID, Category and SubCategory. In two separate tables i define a list of Categories (Category Table) and Subcategories (Subcategory Table) the latter contains a Category field for each SubCategory.

I would like the Items table to offer a dropdown for the subcategory field based on (restricted to) the category entered in the same record.

So, if the Subcategory table looks like:
*Category* *SubCategory*
Tennis Court
Tennis Racket
Football Pitch
Football Ball

I would like to be offered only a choice of 'Court' and 'Racket' in the dropdown for Subcategory where i've entered 'Tennis' in the preceding Category field of the Items table.

Is that possible?
many thanks in advance
Sam

 

[Sam]

Hi,

I have a Items table with fields: ID, Category and SubCategory. In two separate tables i define a list of Categories (Category Table) and Subcategories (Subcategory Table) the latter contains a Category field for each SubCategory.

I would like the Items table to offer a dropdown for the subcategory field based on (restricted to) the category entered in the same record.

So, if the Subcategory table looks like:
*Category* *SubCategory*
Tennis Court
Tennis Racket
Football Pitch
Football Ball

I would like to be offered only a choice of 'Court' and 'Racket' in the dropdown for Subcategory where i've entered 'Tennis' in the preceding Category field of the Items table.

Is that possible?
many thanks in advance
Sam

I'm using Access 2007 btw. thanks, Sam

 

[John W. Vinson]

Hi,

I have a Items table with fields: ID, Category and SubCategory. In two separate tables i define a list of Categories (Category Table) and Subcategories (Subcategory Table) the latter contains a Category field for each SubCategory.

I would like the Items table to offer a dropdown for the subcategory field based on (restricted to) the category entered in the same record.

So, if the Subcategory table looks like:
*Category* *SubCategory*
Tennis Court
Tennis Racket
Football Pitch
Football Ball

I would like to be offered only a choice of 'Court' and 'Racket' in the dropdown for Subcategory where i've entered 'Tennis' in the preceding Category field of the Items table.

Is that possible?
many thanks in advance
Sam

This is one of the MANY limitations to Microsoft's misdesigned, misleading
Lookup Field. See httm://www.mvps.org/access/lookupfields.htm for a critique.

It's very easy to do on a Form. You would base the subcategory combo on a
Query referencing

=[Forms]![NameOfForm]![NameOfCategoryCombo]

as a criterion on the category in the subcategories table. You would need to
Requery the subcategory combo box in the AfterUpdate event of the category
combo.

These capabilities are not available in table combos - just one reason why you
should always use Forms rather than table datasheets to interact with data!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Sam]

Hi,

I have a Items table with fields: ID, Category and SubCategory. In two separate tables i define a list of Categories (Category Table) and Subcategories (Subcategory Table) the latter contains a Category field for each SubCategory.

I would like the Items table to offer a dropdown for the subcategory field based on (restricted to) the category entered in the same record.

So, if the Subcategory table looks like:
*Category* *SubCategory*
Tennis Court
Tennis Racket
Football Pitch
Football Ball

I would like to be offered only a choice of 'Court' and 'Racket' in the dropdown for Subcategory where i've entered 'Tennis' in the preceding Category field of the Items table.

Is that possible?
many thanks in advance
Sam

This is one of the MANY limitations to Microsoft's misdesigned, misleading
Lookup Field. See httm://www.mvps.org/access/lookupfields.htm for a critique.

It's very easy to do on a Form. You would base the subcategory combo on a
Query referencing

=[Forms]![NameOfForm]![NameOfCategoryCombo]

as a criterion on the category in the subcategories table. You would need to
Requery the subcategory combo box in the AfterUpdate event of the category
combo.

These capabilities are not available in table combos - just one reason why you
should always use Forms rather than table datasheets to interact with data!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

Thanks John, having just started with Access that will not doubt save me a lot of time.
Regards,
Sam

Hi,

I have a Items table with fields: ID, Category and SubCategory. In two separate tables i define a list of Categories (Category Table) and Subcategories (Subcategory Table) the latter contains a Category field for each SubCategory.

I would like the Items table to offer a dropdown for the subcategory field based on (restricted to) the category entered in the same record.

So, if the Subcategory table looks like:
*Category* *SubCategory*
Tennis Court
Tennis Racket
Football Pitch
Football Ball

I would like to be offered only a choice of 'Court' and 'Racket' in the dropdown for Subcategory where i've entered 'Tennis' in the preceding Category field of the Items table.

Is that possible?
many thanks in advance
Sam

This is one of the MANY limitations to Microsoft's misdesigned, misleading
Lookup Field. See httm://www.mvps.org/access/lookupfields.htm for a critique.

It's very easy to do on a Form. You would base the subcategory combo on a
Query referencing

=[Forms]![NameOfForm]![NameOfCategoryCombo]

as a criterion on the category in the subcategories table. You would need to
Requery the subcategory combo box in the AfterUpdate event of the category
combo.

These capabilities are not available in table combos - just one reason why you
should always use Forms rather than table datasheets to interact with data!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

Hi,

I have a Items table with fields: ID, Category and SubCategory. In two separate tables i define a list of Categories (Category Table) and Subcategories (Subcategory Table) the latter contains a Category field for each SubCategory.

I would like the Items table to offer a dropdown for the subcategory field based on (restricted to) the category entered in the same record.

So, if the Subcategory table looks like:
*Category* *SubCategory*
Tennis Court
Tennis Racket
Football Pitch
Football Ball

I would like to be offered only a choice of 'Court' and 'Racket' in the dropdown for Subcategory where i've entered 'Tennis' in the preceding Category field of the Items table.

Is that possible?
many thanks in advance
Sam

This is one of the MANY limitations to Microsoft's misdesigned, misleading
Lookup Field. See httm://www.mvps.org/access/lookupfields.htm for a critique.

It's very easy to do on a Form. You would base the subcategory combo on a
Query referencing

=[Forms]![NameOfForm]![NameOfCategoryCombo]

as a criterion on the category in the subcategories table. You would need to
Requery the subcategory combo box in the AfterUpdate event of the category
combo.

These capabilities are not available in table combos - just one reason why you
should always use Forms rather than table datasheets to interact with data!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

Thanks John, having just started with Access that will not doubt save me a lot of time!
Regards,
Sam