Join not supported

[MajP]

I have a table tblPartCat

PartID
catID
classID

I have a table tblCat with all categories
and a tblParts with all parts

When I add a part on the form I want to assign all categories from tblCat into tblPartCat for the Part
that is on the form but not categories already in tblPartCat.

INSERT INTO tblPartCat ( catID, PartID )
SELECT tblCategory.catID, 
[Forms]![frmPartsAndSubForm]![PartID] AS PartID
FROM tblCategory 
LEFT JOIN tblPartCat ON  (tblCategory.catID = tblPartCat.catID) 
AND ( tblPartCat.PartID =  [Forms]![frmPartsAndSubForm]![PartID])
WHERE (tblPartCat.PartID) Is Null);

This Sql seems to work fine and does what I want.
But once I create it I can never edit it again.
If I try to go to design view it says "join not supported" and will not open
Is there a better way?

I have recreated the query a couple of times and always works, but can never edit.

 

[genomon]

I'm guessing the QBE doesn't like references like "[Forms]![frmPartsAndSubForm]![PartID]" since the source object (your form) cannot be displayed in it.

"Business conventions are important because they demonstrate how many people a company can operate without."

 

[MajP]

I moved the second part into the where statement and that fixed the problem. I guess I am more confused why this works at all.

 

[genomon]

Me 2!


"Business conventions are important because they demonstrate how many people a company can operate without."

 

[dhookom]

I would have probably tried something like:

INSERT INTO tblPartCat ( catID, PartID )
SELECT tblCategory.catID, 
[Forms]![frmPartsAndSubForm]![PartID] AS PartID
FROM tblCategory 
WHERE CatID NOT IN 
(SELECT pc.CatID
 FROM tblPartCat pc
 WHERE pc.PartID =[Forms]![frmPartsAndSubForm]![PartID]);

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Thanks.