Autonumber

[Knew2]

IS there anyway to reset the autonumber feature in a table back to zero? I am
using the autonumber feature as an accession number with the following format
07-####. As the end of the year approaches I need to archive this years
records and start with 08-###0. Can this be done easily?
TIA

 

[Jeff Boyce]

The Access Autonumber is designed to provide a unique row identifier. It is
NOT guaranteed to be sequential (if this is what you mean by an "accession
number"). It is generally unfit for human consumption.

If you need a guaranteed-sequential number, you'll have to create your own
procedure (easy to find by searching on "Custom Autonumber").

I'm not sure what you are doing when you say "need to archive ...". Does
this mean you are physically (re-)moving records? If so, this can create
issues later when you want to research historical information. An alternate
approach is to simply not show the "old" records. Use a query to limit what
records you/your user sees.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[FSt1]

hi
i hate autonumber. i hate wizards. BUILT IN ACCESS STUFF USUALLY DOES THINGS
THAT YOU DON'T WANT TO DO OR WANT TO CORRECT. (am i screaming loud enough.)
for that reason when i design a database i chose my own autonumber or
"nextnumber". I create a table called config and put all of my nextnumbers
and any other references in it. SAP does it, peoplesoft does it, why not me.
I usually had a "next" button which used dlookup to reference the "next
number" from config. after code had run, i added 1 to the nextnumber in
config. If you canceled "new record", one was not added to nextnumber.
advantage to having your own autonumbers or "nextnumbers" is that you can
open the table and reset them to zero thus avoiding builting access stuff.
hummmmm......

my thoughts
Regards
FSt1