Looking to take your VBA skills further?...

Discover twinBASIC — a powerful new development platform that expands on VBA and VB6 with advanced features, modern tools, and enhanced compatibility. Perfect for those ready to elevate their projects or transition from VBA, twinBASIC lets you build on what you already know and take your applications to the next level!

Try out twinBASIC Community Edition - it's free!

Using AutoNumbers properly

        8 votes: *****     3,712 views      No comments
by Keri Hardwick, 01 January 1999    (for ALL VERSIONS of Access)

(Q)     In a table I'm using an Autonumber Field as the primary ID.   But if the user creates a new record and closes the form without saving it,   the Autonumber field still increases by one. This creates gaps in the continuity of the primary key field. How can I:

(a)  stop the Autonumber incrementing itself if the record wasn't created; and
(b)  force a renumber of this field to occupy contiguous numbers if I was delete record(s) from the table?

(A)   There is no such thing as a record number in Access.

Records just "are" - a table is a big, unsequenced bucket. Indexes and queries provide order to the records in the bucket.

Autonumber is simply a way to create a unique identifier for each record. It should NEVER be treated as a meaningful piece of data.

If you need a meaningful sequencer (like to assign user numbers), you have to create it and manipulate it yourself. You will need to stop using Autonumber, and create a sequence number that you increment yourself and apply to the record when it is saved.

You can also capture the numbers from deleted records and re-use  them. If, however, you have the possibility of related records using the number as a foreign key - do not reuse old numbers.

Now, for your db to "work" primary keys do not need to be continuous numbers; they just need to be unique - which is why the Autonumber function works like it does.

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


Have your say - comment on this article.

What did you think of 'Using AutoNumbers properly'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).