Gaps in Auto-Numbers

If you define a field as type Auto Number, Access will assign a unique, sequential number to a record when it is created. These numbers should not be relied on for anything other than a unique identifier.

If a record is deleted, that number will be missing. Auto numbering will not go back and assign missing numbers.

Also, if you start to create a new record and then cancel, the auto-number may be used up. It will be missing from the set of numbers.

Gaps in auto-numbers are very common. There isn't any Access feature that allows you to fill-in these missing numbers.

Resequencing Auto-Numbers

However, it is possible to restart auto-numbers from 1 again:
* Remove all records from the table (Copy them if you don't want to loose them).
* Compact the database [Tools --> Database Utilities --> Compact and Repair database].
* Use an APPEND query to add your original records into this newly compacted table. (Don't append the original auto numbers)

Special Formatting for Auto-Numbers

What if you want auto-numbers that start with a prefix, such as IT-100 or PT-250?

In the database design, use the format property of the auto number field.

For the examples mentioned here you would enter "IT-"# or "PT-"# in the format property field.

Copyright © 2006-2018, LQSystems,Inc. All rights reserved.

   Printer Friendly Page