Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change autonumber (Access 2000)

    I want to change the autonumber of the field in table to 1000
    and to preserve the old number from 1 to 22.Is there any code in Visual basic to do
    that?. This concerns my tables Houses where houseid up to now is 22.I want the next
    autonumber to be 1000.
    Can somebody help me ?

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

    Re: Change autonumber (Access 2000)

    You can create an append query that adds a record with value 1000 for the AutoNumber field. If you prefer, you can run this in VBA:

    Dim strSQL As String
    strSQL = "INSERT INTO [NameOfTable] ( [NameOfField] ) VALUES ( 1000 )"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    Replace NameOfTable with the name of the table and NameOfField with the name of the field.

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

    Re: Change autonumber (Access 2000)

    It isn't that easy to reset the Autonumber next value to a specific number. One technique is to use an append query which specifies a value of 999, then delete that record. Then the next number used will be 1000. Note however that if someone starts entering a record and then cancels, that number is used up, and the next number will be 1001. If you are trying to manipulate autonumbers, you might want to reconsider and use another scheme such as using the max value of existing records, or storing the next number in a table and doing the increment function on your own.

    PS - you cannot change the number to be a regular integer, add a record, and then change it back to autonumber - Access won't let you.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change autonumber (Access 2000)

    Thanks for the detailed and useful reply

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

    Re: Change autonumber (Access 2000)

    I would just like to add 1 little thought to the answers given by Hans and Wendell, as sometimes there is a problem with appending records to a table in which there is an autonumber (and you force the value for this field).

    I'd use the append query to set the # of the new record to 998. Then I would open the table in datasheet view and manually start adding a new record (you won't actually try to save this record, just start data entry and cancel later). As soon as you start any data entry on the new record, Access will assign the autonumber. Make sure it is 999. If it is, everything is OK. If not, navigate to my website (see below) for a tool that will help you correct the autonumber setting.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change autonumber (Access 2000)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Here here. Amen to that. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I'm not trying to impose religion...it's JUST a saying <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

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

    Re: Change autonumber (Access 2000)

    When will people stop to use autonumber for what it isn't desing.
    An autonumber is only to be used as an ID and it should not matter what it contain.
    If you need a specific numbering, use a numeric field and use the proper techniques to do the numbering yourself.
    Francois

  8. #8
    New Lounger
    Join Date
    Dec 2005
    Location
    Beechworth, Victoria, Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change autonumber (Access 2000)

    What it contains does matter when the ID autonumber has already been used, so the ability to reset an autonumber field is very important.
    Garry

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

    Re: Change autonumber (Access 2000)

    Welcome to Woody's Lounge!

    Sorry, I don't understand what you're saying here. Could you explain? Thanks.

  10. #10
    New Lounger
    Join Date
    Dec 2005
    Location
    Beechworth, Victoria, Australia
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change autonumber (Access 2000)

    Thanks for the welcome. I may have been slightly off the topic - it's now after 3:30 AM and I should have waited till the morning.

    I frequently have to import multiple related tables. In Access 97 I never had a problem but in 2000 the autonumbering gets messed up - the next autonumber ID assigned to a new record has already been used so new records can't be added until i have reset the autonumber field to a higher number than the biggest already assigned in the imported table.

    See also "problem with repeating autonumbers (Access 97)" [Post: 513,879) and Mark Liquorman's website http://www.Liquorman.net under "Tips and Downloads". According to Mark: "The problem with AutoNumbers is a known and documented Access 2000 problem."

    I agree that a manually incremented Id would avoid my problem, but there are a lot of tables in a lot of databases that would need to be changed. Anyway, all I was saying is that it is valid and sometimes necessary to want to set the next available autonumber. Autonumbering is a convenience, it would be more convenient if you could easily set the next number occasionally, especially when it is malfunctioning..
    Garry

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

    Re: Change autonumber (Access 2000)

    OK, thanks for the explanation. I agree that the contents of an AutoNumber field matter if there are records in other tables linked to it.

    I think Francois' remark was directed more to people who want to reset an AutoNumber for aesthetic reasons. They have added some records (for example in a test) and deleted them again. They don't like the resulting gap in the AutoNumber sequence. If prevention of such gaps is really important, it's better not to use an AutoNumber field.

Posting Permissions

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