Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    44

    Unanswered: Make Autonumber value smaller for next record.

    Hello. I have a question about changing the value of autonumber. I have a db that has record 172 right now, and that's where the records stop. I want the next record that is added to be 173. However, the next record I add is 179, because of my testing and adding test records and then deleting them in the database, thus forcing the autonumber to increase unnecessarily.
    My question is how can I make the autonumber value for the next REAL record I add be 173 instead of 179? Or can this not be done?
    Do you see my point?

    I have already been through the access help and changed the autonumber to start at 229 (just a random number I picked to see if I could do it), which I can do no problem, but that is not the kind if help I am looking for. I want to make the autonumber of my next record smaller than what it currently is. Ok, I will stop now. Any help will be appreciated. Thanks.

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    With autonumber fields, once the number has been used it cannot be used again. You just need to accept the fact that the autonumber will not match up with the number of records. If you need this functionality then you will have to generate the numbers yourself rather than rely on the autonumber feature.

    Steve

  3. #3
    Join Date
    Aug 2004
    Posts
    44
    Alright, that's all I needed to know. You would think though that there would be an option or workaround to re-use an autonumber. But oh well, I suppose I can just let it go. Thanks.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by thespursfan
    Alright, that's all I needed to know. You would think though that there would be an option or workaround to re-use an autonumber. But oh well, I suppose I can just let it go. Thanks.
    Well ..... If you copy the structure of the table to a new table name and then paste the existing records into it. Delete the old table and rename the new one ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Aug 2004
    Posts
    44
    That's what I was thinking of as well. I will play around with it and see if I can get it to work. Also, just where exactly is Acess storing the values of the autonumber and how does it know that a number has been used already and its time to increment? It has to be designated SOMEWHERE, right?

    Thanks.

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Here you go, straight from the Access helpfiles.....your solution. And I have done this before so have a good time.

    Change the starting value of an incrementing AutoNumber field
    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.

    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.
    How?

    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.


    Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.
    How?

    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.

    Delete the temporary table.


    Delete the record added by the append query.


    If you had to disable property settings in step 3, return them to their original settings.
    When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.

    Note If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.

    That's the end.....
    Have a nice day,
    Bud

  7. #7
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Another easy way is to change the data type to number, then compact the db, delete all the data from that column and then change the data type back to autonumber. !!!

Posting Permissions

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