G
Gavin Philpott
I have a database which stores product information for hardware products.
Each product is allocated into a category (like Screws, Bolts etc) then a
sub-category (like Brass, Steel etc) For each category there are different
sub-categories (i.e. Screws may have Brass & Steel as sub-cat, whereas Bolts
may have Imperial & Metric as its sub-cats). Each category and sub-category
has been assigned a letter so that a product code can be allocated like AA,
AB, BA etc where the 1st letter is the cat & the 2nd is the sub-cat.
I have set up one table each for categories & sub categories. The tables are
linked so that in the sub-cat table the category can be selected from a drop
down box before typing in the sub-cat detail. Both tables are then used in a
third products table to contain the actual details of each product. (Outline
of tables below)
In this table I have set up a drop-down box to select which category &
sub-cat each product record is allocated into. My problem comes with the
sub-cat drop down, in that it displays all the data from the sub-cat table.
I want it to only display the sub-cats that are applicable to the category
already selected, firstly for ease of reading (there will probably be about
20 sub cats for each cat), and to make sure that a only an applicable
sub-cat can be selected. I have tried adding filter criteria to the lookup,
but everything I have tried either displays all the sub-cats or none at all.
Any help greatly appreciated,
Gavin.
tblCategories
CatID (autonum)
CatLetter (text) A,B,C etc
CatDescription (text) Screws, Bolts etc
tblSubCategories
SubCatID (autonum)
Cat (number) Lookup of tblCategories
SubCatLetter (text) A,B,C etc (may be repeated for different
categories)
SubCatDescription (text) Brass, Steel, Metric, Imperial
tblProducts
ProdID (autonum)
Cat (number) Lookup of tblCategories
SubCat (number) Lookup of tblSubCategories
ProdDescription (text) Info about individual product
Each product is allocated into a category (like Screws, Bolts etc) then a
sub-category (like Brass, Steel etc) For each category there are different
sub-categories (i.e. Screws may have Brass & Steel as sub-cat, whereas Bolts
may have Imperial & Metric as its sub-cats). Each category and sub-category
has been assigned a letter so that a product code can be allocated like AA,
AB, BA etc where the 1st letter is the cat & the 2nd is the sub-cat.
I have set up one table each for categories & sub categories. The tables are
linked so that in the sub-cat table the category can be selected from a drop
down box before typing in the sub-cat detail. Both tables are then used in a
third products table to contain the actual details of each product. (Outline
of tables below)
In this table I have set up a drop-down box to select which category &
sub-cat each product record is allocated into. My problem comes with the
sub-cat drop down, in that it displays all the data from the sub-cat table.
I want it to only display the sub-cats that are applicable to the category
already selected, firstly for ease of reading (there will probably be about
20 sub cats for each cat), and to make sure that a only an applicable
sub-cat can be selected. I have tried adding filter criteria to the lookup,
but everything I have tried either displays all the sub-cats or none at all.
Any help greatly appreciated,
Gavin.
tblCategories
CatID (autonum)
CatLetter (text) A,B,C etc
CatDescription (text) Screws, Bolts etc
tblSubCategories
SubCatID (autonum)
Cat (number) Lookup of tblCategories
SubCatLetter (text) A,B,C etc (may be repeated for different
categories)
SubCatDescription (text) Brass, Steel, Metric, Imperial
tblProducts
ProdID (autonum)
Cat (number) Lookup of tblCategories
SubCat (number) Lookup of tblSubCategories
ProdDescription (text) Info about individual product