Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonumber (2000)

    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.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber (2000)

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

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Twickenham, UK
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber (2000)

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber (2000)

    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.
    Francois

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Autonumber (2000)

    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.

Posting Permissions

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