Leno01: If you don't mind me asking; why?
Also, I just about deleted this thread based on the title:
"Key field password " in that it reads as if you are asking for a password (which doesn't appear to be the case).
The entire point of the autonumber is that it is never repeated, ever and that it is not suposed to be really used for anything meaningfull. For example, in the lab, there are lot numbers, these are non-repeating and sequential; however, I do not use these as the primary key because the lot numbering system might (and has) changed. Each record has the autonumber assigned as the primary key and it is this primary key that I have linked. Let them change the lot numbers all they want, I still have a fool proof tracking on the records.
- Backup your database.
- Open the table you wish to reset, delete all records. As this is in a relationship, all records in all other tables that depend on this one will have to have all of the related records deleted as well or you risk data orphans.
- Close the table
- Make a copy of your table that you wish to "reset"
- Open only the copy in design view
- Change the autonumber to numeric(long)
- Save
- Open/change the copy in/to normal view
- In the newly changed field enter the value of 0 leave remaining fields empty. There should be no other records.
- Save and Close the copy
- Using the wizard create a simple select query on the copy
- Save and run - it should only have the one record
- Switch to design view for the query
- Change the query to an "Append" query selecting the original table to append to.
- Run the append query
- Open your original table and delete the "0" record.
- Your next new record will start with "1" in the autonumber field.