Auto Numbers
by Linda Quinn
Close Window
Print Page
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-2019, LQSystems,Inc. All rights reserved.