Auto number

[dagibbie]

For some reason an entry into a very large database did not save. However,
the auto number for that entry can not be used. The auto numbering system is
skipping that number. Any ideas on why this could happen, and is using an
append query the only way to insert that number?

 

[golfinray]

Autonumbers are simply designed to give you a unique key. If you delete a
record the autonumber gets deleted as well. They are not intended to be
sequential. You could add a record number if needed for each record for
reference.

 

[John Spencer MVP]

Even if you don't delete a record an autonumber can be used up. For instance,
if you start to enter a new record and then cancel the action, an autonumber
is used up.

If you need sequential numbers without breaks, you will need to develop (or
find) a VBA function to generate the number when it is needed. Autonumbers
are designed to generate a unique number within the table and that is what
they are good for.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

For some reason an entry into a very large database did not save. However,
the auto number for that entry can not be used. The auto numbering system is
skipping that number. Any ideas on why this could happen, and is using an
append query the only way to insert that number?

This is intentional and by design. An Autonumber has one purpose, and one
purpose ONLY: to provide a meaningless unique key. As a rule Autonumbers
*will* have gaps; you'll get a gap if you delete a record, or even if you
start a new record and hit <Esc> to cancel the entry. Autonumbers can even
become random; if you replicate a database the next autonumber value could be
-1135228416 and the one after that 331923014.

If you want sequential, user-meaningful values don't use Autonumber. Instead
use a Long Integer value and write code to assign new values.