Autonumber skipping numbers

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

T - 21 Jul 2005 15:58 GMT

I built a web app that uses the autonumber for an order id in an Access 2000
db. Occasionally, a number or two is skipped (the customer insists they are
not deleting records). The asp code is doing a rs.update to insert the new
record.

I have done quite a bit of research but have not found this specific case.
Has anyone seen this or can explain what is happening?

Thanks in Advance!
T

Reply to this Message

Lynn Trapp - 21 Jul 2005 16:01 GMT

The value in an Autonumber field is assigned as soon as a user starts
entering data in the other fields. If the user fails to finish completing
the record and leaves without causing the record to be saved, the value in
that field is lost forever. Autonumbers are designed to provide a nearly
100% unique value for linking tables together. It is not good to attempt to
use it as a sequential numberer... it will eventually develop gaps.

Signature

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

>I built a web app that uses the autonumber for an order id in an Access
>2000
[quoted text clipped - 9 lines]
> Thanks in Advance!
> T

Reply to this Message

Rick B - 21 Jul 2005 16:12 GMT

An autonumber is incremented when a user starts adding a record. They most
likely started adding a record, then hit escape.

Auto numbers WILL have gaps. They were never intended to prevent gaps.

In most cases, your users should never even see an autonumber field since it
is just intended to allow unique keys for your records. Autonumbers should
not be used for any field that the client will be referenceing (invoice
number, customer number, etc.) For those, use some variation of DMAX to
obtain the highest number and add one to it.

Signature

Rick B

> I built a web app that uses the autonumber for an order id in an Access 2000
> db. Occasionally, a number or two is skipped (the customer insists they are
[quoted text clipped - 6 lines]
> Thanks in Advance!
> T

Reply to this Message

T - 21 Jul 2005 18:15 GMT

Yep, that is what I am finding out that I should not be using that as a user
viewable number. I'll have to make some changes to address that as is
suggested. Thanks.

Now, in this case, the web app is adding the entire record at once. So the
user can not halfway insert a record. Perhaps there was some type of race
condition that occured that caused this?

> An autonumber is incremented when a user starts adding a record. They most
> likely started adding a record, then hit escape.
[quoted text clipped - 20 lines]
> > Thanks in Advance!
> > T

Reply to this Message