Change Number to Autonumber (2000/SR1)

Topic

New Reply

I have a database that we use to keep track of reports that are prepared by hand. Each report has a unique control number. We would prepare these blank reports with the control number and print them out. After they were filled out, someone had to enter the information into the database.

Because of this, the control number field was set up as a Number field. Now, we want to have everyone enter their reports on the computer and not use the paper files anymore, (what a concept). Now I need that field to be an AutoNumber field. I know that I can’t change it but how do I retain my old records, over 500, with their numbers and set it up so the new records can be entered with the AutoNumber? Does anyone know a work around? Thanks.

Reply | Quote

Replies

In Access 97 you can make a copy of your data table, delete all of the records from the original then change the data type of your ID field to Autonumber and use an Append query to replace the data from your copied table. I think that this works OK in A2K as well but I strongly recommend that you test it out on a copy of the DB first!!!

HTH

Peter

Reply | Quote

If the user has referential integrity set for relationships involving the original table, it will have to be turned off temporarily, otherwise Access will either refuse to delete the records, or (if cascading deletes have been specified) remove all related records!

Reply | Quote

Peter,

That worked very well. Thank you.

Now, here is the next problem. Back when this system was created and just done on paper, the numbers that were used in the beginning incorporated the year, in the form 2001101, 2001102, etc. Last year we dropped that and just went to a number. These old numbers were all entered in the database. Now what is happening the next autonumber that comes up is 2002582, for example. I tried starting all over again, moving the old number into a seperate field (OldNumber) so we would still have that record and then assigning new numbers into my PlantControl# field. When I got all done and started a new record, it still wants to give me the next 7 digit number. Is there a way that I can roll this back? Thanks.

Reply | Quote

In theory, an AutoNumber field should start at the next available number after a compact and repair. There are problems with this, however. See AutoNumber Field Is Not Reset After You Compact a Database.

Reply | Quote

Hans,

The fix didn’t quite work, it was for 2002 but I did it anyway, and if I followed the directions correctly, it reset the beginning number back to 1, which wasn’t what I wanted.

I did finally get what I wanted by using brute force and ignorance. I kept compacting the database and then appending the records. Not sure how many times I did this, I know it was more than twice, and it finally “stuck” and is giving me the correct AutoNumber. I’ve got what I needed. Big Thanks to you and Peter for all of your help. Very much appreciated.

Reply | Quote