Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change field to autonumber (2002)

    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.

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

    Re: Change field to autonumber (2002)

    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.

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Edmonton, Alberta
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change field to autonumber (2002)

    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.

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

    Re: Change field to autonumber (2002)

    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.

Posting Permissions

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