Problem with lookup

[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

 

[Douglas J. Steele]

What you need to do is dynamically generate the SQL string for your
sub-category combo box in the AfterUpdate event of your category combo box.

Take a look at http://www.mvps.org/access/forms/frm0028.htm at "The Access
Web".

 

[Gavin Philpott]

Thanks Doug,

This makes sense to me, however I am a complete novice when it comes to
code, SQL etc. Whilst I can see where it is all coming from I cannot work
out how to apply it to my particular situation.

Is this beyond me or is there some alternative reference / background that
would help me along?

Gavin.

 

[Douglas J. Steele]

I'm going to assume that you've got combo boxes cboCategories,
cboSubCategories and cboProducts.

I'll further assume that the Row Source Type property of cboCategories is
set to "Table/Query", and that the Row Source is a SQL string like SELECT
CatID, CatLetter, CatDescription FROM tblCategories ORDER BY CatDescription.

When you've got the form open in Design mode, look at the properties of
cboCategories. On the Event tab, you should see an event named AfterUpdate
(should be the 2nd one). Select [Event Procedure] from the combobox
associated with that property, then click on the ellipses (...) that will
appear to the right of the property.

You should be placed in VB Editor in the middle of a block of code that
looks like:

Private Sub cboCategories_AfterUpdate()

End Sub

Type the following code so that you end up with:

Private Sub cboCategories_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT SubCatID, Cat, SubCatLetter, SubCatDescription "
strSQL = strSQL & "FROM tblSubCategories "
strSQL = strSQL & "WHERE Cat = " & Me!cboCategories.Column(0)
strSQL = strSQL & " ORDER BY SubCatDescription"
Me!cboSubCategories.RowSourceType = "Table/Query"
Me!cboSubCategories.RowSource = strSQL

End Sub

Do the same to add code to the After Update event of cboSubCategories:

Private Sub cboSubCategories_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT ProdID, Cat, SubCat, ProdDescription "
strSQL = strSQL & "FROM tblProducts "
strSQL = strSQL & "WHERE Cat = " & Me!cboCategories.Column(0)
strSQL = strSQL & " AND SubCat = " & Me!cboSubCategories.Column(0)
strSQL = strSQL & " ORDER BY ProdDescription"
Me!cboProducts.RowSourceType = "Table/Query"
Me!cboProducts.RowSource = strSQL

End Sub

Hopefully you can figure out how to adjust which columns are showing in your
combo box if you don't want them all.

What I sometimes do is make cboSubCategories and cboProducts invisible, and
add Me!cboSubCategories.Visible = True to the cboCategories_AfterUpdate
routine, and Me!cboProducts.Visible = True to the
cboSubCategories_AfterUpdate routine

HTH

 

[Gavin Philpott]

Thanks a lot Doug, I've got it all doing exactly what I want now.

Your help is greatly appreciated,

Gavin.