AutoNumber Dilemma

[tjones]

I was testing a new database with "test information". Now that I am ready to enter the actual information (I deleated the test data) the field continues numbering from where the test data ended. How do I reset this field to 1.

 

[plog]

Why? What's so magical about 1? Or any other number? How come you have to start from a partiuclar point? Will this affect your data in some way?

 

[tjones]

When your told to start at 1 you do

 

[boblarson]

A quick way is to delete everything from the table(s) and compact and repair. But if Autonumbers are viewable and they can't just exist then you shouldn't be using them.

(by the way you can reset your database fairly quickly using my Free Database Reset Tool, if you want - available here:
http://www.btabdevelopment.com/ts/freetools

 

[harrell.geron]

A quick way to reset AutoNumbers?
Copy the table, paste structure only.
Renamed the table if needed. Append
the new table with the data you need.

 

[vbaInet]

A quick way to reset AutoNumbers?
Copy the table, paste structure only.
Renamed the table if needed. Append
the new table with the data you need.

How is that quicker than what boblarson proposed?

 

[Galaxiom]

Compact and Repair does not always reset the autonumber seed since Access 2002.
Microsoft's advice is to copy the structure and append the records.

http://support.microsoft.com/kb/812718

 

[harrell.geron]

Bob Larson "Reset" tool looks good but it is for a lot more "resets" than just renumbering AutoNumbers. My suggestion is very simple and quick.
Lets say TableA was made by adding data from here, there, and other places.
TableA contained AutoNumbers at a default when you built TableA. Now you want FieldA in TableA to be in alphabetical order and the AutoNumbers to give the Alaphebtic order. How to fix that quickly?

Right click on TableA to copy, then paste with a default name "Copy Of TableA", use only "Structure". to make a blank table with the same fields.
Then do new query to Append TableA to "Copy of TableA" Include all the fields needed, but omit the AutoNumbered field, and choose the Alphabetic field to be "sorted" in Alphabetic ordered. When you then run the query
you will have what you chose. If it checks out right, the remove TableA and Rename "Copy of TableA" to just "TableA"

You're now done. The Autonumber field now matches the Alphabetic order.
If Alphabetical did not matter, just choose the sort order you what you wish.
Very quick, and you did not have to quit your Database to run a external Reset. It will work on any version of Access.

 

[John Big Booty]

If you are concerned about having a sequential series of numbers, you should be aware that there is no guarantee that the native Auto-number facility will provide this. Here's what Allen Browne say on the subject; "In Access 2000 and later, an AutoNumber field may begin assigning extreme values, negatives, and even duplicates. The attempt to assign a duplicate fails if the field is primary key, so it may not be possible to save new records in an affected table."

If you are looking for a sequential series of numbers consider using the Dmax() function plus 1.

The following Code in the Form's On Current Event Should do the trick;

    If Me.YourCounterField = 0 Or IsNull(Me.YourCounterField) Then
        Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), [B][COLOR="Red"]X[/COLOR][/B]) + 1
    End If

Note; Replace X with your seed number (the number you wish your series to start at).

 

[John Big Booty]

...and once you have finished testing your DB simply deleting your test records is all you need to do to re-set the numbering to your seed number.

 

[rodmc]

A quick way is to delete everything from the table(s) and compact and repair. But if Autonumbers are viewable and they can't just exist then you shouldn't be using them.

(by the way you can reset your database fairly quickly using my Free Database Reset Tool, if you want - available here:
http://www.btabdevelopment.com/ts/freetools

cant argue with that, in fact Im bookmarking that to quote from now on in work