Auto Incrementing Numbers (missing numbers)

[Doug]

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.

 

[Joan Wild]

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

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.

 

[John Vinson]

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.