Unexpected autonumber jump in Access apps

We’ve received reports from customers that occasionally their Access app autonumber field jumps by an unexpected number. For example, you may have an ID field that is sequential from 1 to 83 but then the ID suddenly jumps to seemingly random number like 1075.

The autonumber is meant to guarantee a unique record identifier larger than the all previous numbers, but it is not intended to provide a sequential set of numbers that you can use as a counter field.

It is not recommended that you use the autonumber as a meaningful value within your application. If you would like to use a sequential ID value, we’d suggest using a Data Macro to maintain your own sequential ID field.

Example: Create Table1 as shown where MyID will be the field that you’ll use as the sequential ID.

Choose to Add an On Insert data macro within the design of Table1 and then add the following code:

With the data macro in place. Toggle the table to datasheet view and start populating records by entering data in Field1. Note that MyID generates sequentially regardless of the autonumber.

Also, if you wanted to seed MyID with a specific value, such as 1000, you would simply need to adjust the initial value of varNextMyID within the data macro.

The resulting table:

Comments (5)
  1. It is what it is, exactly the same in ALL databases, not just Access, meant to guarantee a unique record identifier only. The technique listed here is a simple method to implement that will provide sequential set of numbers.

  2. Joseph Dowski says:

    "….is not meant to provide a sequential set of numbers…." ? Are you kidding me ? 🙁

  3. lEAH says:

    It is their way of saying they have no idea why the number jumps around for no reason.

  4. Jeremy says:

    Any ideas on what are some possible triggers the jump in the ID values? I’ve noticed two jumps in an old classic ASP application that I’m refactoring.

  5. Shakuna says:

    How to do this in Access 2007?

Comments are closed.

Skip to main content