autonumber skips numbers

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!
 
V

Van T. Dinh

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

Kevin Sprinkel

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.

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
 
S

Scott McDaniel

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top