Can't append all records due to key violations

[step]

Hi,

I am pretty new to Access so please excuse me if my error is total muppetry.
When running an INSERT INTO query in Access 2007 VBA I keep getting the error "Microsoft Access can't append all the records... due to key violations. I have read a few threads and cannot find the problem.

Here is the code that hangs. It has worked before and now fails after synching across machines.

SQL = "INSERT INTO Fleet ( ModifiedRegion, seatcat, YearEnding, Fleet )" & _
"VALUES ('" & region & "', " & seatcat & ", " & MyYear & ", " & MyFleet & ");"

DoCmd.RunSQL SQL

All of the values are variables and all have something in them and all are of the right data type. I have tried running

The destination table resides in a backend database in the same folder and has the primary key ID that is an autonumber field. No fields 'required' and all text fields allow zero length. I have created a custom index on the fields ModifiedRegion, seatcat and YearEnding. For this index, primary key and unique are set to 'No' and Ignore Nulls is set to 'Yes'. I have tried deleting the key but it makes no difference. The table does not have relationships and referential integrity is not enforced.

Any ideas appreciated.
Thanks
step

 

[step]

I have actually found the problem.

Thanks to those that had a look.

I do not know how it happend but the primary key was not autonumber. That was the problem. When I tried to add a record, Access did not auto-generate the primary key value.

Cheers,
Step