Why is Access trying to re-use an AutoNumber when I add a record?

[sueb]

In my database, I have specified that the primary key ("PatientInd ex) is an AutoNumber, and that duplicates are not allowed. ("New Values" is set to "Increment" , "Indexed" is set to "Yes (No Duplicates)", and it is the primary key.)

However, when I try to add a new record, Access provides a value for the new record that already exists in the existing records. Why is that, and how do I make it create a new, unused one?

[Mariostg]

Did you try to compact and repair your database?

[sueb]

Yes--I actually do that every evening.

[Mariostg]

There are discussions on this topic in this forum like this one:
Autonumber duplicates
Maybe that could help. There are others too.

[sueb]

I stumbled across a fix! (Caution: It's lame.)

In trying to get the starting AutoNumber set to what would work, I made a copy of the table. I noticed that trying to add a record to this new table resulted in a correct AutoNumber, so I took a chance: I renamed the original table to get it out of the way, and renamed the new table to the correct name.

Bingo! AutoNumber now works, and my linked tables are none the wiser.

Thanks, Mario, for the link, though--good info there.