ID Number not consistant, goes from 157 to 24597

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

The Autonumber is not consistant. It goes from number 157 to 24597. I am
trying to make it go in sequence. How do I do that?
 
Sue said:
The Autonumber is not consistant. It goes from number 157 to 24597. I am
trying to make it go in sequence. How do I do that?
Click to expand...

You shouldn't. It's system-generated according to Access's own rules,
and the ONLY thing you can count on is that it won't be duplicated,
making them suitable for Primary Keys. You can't meaningfully add them,
subtract them (etc...). If you need a facility where you can "apply for"
a unique number which is the next in some sequence (especially if not
the only user) then one solution is to use a separate database file,
with exclusive locking, just to give you that. Often it's not actually
that important that numbers are sequential when you really go into it!

Phil, London
 
If you really do need sequential numbering don't use an autonumber; generate
the numbers yourself. Records must be entered via a form for this not in raw
datasheet view, which you shouldn't do in any event. In a single user
application in the forms BeforeInsert event you can put:

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

This can give rise to conflicts in a multi-user environment. Roger Carlson
has a solution at:


http://www.rogersaccesslibrary.com/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb


Or there's one of mine using the second method described by Phil, which is at:


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


Mine also allows the value of next number to be used to be reset.


Ken Sheridan
Stafford, England
 
The Autonumber is not consistant. It goes from number 157 to 24597. I am
trying to make it go in sequence. How do I do that?
Click to expand...

An Access Autonumber has one purpose, and one purpose ONLY: to provide a
unique identifier. This is a rather extreme example of its normal behavior. If
you delete a record, that record's autonumber will never be reused; if you hit
<ESC> an instant after starting a new record, an autonumber will be used up
and will leave a gap; if (as probably happened here) you fill a table using an
Append query, you may get a very large gap. This is intended and expected
behavior.

If you want numbers to be sequential and gapless... don't use an Autonumber.
Use a Long Integer or other appropriate datatype, and use VBA code to
programmatically assign the next number. You'll need to be careful in the code
and make decisions about what you will do when a record must be edited or
deleted (you really DON'T want to renumber records 3 through 22147 when you
realize that record 2 was a test record that shouldn't be there...)

There are plenty of examples on this group and elsewhere for "Custom Counters"
- you can use http://groups.google.com advanced search, or post back with some
details if you need more help.
 
Correction: missing quotes in:

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

Ken Sheridan
Stafford, England
 
Back
Top