Fixing AutoNumbers when Access assigns negatives or duplicates

I ran across this post from Allen Browne on resolving problems with autonumbers. It seems like a useful post to keep handy for when I need it someday.

In Access 2000 and later, an AutoNumber field may begin assigning extreme values, negatives, and even duplicates. The attempt to assign a duplicate fails if the field is primary key, so it may not be possible to save new records in an affected table.

Autonumber Your Records Without Using the Built-In Autonumber

Utter Angel's
MS Access Downloads

"I want my records to have a unique number assigned automatically, but I don't want to use the built-in autonumber."

First think through why you need to build your application that way. There are lots of arguments to be made that the autonumber is the best way to assign IDs, and that the primary key on your tables should be an autonumber. That being said, in one of my biggest databases I chose to generate the unique IDs in code instead of using an autonumber field. I was worried that if I needed to merge data between two copies of the database (for instance merging two separate projects) I would need to be able to change the ID number on the records before merging). I have still never merged data, but the database is working will with my "manual autonumber".

Here's a post from Candace Tripp's Utter Angel page with simple sample code for generating "next number in sequence" IDs without using an autonumber field.