Key Violation errors - Expert advice needed

[SallyG]

I have a table that I am try to add new records to using an append query.

All the fields in the query are of the same data type as the table i am appeneding into, everything matches perfectly but when I run the query i get the error msg, "didn't add XX number of records to the table due to key Violations".

Now if I remove my primary key from the AutoNo ID field in my table they append OK but as the key is removed, they append all over the place and not sequentially as the existing records are. Access starts assigning IDs from EXISTING records to the new ones which means when I display the records in a from, the wrong records show up.

Any ideas on what I am doing wrong here ?????

Thanks in advance,


Sally [sig][/sig]

 

[petermeachem]

hi,
You need to append everything from your 'updater' table bar the autonumber field. Access will autoincrement the main table for you.
[sig][/sig]

 

[MichaelRed]

Sally,

To expand Peter's explination - just a little. The "AutoNumber" is a system generated value - and must be a Unique value. When you attempt to append a value to this field, is (often) already in the table, thus would violate the unique requirement. Even if this is not so, since it is a system created value, it will not accept a "suggestion".

You need to craefull consider how you need the records will be identified/ordered before you implement the database schema to avoid having this type of problem.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]