Adding Many to Many Relationships to an existing table

[brharrii]

I have 3 tables

tblProductInfo
- ProductID
- ProductItemNumber
- JDEDescription

tblFacility
- FacilityID
- FacilityDescription

tblProductFacilityMM
- ProductToFacilityID
- ProductIDFK (combined with FacilityIDFK to make a PK)
- FacilityIDFK

As I'm writing this out, I am realizing that tlbProductFacilityMM.producttoFacilityID is probably not necessary, but that I don't expect that to have much significance to the issue.

So I've setup a query between the two tables:

SELECT tblProductInfo.ProductID, tblProductInfo.ItemNumber, tblProductInfo.JDEDescription, tblProductFacilityMM.FacilityIDFK, tblFacility.FacilityDescription
FROM tblFacility INNER JOIN (tblProductInfo INNER JOIN tblProductFacilityMM ON tblProductInfo.ProductID = tblProductFacilityMM.ProductIDFK) ON tblFacility.FacilityID = tblProductFacilityMM.FacilityIDFK;

And used it to create my subform which is simply a drop down box for tblProductFacilityMM.FacilityIDFK.

My main form is one that has already been in use for 6 months or so, it is based off the tblproductinfo table and needs to have the option to select multiple Facilities for each ProductID. I inserted the subform, but when I try to select a facility I get an error that reads:

Cannot Join Records; Join key of tblProductFacilityMM not in recordset

Any ideas what I might need to do to resolve this?

thanks

Bruce

 

[brharrii]

Apparently I posted 5 minutes too soon. I discovered the issue was in my sql querry, I hadn't included the ProductIDFK field. After I did that the problem seemed to resolve itself.

Thank you, and sorry for the post