I need to convert a number field to autonumber

[jglazier]

I have several existing tables with data input. I need to convert an
existing number field to an autonumber field. If I can not accomplish this
and must create a new autonumber field, how can I limit the view to show the
old numbers on existing data and new autonumber on new entries?

 

[Allen Browne]

You cannot convert the field from Number to AutoNumber if it contains data,
so the process is to create a new table with the AutoNumber field, and
populate it with the old records.

1. In the Database window, select the table on the Tables tab.

2. Copy (Ctrl+C), and Paste (Ctrl+V), providing a new name, and choosing:
Structure Only
so the data is not copied.

3. Open the copy in design view. Change the Number field to AutoNumber. If
Access won't let you do this, then delete the Number field, save, and then
add an AutoNumber field using the same name. Save Close.

4. Create a new query into the old table.
Change it to an Append query (Append on query menu.)
When it asks, tell it the name of the new table to append to.
Access adds a new Append To row to the grid.

5. Drag all the fields into the output grid.
Verify that each field maps correctly to the field in the new table.

6. Run the query.

After verifying the data is right, you can delete the old table, and rename
the new one with the same name. You will need to delete any relationships
the old table was involved in, and recreate them for the new table.

 

[BruceM]

If there is a lot of related data (in which case recreating the data could be
tedious)perhaps a simulated autonumber could be used. A text box with its
control source set to the field you want to convert could contain something
like the following as its default value:
=DMax("ID_FieldName","tblYourTable")+1
Actually, I used =Nz(DMax("ID_FieldName","tblYourTable"),0)+1, but since you
already have values in ID_FieldName I don't see a need for Nz (provided all
of the fields have some value entered).
This should add one to the highest number contained in that field. I have
had success with this in experimental situations, but have not yet deployed
it in an active database.