Change field to autonumber (2002)

Topic

New Reply

How can I change a number field to autonumber? I want the new autonumber field to keep the same number as the current number field.
Thank you in advance.

Reply | Quote

Replies

You can’t change an existing field to an AutoNumber field. You could do the following; this will only be easy if the table isn’t involved in relationships with other tables.

– Select the table in the database window.
– Copy it (Ctrl+C).
– Paste it (Ctrl+V), specify a name and select the option to paste the structure only.

– Open the new table in design view.
– If there are indexes involving the number field, delete them.
– Delete the number field.
– Create a new field of type AutoNumber, and give it the same name as the field that you removed.
– Close and save the new table.

– Create a query in design view based on the old table.
– Select Query | Append Query and specify the new table as target.
– Drag all fields or * to the query grid. Access should automatically assign the target fields.
– Select Query | Run or click the Run button on the toolbar.
– You can discard the query or save it.

– Delete the old table.
– Rename the new table to the name of the old one.

Reply | Quote

I don’t think that would work. I have another idea but don’t know how to do it. The field I want to increment the number in is in a subform. Instead of have an autonumber can I have it take the last number – in the table and add 1 to it?
Thanks Hans for always responding and being such a big help.

Reply | Quote

You could use the Before Update event of the subform for this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim lngID As Long
If Me.NewRecord Then
lngID = Nz(DMax(“ID”, “tblSomething”), 0) + 1
Me.ID = lngID
End If
End Sub

Here, tblSomething is the name of the table on which the subform is based, and ID is the name of the field that you want to increment.

The disadvantage is that the number will only be assigned when the new record is saved. You could use the Before Insert event instead, but that would be risky in a multi-user environment – two users could easily assign the same number to different records.

Reply | Quote