I have a component on a database where users enter details about a specific incident. Upon clsoure of the incident form a query is run that append (or is supposed to append) certain field to another table. The second table has an auto number field but this is not included in the append query. The SQL for the query is...

INSERT INTO Observations ( referral_id, [Date], Observation, Logger )
SELECT Incident.referral_id, Incident.[date time], "Refer to Incident Report " & [forms].[incidentreport].[incidentid] AS Observation, [forms].[incidentreport].[logged by] AS Logger
FROM Incident
WHERE (((Incident.IncidentID)=[forms].[incidentreport].[incidentid]) AND ((Incident.ObsLogUpdate)=No));

There is only one primary key in the table to be appended to and that is the autonumber field and I have left this to Access to insert an autonumber when it appends the new data. Whenever I run the query I receive a key violation error. If the primary key is removed from the second table (that which is being appended to) I will get duplicate numbers created in what was the auto number field but no longer receive an error message.

Can anyone advice me please

Thanking you