Auto Incrementing Numbers (missing numbers)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

--
Joan Wild
Microsoft Access MVP

Doug said:
I have a table with an AIN as primary key, 2 users, and have observed that
Click to expand...
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.
 
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.
Click to expand...

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