Autonumber not going in order

[Guest]

Autonumber on my table in access skipped from 1017 to 1019 why would this
happen. But in my form it shows 1018

 

[Guest]

An autonumber value is assigned when you begin to insert a new record. If
you then abandon that record before saving it by pressing the Esc key, or you
delete it after saving it, the autonumber value is not re-used for the next
record unless the database is compacted before the next record is added. An
autonumber is designed purely to guarantee unique values, not sequential
ones. If sequential values are important you should not use an autonumber,
but compute the values. In a single user environment this can be done when
inserting a new record via a form by looking up the last value in the form's
BeforeInsert event procedure and adding 1, e.g.

Me.MyID = Nz(DMax("MyID","MyTable"),0) + 1

In a multi-user environment this is not reliable as conflicts can occur.
I've posted a demo of the commonly used solution to this, which involves
storing the last number in an external database which is opened exclusively
in code to get the next number, at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Guest]

Hi Ken,

...which involves storing the last number in an external database which is opened exclusively in code to get the next number...

This shouldn't really be necessary. Try the method shown in Form 2, in this
sample database:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________