Repeating AutoNumber Primary Key

[Deb]

Recently, a new record entry repeats an AutoNumber primary
key, essentially resulting in a duplicate record, which of
course, cannot be saved. The AutoNumber is set to
increment. Does anyone know why this is happening or
what I can do about it? There are not many records in the
table, only about 75 and the AutoNumbers are up to 323
with the repeats starting at about 173.

 

[Gerald Stanley]

I wouldn't like to speculate as to why it is happening but
as to what to do about it, I would recommend transferring
the data to a new version of the table.
To do this, export the definition of the table. If the
autonumber column appears on any other table as a forign
key, then add a new column for it. Next, create a
MakeTable query to export all the data from the damaged
table to the new table. This will create new autonumbers
for the data. If required, run update queries on the
tables where the autonumber is a foreign key to change the
value from the old to the new and delete the old autonumbers

Hope This Helps
Gerald Stanley MCSD

 

[Tim Ferguson]

Recently, a new record entry repeats an AutoNumber primary
key, essentially resulting in a duplicate record, which of
course, cannot be saved. The AutoNumber is set to
increment.

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and
Repair a Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;257408

Hope that helps


Tim F