Row source for form cbo to drive qry from M:M link tbl

[Guest]

New to access. Trying to have a parameter qry based on user selection from a
combo box, but the table referenced is a link table for a many-to-many
relationship. I'm struggling to figure out what the row source should be for
the cbo.

tblProduct
pkeyProductID
strProduct

tblProcess
pkeyProcessID
strProcess

tbllinkPlan
fkeyProductID
strProduct
fkeyProcessID
strProcess
pkeyPlanStep
strPlanArea

I want a cbo on a form that asks the user before running the query what
strProduct to select. What should the row source SQL be?

 

[Marshall Barton]

New to access. Trying to have a parameter qry based on user selection from a
combo box, but the table referenced is a link table for a many-to-many
relationship. I'm struggling to figure out what the row source should be for
the cbo.

tblProduct
pkeyProductID
strProduct

tblProcess
pkeyProcessID
strProcess

tbllinkPlan
fkeyProductID
strProduct
fkeyProcessID
strProcess
pkeyPlanStep
strPlanArea

I want a cbo on a form that asks the user before running the query what
strProduct to select. What should the row source SQL be?

First, the tblLinkPlan should NOT have the strProduct or
strProcess fields. That would duplicate the data and allow
for it to become out of sync.

You can easily retrieve that arrangement of data with a
query like:

SELECT tbllinkPlan.strPlanArea,
tblProduct.strProduct,
tblProcess.strProcess
FROM (tbllinkPlan
INNER JOIN tblProduct
ON tbllinkPlan.fkeyProductID = tblProduct.pkeyProductID)
INNER JOIN tblProcess
ON tbllinkPlan.fkeyProcessID = tblProcess.pkeyProcessID

Now, to your question. Assuming I inderstand what you're
doing, the combo box would simply use tblProduct oe, more
likely, a simple query:

SELECT pkeyProductID, strProduct
FROM tblProduct
ORDER BY strProduct

The combo box's properties would be:
ColumnCount 2
BoundColumn 1
ColumnWidths 0;

This would then allow you to add a where clause to the above
query:

WHERE tblProduct.pkeyProductID = Forms!yourform.thecombo