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: