Auto-Number fields in Access

Auto-Number fields in Access.

by Linda Quinn

Close Window

&Gaps in Auto-Numbers
Resequencing Auto-Numbers
Special Formatting for Auto-Numbers

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 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.

=================================================================
This content was created by Linda Quinn of LQNet.

See http://www.lqnet.com for a great collection of articles on this and other topics.

=================================================================

====================================================================
Want an expert to help with your project? LQ Systems, Inc. Business Solutions
====================================================================

Auto-Number fields in Access.

Contents:

Copyright © 2006-2008, LQ Systems,Inc. All rights reserved.