Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Change Number to Autonumber (2000/SR1)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change Number to Autonumber (2000/SR1)

    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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change Number to Autonumber (2000/SR1)

    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!

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Change Number to Autonumber (2000/SR1)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Change Number to Autonumber (2000/SR1)

    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.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Change Number to Autonumber (2000/SR1)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •