Record Specific filtering

[epoh97 via AccessMonster.com]

I would like to create a general form that the user can select one type of
ProductClass (for instance) and then select the ProductName from their.

Example:
ProductClass

Produce
Hardware
Electronics

ProductName
Potatoes(Produce)
Carrots(Produce)
Nails(Hardware)
CDs(Electronics)
etc.

What the user is getting right now for filtering is if they select Produce
for Record1. Potatoes and Carrots show up in the list box. When they go to
record2 and select Hardware. Potatoes, Carrots & Nails appear in the list box.


How can I filter categories per record?

-----------------
Structure:

Each Product is entered in a table called MasterLookup

ProdID, ProductClass and ProductName are all Fields in the Master Lookup
table

This is how data is entered

1,Produce,Potatoes
2,Produce,Carrots
3,Hardware,Nails
4,Electronics,CDs


Thanks,

 

[Steve Schapel]

Epoh,

Try it like this...
Remove any code from the ProductClass combobox that requeries the
ProductName combobox.
Remove any criteria from the Row Source query of the ProductName
combobox that refers to the ProductClass combobox.
On the Enter event of the ProductName combobox, put code like this...
Me.ProductName.RowSource = "SELECT ProductName FROM [Master Lookup]
WHERE ProductClass ='" & Me.ProductClass & "'"
.... and then, on the Exit event of the ProductName combobox...
Me.ProductName.RowSource = "SELECT ProductName FROM [Master Lookup]"

 

[epoh97 via AccessMonster.com]

Thanks, Steve it worked like a charm.

 

[epoh97 via AccessMonster.com]

Is there a way to skip this:

(On the Enter event of the ProductName combobox, put code like this...
Me.ProductName.RowSource = "SELECT ProductName FROM [Master Lookup]
WHERE ProductClass ='" & Me.ProductClass & "'"
... and then, on the Exit event of the ProductName combobox...
Me.ProductName.RowSource = "SELECT ProductName FROM [Master Lookup]")

If the record has a value?

 

[Steve Schapel]

Epoh,

Yes, you can specify this in code...

If IsNull(Me.ProductName) Then
Me.ProductName.RowSource = "SELECT ProductName FROM [Master Lookup]
WHERE ProductClass ='" & Me.ProductClass & "'"
End If

There is a problem, though. What if you are returning to the record in
order to edit it, in which case you still want the second combobox's
list to be restricted according to the first combobox's value.

Why are you returning to an existing record anyway? And, when returning
to an existing record, why are you accessing the combobox if you don't
want to change it?