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.
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-2016, LQSystems,Inc. All rights reserved.