Autonumber (2000)

Topic

New Reply

Forgive a very basic question but how exactly do you reset an autonumber field back to 0. The situation is that I have developed a front-end form for an access database to record regular user input records and have put an autonumber field in one of the fields to give them a tracking number for their records. I have checked the help files but all that came up with was a way of creating a new table with a reset autonumber field and appending a query to the main table which would replace the original autonumber with the reset one. this didnt appear to work when I tried it. I have also tried amending the field to be a text entry to see if that would reset it and checking all the settings to see if changing them would stop it holding the number of records it has had in the database. The reason I need to change this back is that I have used a few of the first records for testing purposes and dont want it to be starting the tracking numbering at 9.

Reply | Quote

Replies

Remove all records from the table to be reset and do a Compact and Repair form your Back-end database .

Reply | Quote

I’ve never succeeded in resetting an autonumber field.

The only solution I’ve got is to go into the design view on the table, copy the autonumber field, paste it at the end of the list (in the same table), delete the original autonumber, move the new one back to where the original one was and save.

HTH

Reply | Quote

If you want to have a sequential numbering of you records without gaps, you may not use an autonumber field. If the user begin to enter a record and change his mind and undo (escape) his entering, the autonumber for this record is gone. Next new record will have the following number. lets say you have 3 records, number 1, 2, 3 and the user begin to enter number 4. If he escape, the next number will be 5 and 4 is gone for ever. Even Compact and repair will not fill the gaps. The only thing Compact and repair does is resetting the number to the first following number. If you Compact and Repair before entering number 5, then 4 will be regained.
If you want a numbering without gaps you have to generate you own numbering.

Reply | Quote

Another option to reset autonumbers: Make a copy of the table with the AutoNumber and then Paste Table As, structure only. Create an append query based on original table to append all records to new, empty table. Drag all fields from original table to query grid. To maintain original sequence, sort by the original AutoNumber field, but leave the “Append To” block blank. Run append query. All records will be copied to new table, the AutoNumbers will be in sequence with no gaps, starting with 1. Then delete old table, rename new table, & compact database. Note: If sequence of autonumbers is NOT important, then you don’t need append query. Open table in design view, simply delete AutoNumber field. Insert new field of AutoNumber data type. The new AutoNumbers will begin with 1 and have no gaps.

Reply | Quote