Auto number 1

[mrk2]

I have a table that holds course dates and times. There is an ID coulmn in that table which was originally an autonumber. The ID is the primary key for the table. When I was upgrading a part of the database, I had to remove the automnumber designation from the ID column and changed it to "number". Little did I know, that you cannot change a field with data in it back to autonumber. My thought on this was is there some way to set the default value of a new record to the max "ID" +1 or if anyone knows how I can reinstate the autonumber with keeping the exisitng ID values that would work also.
thanks
mrk

 

[coco86]

I'm not sure if it's possible at the table level. I played around with it and couldn't come up with anything.

However, you can do it at the form level.

Use the following code in the beforeInsert event of the form

Dim rs As New Recordset
Set rs = CurrentProject.Connection.Execute _
         ("Select nz(max(NumberField)) + 1 as NewID from table")
If Not rs.EOF Then
    Me.[fieldOnFormforNumberField]= rs!newid
End If

 

[mrk2]

That sounds fine, doing it through the form
Can you walk me though this code a little bit? No experience with it. What exactly needs to be changed to match the fields in my database? Thanks


Dim rs As New Recordset
Set rs = CurrentProject.Connection.Execute _
("Select nz(max(NumberField)) + 1 as NewID from table&quot
If Not rs.EOF Then
Me.[fieldOnFormforNumberField]= rs!newid
End If

 

[coco86]

Make sure you have a reference set to ADO using tools => references from the VBA code window

Make sure you have a textbox on your form for the field that is the "AutoNumber" Field. It doesn't have to be visible or enabled. Whatever you've named this textbox will substitute for [fieldOnFormforNumberField]

Whatever you've named the "AutoNumber" field in the table will substitute for NumberField

The name of the table containing the "AutoNumber" field will substitute for table

Everything else stays as is only the red stuff needs substitution

Dim rs As New Recordset
Set rs = CurrentProject.Connection.Execute _
("Select nz(max([NumberField])) + 1 as NewID from table&quot
If Not rs.EOF Then
Me.[fieldOnFormforNumberField]= rs!newid
End If

 

[mrk2]

Brilliant, works perfectly. Thanks for you help