Lookup 2 based upon Lookup 1

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 wrote:
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?

CREATE TABLE ProductTypes (
product_type VARCHAR(100) NOT NULL PRIMARY KEY
)
;
CREATE TABLE Products (

product_name VARCHAR(255) NOT NULL PRIMARY KEY,
product_Type VARCHAR(100) NOT NULL,
FOREIGN KEY (product_type) REFERENCES ProductTypes (product_type)
ON UPDATE CASCADE ON DELETE CASCADE
)
;
CREATE TABLE TblA (
ID INTEGER NOT NULL PRIMARY KEY,
FldX NTEXT,
product VARCHAR(255) NOT NULL,
FOREIGN KEY (product) REFERENCES Products (product_name)
ON UPDATE CASCADE ON DELETE CASCADE)
;
CREATE VIEW viewA AS
SELECT tblA.ID, Products.product_name, ProductTypes.product_type
FROM (tblA INNER JOIN Products ON tblA.sku = Products.sku)
INNER JOIN ProductTypes
ON Products.product_type = ProductTypes.product_type
;

Jamie.

--

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."

"Jamie Collins" wrote:

Bill Sturdevant wrote:
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?

CREATE TABLE ProductTypes (
product_type VARCHAR(100) NOT NULL PRIMARY KEY
)
;
CREATE TABLE Products (

product_name VARCHAR(255) NOT NULL PRIMARY KEY,
product_Type VARCHAR(100) NOT NULL,
FOREIGN KEY (product_type) REFERENCES ProductTypes (product_type)
ON UPDATE CASCADE ON DELETE CASCADE
)
;
CREATE TABLE TblA (
ID INTEGER NOT NULL PRIMARY KEY,
FldX NTEXT,
product VARCHAR(255) NOT NULL,
FOREIGN KEY (product) REFERENCES Products (product_name)
ON UPDATE CASCADE ON DELETE CASCADE)
;
CREATE VIEW viewA AS
SELECT tblA.ID, Products.product_name, ProductTypes.product_type
FROM (tblA INNER JOIN Products ON tblA.sku = Products.sku)
INNER JOIN ProductTypes
ON Products.product_type = ProductTypes.product_type
;

Jamie.

--

Bill Sturdevant wrote:
I am trying to run your suggested code and get an error on the second

CREATE, runtime error 3289 "Syntax error in CONSTRAINT clause."

Sorry, I messed up my version control g. Here's the correct VBA:

Sub test()
With CurrentProject.Connection
..Execute _
"CREATE TABLE ProductTypes ( " & _
" product_type VARCHAR(100) NOT NULL PRIMARY KEY ) ; "
..Execute _
"CREATE TABLE Products (" & _
" sku CHAR(9) NOT NULL PRIMARY KEY," & _
" product_name VARCHAR(255) NOT NULL," & _
" product_Type VARCHAR(100) NOT NULL," & _
" FOREIGN KEY (product_type) REFERENCES ProductTypes (product_type)" &
_
" ON UPDATE CASCADE ON DELETE CASCADE);"
..Execute _
"CREATE TABLE TblA (" & _
" ID INTEGER NOT NULL PRIMARY KEY," & _
" FldX NTEXT," & _
" sku CHAR(9) NOT NULL," & _
" FOREIGN KEY (sku) REFERENCES Products (sku)" & _
" ON UPDATE CASCADE ON DELETE CASCADE);"
.Execute _
"CREATE VIEW viewA AS" & _
" SELECT tblA.ID, Products.product_name, ProductTypes.product_type" & _
" FROM (tblA INNER JOIN Products ON tblA.sku = Products.sku)" & _
" INNER JOIN ProductTypes" & _
" ON Products.product_type = ProductTypes.product_type;"
End With
End Sub

Jamie.

--

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.


"Jamie Collins" wrote:

Bill Sturdevant wrote:
I am trying to run your suggested code and get an error on the second

CREATE, runtime error 3289 "Syntax error in CONSTRAINT clause."

Sorry, I messed up my version control g. Here's the correct VBA:

Sub test()
With CurrentProject.Connection
..Execute _
"CREATE TABLE ProductTypes ( " & _
" product_type VARCHAR(100) NOT NULL PRIMARY KEY ) ; "
..Execute _
"CREATE TABLE Products (" & _
" sku CHAR(9) NOT NULL PRIMARY KEY," & _
" product_name VARCHAR(255) NOT NULL," & _
" product_Type VARCHAR(100) NOT NULL," & _
" FOREIGN KEY (product_type) REFERENCES ProductTypes (product_type)" &
_
" ON UPDATE CASCADE ON DELETE CASCADE);"
..Execute _
"CREATE TABLE TblA (" & _
" ID INTEGER NOT NULL PRIMARY KEY," & _
" FldX NTEXT," & _
" sku CHAR(9) NOT NULL," & _
" FOREIGN KEY (sku) REFERENCES Products (sku)" & _
" ON UPDATE CASCADE ON DELETE CASCADE);"
.Execute _
"CREATE VIEW viewA AS" & _
" SELECT tblA.ID, Products.product_name, ProductTypes.product_type" & _
" FROM (tblA INNER JOIN Products ON tblA.sku = Products.sku)" & _
" INNER JOIN ProductTypes" & _
" ON Products.product_type = ProductTypes.product_type;"
End With
End Sub

Jamie.

--

Bill Sturdevant wrote:

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.

For your first dropdown

SELECT ProductTypes.product_type
FROM ProductTypes
INNER JOIN Products
ON ProductTypes.product_type = Products.product_type;

For your next dropdown, use the selected value from the first in the
following:

SELECT sku, product_name
FROM Products
WHERE product_Type = @product_Type;

Then INSERT the chosen sku in TblA.

Jamie.

--

Jamie,

I think we are almost there!

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

"Jamie Collins" wrote:

Bill Sturdevant wrote:

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.

For your first dropdown

SELECT ProductTypes.product_type
FROM ProductTypes
INNER JOIN Products
ON ProductTypes.product_type = Products.product_type;

For your next dropdown, use the selected value from the first in the
following:

SELECT sku, product_name
FROM Products
WHERE product_Type = @product_Type;

Then INSERT the chosen sku in TblA.

Jamie.

--

Bill Sturdevant wrote:
For your first dropdown

SELECT ProductTypes.product_type
FROM ProductTypes
INNER JOIN Products
ON ProductTypes.product_type = Products.product_type;

For your next dropdown, use the selected value from the first in
the
following:

SELECT sku, product_name
FROM Products
WHERE product_Type = @product_Type;

Then INSERT the chosen sku in TblA.

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

@product_Type is a placeholder for the value from your first dropdown.
Apologies for not being clearer. I guess I was suggesting you could use
a procedure e.g.

CREATE PROCEDURE ProductsByType
(arg_product_Type VARCHAR(100)) AS
SELECT sku, product_name
FROM Products
WHERE product_Type = arg_product_Type
ORDER BY product_name;

Jamie.

--