Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    change autonumber (Access 2000)

    In my table i have erroneously began the numbering with 6000 instead of with 7000. Is there any possibility that i change the number 6 with 7 in my coloumn clinetid? For example the clinet hving a number 6001 to read 7001 etc.I have a code that resets the numbering from a new line :
    CurrentDb.Execute "ALTER TABLE TblClients ALTER COLUMN Clientid COUNTER(" & rate & ",7001)"
    But my queston is can i somehow change the existng nuber with 6000 with the numbers with 7, I am applying the shortened table to explain what i mean

  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 cannot change the value of an AutoNumber field in existing records. In general, an AutoNumber field is used as a meaningless unique identifier, so the exact numbers don't matter.

    If you don't have other tables that are linked to tblClients on ClientID, you could do the following. Make a backup copy of the database before trying it.

    - In the database window, select tblClients.
    - Press Ctrl+C to copy the table.
    - Press Ctrl+V to paste it.
    - Provide a name, for example tblClients2.
    - Specify that you want to copy only the structure of the table.
    - Click OK.
    - Create a query based on tblClients.
    - Add a calculated field to the query grid:

    C: [ClientID]+1000

    - Select Query | Append Query...
    - Specify tblClients2 as target.
    - In the 'Append To' row, enter or select ClientID.
    - If there are other fields, add those to the query grid; Access should automatically assign 'Append To'.
    - Select Query | Run or press the Run button on the toolbar.
    - Confirm that you want to append ... records.

    tblClients2 contains the desired ClientIDs. You can now delete tblClients and rename tblClients2 to tblClients.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change autonumber (Access 2000)

    Thank you for the explanation.You have written to me in details what to do, however i cannot grasp how could i make the calculated field to the query grid.You have told me that the Autonumber cannot be changed but it seems now that with your description the value might be changed.Am i right ? WOuld you tell me where the calculated field might stand in my query 1 ?

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

    Re: change autonumber (Access 2000)

    The query should be based on tblClients only, not on tblClients and tblClients2 (that wouldn't make sense, since tblClients2 doesn't contain any records yet).
    Instead of ClientID, put the calculated field in the query grid - see screenshot.

    Again, the AutoNumber value of existing records cannot be modified. This query creates new records in tblClients2 with the desired values.

Posting Permissions

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