reset autonumber

[rodchar]

hey all,

is there a way to reset an autonumber field to 1 thru any kind of code?

thanks,
rodchar

 

[Pat Hartman\(MVP\)]

You can use the reseed method. But, autonumbers are only meant to provide a
unique identifier for a record. They are not intended for any other purpose
so resetting the seed indicates to me that you have a design flaw in your
application.

 

[DawnTreader]

Hello

but what if i delete a record and then want to re use that number? or in my
case when teaching a class and a student makes a mistake or needs to reset
the numbering to get the proper results in quiries and reports, how?
deleteing the autonumber and adding a new one is one way, but that can be
just as messy as the wrong numbers from the mistake.

 

[Allen Browne]

If you deleted the last number, you could try compacting the database. That
works in some versions of Access.

If the number you deleted is not the last one, you do not want Access
changing all the remaining numbers down. For example, if you delete client
53, you do not want Access changing client 54 to 53, and 55 to 54, and ...

As Pat explained, you need to think of autonumbers as providing a unique
number to identify a record, not providing a complete sequence of numbers.
Because Access is a multi-user program and assigns the number as soon as you
*start* to add a new number, that number will never be reassigned if you
abort the new record. The numbers will not be sequential.

If you want to reset the Seed of the AutoNumber, you can do so in code. In
fact, you can set it to a lower value where it will give you duplicates of
the records you already have, which messes up your database, i.e. you can no
longer add new records to your table.

For an example of how to loop through your tables and check that this
situation has not arisen, and fix the problem autonumbers by resetting the
Seed correctly, see:
Fixing AutoNumbers when Access assigns negatives or duplicates
at:
http://allenbrowne.com/ser-40.html

 

[John Vinson]

Hello

but what if i delete a record and then want to re use that number?

Then use a Long Integer, and don't use an Autonumber.

or in my
case when teaching a class and a student makes a mistake or needs to reset
the numbering to get the proper results in quiries and reports, how?

Then use a Long Integer, and don't use an Autonumber.

deleteing the autonumber and adding a new one is one way, but that can be
just as messy as the wrong numbers from the mistake.

Autonumbers have one purpose, and one purpose ONLY: to provide a
guaranteed-unique key. If you're assigning ANY other meaning to the
value of an autonumber, you're making a bad mistake.

You can use VBA code in a Form to increment a numeric field if you
want one that's editable or gapless.

John W. Vinson[MVP]

 

[rodchar]

thanks everyone for the helpful replies.