Did you allow Duplicates in the Autonumber field when you set up the table?

Hi Gary, Check other fields to see if you don't allow duplicates. John

Kevin and John are both on the right track. Error 3022 specifically means you're trying to add a duplicate value to a field or combination of fields that has a unique index. A missing required field would produce a different error, usually error 3314.

If the only unique field is the Autonumber primary key, then the most likely culprit is that your copy routine is also copying the Autonumber value from the source record. When you use AddNew, don't assign anything to the ID field. Let Access generate the new Autonumber value.

For example, if you're looping through the fields:

If rsSource.Fields(fld.Name).Name <> "ID" Then

    rsTarget.Fields(fld.Name) = rsSource.Fields(fld.Name)

End If

Replace "ID" with the actual name of your Autonumber field.

Also, open the table in Design View and check the Indexes property. A multi-field unique index can exist at the table level even if none of the individual fields says Indexed: Yes (No Duplicates).

You could also try scaling back and just doing one field at a time until you find the one that's causing problems.

Thanks for your responses. I had checked the indexes, etc and couldn't see anything wrong. However, today when I tried to manually add a record to the table, it gave me the same message. The (primary key) id field was set to auto number. But. The last auto number was 376. And it was trying to add 325 which was a duplicate. So, I copied the table structure and appended the data into a new table and now it all works great. The old table was linked from a .mdb database. Maybe it got confused.

Glad you got it sorted out. That definitely sounds like the Autonumber seed in the old linked table got out of sync somehow. If Access was trying to generate 325 when records already existed through 376, error 3022 is exactly what you'd see.

Creating a fresh table and appending the data was a perfectly good fix. Linked legacy .mdb tables can occasionally develop odd little issues like that, especially after imports, deletes, or other changes to the data. Sometimes compacting and repairing the back-end will correct it too, but since the new table is working properly, I'd stick with it.