Access 2000 Duplicating Autonumber Field for New Records 1

[johnfrani]

I have an Access 2000 table with a MemberID field as the primary key.

Data Type = autonumber
Field Size = long interger
New Values = increment
Indexed = Yes (No Duplicates)

For the past year, the MemberID field has worked correctly creating a unique MemberID number for each new record.

Recently, when I try to insert a new record, I get a value in the MemberID field that is a duplicate of an existing record.

Any ideas why this might be happening and how to fix it?

Thanks, John

 

[lameid]

It is not possible if the key is unique (primary key) for it to save the record with a duplicate value nor should autonumber give you a bad value.

Are you sure the table definition is as you describe?

Have you tried compacting and repairing the database?

 

[johnfrani]

It took me 2 tries to compact and repair the DB (the first one stalled-out) but when it was successful, the autonumber started working correctly.
Thanks
John