Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Autonumber (Access 2003)

    I want to change the starting figure of autonumber. The problem isn't that I don't know how to do it, it just won't work. It keeps going back to the next autonumber, instead of the next one after the appended number. I've performed this operation on numerous occasions and it has worked, but this time it won't. Any ideas what could be wrong. This is the first time I've done it in Access 2003 by the way. Cheers.

    Rob

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Autonumber (Access 2003)

    I assume you are dealing with an MDB file, and not an MSDE or SQL Server. In that case you might try installing the most recent fixes for the Jet engine - SP8 - there were some fixes in it to correct autonumber behavior under unusual circumstances like yours. Out of curiosity, when you append a record with a specified value, does the record get that value, or does it get the next consecutive number? Finally, autonumbers are intended only for internal use as pointers or keys, so you may want to consider another strategy is setting the value for your field.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autonumber (Access 2003)

    Yes, it does append the correct value. You see, I've been testing the database, so I used up some numbers. I wanted to set the Autonumber to 500, so appended 499 from a table, then deleted, thinking the next number would be 500, but it came up with the next number after the one I had already used.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Autonumber (Access 2003)

    Oops - I mislead you, as that is normal behavior. If you want to set the number, you need to leave the record with 499 in until you append the first record, as the autonumber doesn't get set properly until it discovers there is a 499. Try it and see if that doesn't make things work correctly.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autonumber (Access 2003)

    No, still not working. I've just created a simple database with just a Autonumber field, and name in it, and appended a number to it, and it worked fine. It must be something in my table that it doesn't like, but I can't find out what.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Autonumber (Access 2003)

    It is possible you have some corruption in your database - try doing a compact and repair and see if it behaves better.
    Wendell

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber (Access 2003)

    Hi Rob

    Give this a try:

    ACC2000: How to Use an Append Query to Set Initial Value of an AutoNumber Field

    Microsoft Knowledge Base Article - 209696

    http://support.microsoft.com/?kbid=209696

    HTH

    John

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Autonumber (Access 2003)

    It is possible (or probably) that you are suffering from the same problem as Ian. Make sure you have the latest SR. There is a free utility on my website (see link below) that will reset the autonumber after the append.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autonumber (Access 2003)

    Thanks Liquorman. Only just got round to addressing the problem.

Posting Permissions

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