Auto Number Reset

[Guest]

I've got a table that has a list of entries with an ID number that is set to
Autonumber. As a nature of this table items are regularly added and deleted
from the table. This results in large gaps in the ID number, where the ID
goes from say 7 to 13 then to 28, etc. Is there a macro or something I can
utilize to every so often reset the ID numbers, so that the return to
sequence... (1,2,3,4,...)?

As it is set up currently, altering the ID number will not cause any
problems with other tables, reports, etc. So that's not really a concern at
this juncture.

Or is there a way to make my own macro to run in a form and/or website
setting, so that I could make the ID field a standard number field, and then
just have the macro look for the lowest unused number, and set that as the ID?

Thanks in advance for any advice offered.

 

[Douglas J. Steele]

Why does it matter? Even with the gaps, the autonumber fields are providing
what they're supposed to: a (practically guaranteed) unique value that can
be used as a primary key. The actual value of the autonumber shouldn't
matter: in fact, it's usual not to even display the value of autonumber
fields.

If you're determined to do it, yes, the best approach is to change the
Autonumber field into a Long Integer instead, and "roll your own". You'll
need to use VBA, though, not a macro. Put logic in the form's BeforeUpdate
field to determine what the next available number is.

 

[Roger Carlson]

If you need a sequential numbering system, DO NOT use autonumbers. They
will develop gaps that can't be recovered. The only way to do so is to
delete the field, then recreate it. It will autofill with sequential
numbers. Nevertheless, I do not recommend this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Having sequential numbers isn't a major concern at the moment, so I can
likely leave it as is for now, and alter things later if need be. I was more
looking at things from the perspective of potential problems down the road,
when ID numbers may get up in the 10,000 range or higher.

I'll keep this on the back burner for now, but keep in mind the routes
needed. Thank you both for your help.

 

[BruceM]

Autonumber is Long Integer, which mean it can handle values several orders
of magnitude beyond 10,000 (I think the actual number is over 2 billion).
It is just not a concern.

 

[Douglas J. Steele]

Autonumbers can be as large as 2,147,483,647. Once you reach that value, the
next value generated will be -2,147,483,648, and then they'll start
incrementing (i.e. -2,147,483,647, -2,147,483,646, -2,147,483,645) until
they reach 0.

In other words, I wouldnt' worry about it!

 

[Bill Mosca, MS Access MVP]

Don't worry about 10, 000 records. An autonumber is a long integer and can
go uo to 2,147,483,647

 

[Guest]

My future concern is less about Autonumber handling it and more about some of
the less computer savvy members of the office getting concerned when they see
that. I'm an IT guy in a non-IT field; so keeping things simple and
unintimidating is a big focus for when I start changing stuff.

As it stands right now I really don't use the ID's for much, and the value
isn't really a concern, so I'm not going to sweat it. If someone comes to me
with concerns regarding it, then I'll deal with the issue then.

Again thank you all for the help, it's interesting to note just how high the
autonumber field will go for me.

 

[Guest]

As Doug indicated in his first post, "it's usual not to even display the
value of autonumber fields." A user should never see an autonumber on a form
or report. Set the visible property for the textbox to No. This will save you
from receiving calls from panicked users, who think that a bunch of records
have disappeared (due to the gaps in the numbers).

My standard is to set the font color for textboxes that I am not displaying
to red. That way, they're very easy to spot in form design view.


Tom Wickerath
Microsoft Access MVP

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