autonumber skips numbers

  • Thread starter Thread starter Rose
  • Start date Start date
R

Rose

Please help.
We created a database and used autonumber to keep track of
the number of records (i.e. order numbers) and set
autonumber as follows:

no duplicates, indexed, increment

After record 15 however it goes straight to number 17 and
refuses to show number 16 even though number 17 really is
record number 16.

Hope this makes sense and hope very much someone knows how
to solve this!
 
AutoNumber values are allocated when you start adding a Record. If you
cancel the record addition half-way, the value is generally destroyed and
NOT recovered.

Note that the only purpose of the AutoNumber Field is to provide uniqueness
to each Record. AutoNumber WILL develop gaps (as explained above) and can
even become random. In fact, database users (at least, my users) should not
see the values of the AutoNumber Fields (even though I use an AutoNumber
Field in virtually all Tables I designed).

If you want consecutive sequence of numbers, AutoNumber is certainly the
wrong data type to use.

Try using Long and use Form Events with suitable code to create the "Next
Number).

There have been a number of posts on this custom number sequence. Search
Googol for the posts.
 
After record 15 however it goes straight to number 17 and
refuses to show number 16 even though number 17 really is
record number 16.
Click to expand...

Access uses an autonumber once. If a record is deleted
during data entry, it is gone forever. When I first
began, I was similarly confused by this behavior.

It's really not a limitation, however. The purpose of the
AutoNumber is simply to give a unique key for each record,
and it really doesn't matter if that is "1023", "1024",
or "JOHNSMITH".

The current record number and the total record count is by
default shown in datasheet and form views on the status
line at the bottom of the screen. It can also be found
using a total query, selecting the Key Field, and "Count".

HTH
Best regards.
Kevin Sprinkel
Becker & Frondorf
 
As Van said, you are using Autonumbers incorrectly. An Autonumber field will
never tell you the number of records. An Autonumber field can be used to
distinguish a particular record, but it should never be use to guarantee the
uniqueness of the data contained in a record. To do so often violates
relational design theory, and can result in bad data.
 
Back
Top