Lookup 2 based upon Lookup 1

[Bill Sturdevant]

I have 3 tables:

TblA
ID
FldX
Lookup_To_TblB_For_Product_Type
Lookup_To_TblC_For_Product

TblB
ID
Product_Type

TblC
ID
Product
Lookup_To_TblB_For_Product_Type

In TblA, what rowsource do I use for Lookup_To_TblC_For_Product
such that the only values that show in the list are those values from TblC
where TblC.Lookup_To_TblB_For_Product_Type is equal to TblB.ID?

In other words, in TblA, you choose a Product Type, and the list of Products
available on that record are only those of the already select Product Type.

 

[Bill Sturdevant]

Jamie,

I am trying to run your suggested code and get an error on the second
CREATE, runtime error 3289 "Syntax error in CONSTRAINT clause."

 

[Bill Sturdevant]

Jamie,

Thanks! What you suggested works (except for the ".." before the "Execute".
I changed those to a single ".".

BUT, it still does not give me what I was asking for.

Using your example as a basis:
In the Product Table, I do not want to store Product type again.
Instead, I want a Lookup, such that when I am adding records to Products, I
have a combo box that lets me choose a Product Type from a list of types
available in the Product Type table.

Then, in TblA I want 2 Lookups, so that when I am adding records, I see,
first, a combo box that lets me pick the desired Product Type, and after
having chosen that, I can pick the Product, but this combo box should only
have products listed that match the chosen Product Type according to what is
in the Products Table. Then there can be some other fields for additional
data.

In practical terms, when I am entering records in TblA, I want to first
choose Fruits as a product type and then only be allowed to choose from
Bananas, Grapefruit and Lemons on the Product combo box. When I add the next
record, I want to first choose Toothpaste, and then be allowed to choose only
from Colgate, Crest and Ipana.

 

[Bill Sturdevant]

Jamie,

I think we are almost there!

I keep getting asked for a value for "@product_Type". The "@" isn't getting
interpretted correctly.