query doesn't allow new records

  • Thread starter Thread starter Slez via AccessMonster.com
  • Start date Start date
S

Slez via AccessMonster.com

I have a subform that won't allow me to enter new records which I have traced
back to the fact that the query it's based off of doesn't allow them. Rather
than trying to explain in detail my table layout, I was thinking I could post
the SQL below and hopefully someone could steer me in the direction of what I
should be looking for. The error message I get is: "The changes you
requested ....would create duplicate values...", yet I can enter directly
into the associated tables without error. Any suggestions??
Thanks in advance for any help!

SELECT tblFinishSample.JobNumber, tblFinishSample.SampleID, tblFinishFormula.
Qty, tblFinishColors.FinishItemID, tblFinishColors.FinishItemName,
tblFinishColors.GroupID, tblFinishGroup.GroupName
FROM tblFinishSample INNER JOIN (tblFinishGroup INNER JOIN (tblFinishColors
INNER JOIN tblFinishFormula ON tblFinishColors.FinishItemID =
tblFinishFormula.FinishItemID) ON tblFinishGroup.GroupID = tblFinishColors.
GroupID) ON (tblFinishSample.SampleID = tblFinishFormula.SampleID) AND
(tblFinishSample.JobNumber = tblFinishFormula.JobNumber)
WHERE (((tblFinishSample.JobNumber)=[Forms]![frmFinishFormulaEntry]!
[JobNumber]) AND ((tblFinishSample.SampleID)=[Forms]![frmMainScreen]!
[frmFinishSampleEntry]![SampleID]))
ORDER BY tblFinishSample.SampleID, tblFinishColors.FinishItemID;
 
I have a subform that won't allow me to enter new records which I have traced
back to the fact that the query it's based off of doesn't allow them.
Click to expand...

A four-table join will usually NOT be updateable. Normally you'ld use
just the main table as the recordsource for a form; if you want to
display things like Finish Color you can use a Combo Box on the form,
bound to the linking field but displaying the text. You're not trying
to update all four tables using the same form, I hope?

John W. Vinson[MVP]
 
Thanks for the response! I was only trying to update one table, and I guess
I was over-thinking this a bit. I changed the record source to the table and
got rid of the query altogether. It now works properly.
Thanks again!
Slez
 
Back
Top