Populating an AutoNumber column with custom numbers

[mmurn]

Hi,

I am a beginner working with MS Access.

I have a table with member information for a local "vineyard society" (sorry for my bad English).

Every existing member has an ID number (Id field in the table). The field type is currently set as Number (Long Integer). Is it possible to keep the current Id Numbers and change the field to AutoNumber so it will automatically increase when I add a new member?

The problem is that the current Id numbers aren't consecutive. First member has the Id: 4, second has 5, third has 8, fourth has 13.

I want every member to keep his current Id, I don't want to start from scratch. I just want the Ids to autoincrement after the last member's Id.

I tried changing the field type from Number to AutoNumber but to no avail. I tried creating an AutoNumber field and appending the Id field to the new AutoNumberId field and it doesn't work either.

I know there should be a simple solution, I've googled but can't seem to find the right solution for my problem.

Thanks for your help.

[zmbd]

Right-click on the table in the Access Object Navigation Pane
[Copy]
Name this - "Backup_tablena mehere"
Select data and structure.

Right-click on the table in the Access Object Navigation Pane
[Copy]
Leave the default "copy of... " name
select "structure only"

Open the "Copy of...." in design view
Change the field you want from number(long) to autonumber
save

Use the wizard to make a simple select query on the original table in question. Run this query to ensure that all records and all fields are returned.

Open this query in design view, in the ribbon, select append, select the "Copy of.... " table and run.

This should move all of your records into the "Copy of... " table.
The next autonumber assigned will be the (last number(long)+1) in the autonumber field from the old table.

Once you have verified that all of your data has transfered:
Deleted or rename the original table.
Rename "Copy of... " the original table name.
Delete the backup copy of the original table.


If the data table is HUGE... then MAKE A BACKUP OF THE DATABASE FIRST!!!
In fact, one should never, never, ever, work on the production copy of any database. Always work on a copy of the database.
-z

[mmurn]

Thank you very much! This has worked a treat. This was actually the best and specific help I've ever received on internet.

Thank you again!