Autonumber lost count

[Phil Stokes]

I have a primary key in a table that contains records input via a form. This
data is then transferred to another table for storage and is deleted from the
first table. The record in the first table is allocated a ID that is a
primary key using autonumber. It has worked ok until now when suddenly the
Autonumber in the first table has reverted to 1 and is now producing records
that are non unique in the second table. I seem to remember this coming up
previously but there are no threads coming up with similar problems. Oh by
the way, I'm using Access 2000 . any help would be apreciated.

 

[Allen Browne]

When you compact the database after removing records, Access resets the
AutoNumber field.

 

[Pat Hartman\(MVP\)]

The kb suggests downloading the latest version of Jet but that didn't solve
the problem for me. In my case, the form had been corrupted and every time
I fixed the table, as soon as I added a new record with the form, the
autonumber would break again. I ended up having to create a new database.
I imported everything except the bad table and the bad form. I exported the
table to a csv file and then imported the .csv file. I had to rebuild the
form since importing/compacting/repairing - even the decompile switch did
not resolve the corruption issue.