There are posts everywhere about Autonumbers misbehaving in Access 2007 and Access 2010. Most post end up pointing to a solution on Allen Brownes great website
Unfortunately there are problems with that solution (and if I am wrong about that sorry).
1) It doesn't work in Access 2010 but seems to work.
2) It fixes the random number seed but eventually the problem returns.
There is only one solution and its a manual fix.
Open the table in design view. Find the auto-number field. Make it the primary key. Look at the sort on the primary key. It must be Ascending.
Do not remove the Primary key from the autonumber key, no matter how you want that table to behave.
What I think is going on
At some stage, someone in Microsoft decided that when a database is compacted, they could look at the very last record in the table and then look at the autonumber field and save that number as the seed. Then when the next record is added, it looks at the seed, adds one and then updates the seed. All fine if the table is sorted correctly. I think the bug crept in because Microsoft templates all have autonumbers as the primary key in each table. Therefore they wouldn't have noticed the bug. For people who are getting an error like "Autonumber already exists", its because the seed is lower than the highest autonumber in the database and eventually it hits a record that already exists.
I hope I am wrong, this is totally unnecessary bug that should be fixed in the next service pack. It has caused problems in at least 3 of the databases we are maintaining.
We have this bug in Access 2000 databases but we are running them with Access 2010 now.
I do not want anyone to lecture me about primary key design or autonumbers but if you know anything about house paint colours and kitchen design, happy to chat.
Here is an article that shows you how to change (primary key) indexes
Access MVP 2006-2013