Auto Incrementing Numbers (missing numbers)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Doug - 16 Feb 2004 20:01 GMT

I have a table with an AIN as primary key, 2 users, and have observed that some numbers in the sequence are missing, eg 1,2,3,4,7,8,10,11,12. Users cannot delete these records so I'm puzzled. Thanks.

Reply to this Message

Joan Wild - 16 Feb 2004 20:25 GMT

As soon as a user starts entering data in a record, the autonumber is
generated. If the user cancels the insert (hitting ESC perhaps), the number
is still used up.

You should not use an autonumber if you care what its value is.

Signature

Joan Wild
Microsoft Access MVP

> I have a table with an AIN as primary key, 2 users, and have observed that some numbers in the sequence are missing, eg 1,2,3,4,7,8,10,11,12. Users

cannot delete these records so I'm puzzled. Thanks.

Reply to this Message

John Vinson - 17 Feb 2004 02:08 GMT

>I have a table with an AIN as primary key, 2 users, and have observed that some numbers in the sequence are missing, eg 1,2,3,4,7,8,10,11,12. Users cannot delete these records so I'm puzzled. Thanks.

This is exactly how autonumbers are designed to work. A deleted record
will leave a permanent gap; even hitting <Esc> the moment after a new
record is started will leave a gap. An autonumber has one function
ONLY - to provide an almost-guaranteed unique key. It's not intended
for human consumption or to be gapless or sequential.

If you want a gapless, sequential number, don't use an Autonumber; use
a long integer and maintain its value using VBA code.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message