have a component on a database where users enter details about a specific incident. Upon closure 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

Duplicates numbers in AutoNumber...? sounds very weird to me, even if the AutoNumber is not set-up as PrimaryKey, you cannot have duplicates in it.

I agree with comment about the auto number issue

Key violations can occur is any fields have a unique index on them. Have you checked for that?

Also Date is a reserved word in Access and shouldn't be used for a object or field name.

HTH