Microsoft Access 97 AutoNumber Question

[tdin]

I have an AutoNumber field in a Microsoft Access 97 table. I added a record and used the UNDO button. The AutoNumber incremented anyway, so the next record was out of sequence. For example now I have 5,6,7,10,11. <br>
<br>
Is there a way to edit the AutoNumber or to reset the counter to a new starting value without losing the numbers on existing records?

 

[Hole19er]

tdin..<br>
<br>
You might find your answer in Access online help.<br>
<br>
Search on 'autonumber' and select 'change the starting number of an incrementing autonumber field....'<br>
<br>
Basically this is how it's done:<br>
<br>
For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.<br>
<br>
1 Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.<br>
<br>
2 In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.<br>
3 Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.<br>
<br>
Note If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.<br>
<br>
4 Delete the temporary table.<br>
<br>
<br>
*5 Delete the record added by the append query.<br>
<br>
* (I DON'T delete the added record, I simply add a record in that field in the datasheet view.<br>

 

[tdin]

Thank you Hole19er! I will give that a try.

 

[tfewald]

You could also view the layout of the table, delete the field with the offending (missing) autonumber critters, and then recreate one in the same place. Everything would be renumbered again.<br>
<br>
Tom

 

[JazzMan2]

you guys, <br>
<br>
I just would like to put my two cents in about auto numbers. They can really be a pain in the butt. I would like to recomend using just a number and then write some code to generate the record number. In my opinion it is a bit easier than working with auto number.

 

[NormDuffy]

Simple way is to create an append query and type the missing autonumber in the field name. Works great and is so simple and easy.