Start Autonumber at a given value

[luciegiles]

Is it possible to set an autonumber to start from a given value? I would like all numbers to start at 500 and increase by 1 from there.

[NeoPa]

This is not an easy thing to do, nor is it something anyone would want to do if they understood autonumbers.

What you seem to be looking for is a way to have keys starting from a particular point and going sequentially from there. This is not something you would want to use autonumbers for.

[luciegiles]

Thanks NeoPa. I am trying to adapt a database that already exists as the majority of the structure is as I want it. However I would like to change the main identifier (currently assigned as an autonumber) so that the entries I make are easily identifiable - any ideas?

[NeoPa]

The way I would approach this is by converting the autonumber field to a long integer (compatible data type) and take control of assigning the number myself.

Exactly how and where this would be done depends on where and when data is EVER added to that table.

[ADezii]

I accidentally stumbled across this Thread and decided to respond because I have actually used this concept on a couple of occasions. What you are requesting can be done fairly easily, compliments of Allen Browne. The basic logic involves adding a Record number with an AutoNumber Value of 1 less than what is requested then deleting it. The next Record you add will have your requested value as the new AutoNumber. Simply pass 2 Arguments to the fForceAutoNumbe r() Function, the Table Name containing the AutoNumber Field, and the value you wish to start the AutoNumber with. The Function definition as well as a sample Call is listed below. Should you have any questions whatsoever concerning the code, please feel free to ask.
[CODE=text]
Public Function fForceAutoNumbe r(strTableName As String, lngAutoNumToSta rt As Long)
' Purpose: set the AutoNumber field in strTableName to begin at lngAutoNumToSta rt.
' Arguments: strTableName = name of table to modify.
' lngAutoNumToSta rt = the number you wish to begin from.
' Sample use: Call SetAutoNumber(" tblInvoice", 1000) - Allen Browne
Dim db As DAO.Database ' Current db.
Dim tdf As DAO.TableDef ' TableDef of strTableName.
Dim i As Integer ' Loop counter
Dim fld As DAO.Field ' Field of strTableName.
Dim strAutoNumField As String ' Name of the AutoNumber field.
Dim vMaxID As Variant ' Current Maximum AutoNumber value.
Dim sSQL As String ' Append/Delete query string.
Dim sMsg As String ' MsgBox string.

lngAutoNumToSta rt = lngAutoNumToSta rt - 1 ' Assign to 1 less than desired value.

' Locate the auto-incrementing field for this table.
Set db = CurrentDb()
Set tdf = db.TableDefs(st rTableName)

For i = 0 To tdf.Fields.Coun t - 1
Set fld = tdf.Fields(i)
If fld.Attributes And dbAutoIncrField Then
strAutoNumField = fld.Name
Exit For
End If
Next

If Len(strAutoNumF ield) = 0 Then
sMsg = "No AutoNumber field found in table """ & strTableName & """."
MsgBox sMsg, vbInformation, "Cannot set AutoNumber"
Else
vMaxID = DMax(strAutoNum Field, strTableName) 'MAXIMUM AutoNumber value
If IsNull(vMaxID) Then vMaxID = 0
If vMaxID >= lngAutoNumToSta rt Then
sMsg = "Supply a larger number. """ & strTableName & "." & _
strAutoNumField & """ already contains the value " & vMaxID
MsgBox sMsg, vbInformation, "Too low."
Else
' Insert and delete the record.
sSQL = "INSERT INTO " & strTableName & " ([" & strAutoNumField & "]) SELECT " & _
lngAutoNumToSta rt & " AS lngAutoNumToSta rt;"
db.Execute sSQL, dbFailOnError
sSQL = "DELETE FROM " & strTableName & " WHERE " & strAutoNumField & " = " & _
lngAutoNumToSta rt & ";"
db.Execute sSQL, dbFailOnError
End If
End If
End Function[/CODE]
Sample Call given your criteria of next AutoNumber starting at 500:
[CODE=text]Call fForceAutoNumbe r("Your Table Name Here", 500)[/CODE]

[n8kindt]

idk where your data is coming from (a table, i would assume). and idk if you want the number sequential (i will also assume this to be so) but if i'm right about both those then u can use DMax and add one to this result to assign the next number:

NewAutonumber = DMax("ExistingA utoNumberField" , "YourTable" ) + 1

if you manually make your first "autonumber " value set to 500, the rest will pile on top of that in sequential order. u can place this code in a form or add it to your code in vba or wherever you want (u could possibly even add it to the validation rule of the field in your table--although i wouldn't recommend this). idk what your skill level is, but if you are trying to change existing records you will have to a) use a DAO recordset loop b) create a query using this code to assign the number then copy/paste it into your table or c) do it manually

[ADezii]

idk where your data is coming from (a table, i would assume). and idk if you want the number sequential (i will also assume this to be so) but if i'm right about both those then u can use DMax and add one to this result to assign the next number:

NewAutonumber = DMax("ExistingA utoNumberField" , "YourTable" ) + 1

if you manually make your first "autonumber " value set to 500, the rest will pile on top of that in sequential order. u can place this code in a form or add it to your code in vba or wherever you want (u could possibly even add it to the validation rule of the field in your table--although i wouldn't recommend this). idk what your skill level is, but if you are trying to change existing records you will have to a) use a DAO recordset loop b) create a query using this code to assign the number then copy/paste it into your table or c) do it manually

I don't think that it is that simlpe, since Access will not allow you to dynamically assign a value to an AutoNumber Field. Case in point, the following code will generate a Run Time Error should you attempt it ([EmployeeID] is an AutoNumber Type Field and also the Primary Key).
[CODE=text]
Private Sub Form_Current()
If Me.NewRecord Then
Me![EmployeeID] = DMax("[EmployeeID]", "Employees" ) + 1
End If
End Sub[/CODE]

[n8kindt]

I don't think that it is that simlpe, since Access will not allow you to dynamically assign a value to an AutoNumber Field. Case in point, the following code will generate a Run Time Error should you attempt it ([EmployeeID] is an AutoNumber Type Field and also the Primary Key).
[CODE=text]
Private Sub Form_Current()
If Me.NewRecord Then
Me![EmployeeID] = DMax("[EmployeeID]", "Employees" ) + 1
End If
End Sub[/CODE]

right, when he said "AN autonumber" not "THE autonumber" i guess i assumed he would have the access generated autonumber in addition to the the new autonumber field. another aspect to take into consideration is whether he needs to use this new autonumber as a primary key or not. i also assumed this number would be generated for cosmetic purposes only--not internal data relationships which is definitely not advisable.

[NeoPa]

I don't think that it is that simlpe, since Access will not allow you to dynamically assign a value to an AutoNumber Field. Case in point, the following code will generate a Run Time Error should you attempt it ([EmployeeID] is an AutoNumber Type Field and also the Primary Key).
[CODE=text]
Private Sub Form_Current()
If Me.NewRecord Then
Me![EmployeeID] = DMax("[EmployeeID]", "Employees" ) + 1
End If
End Sub[/CODE]

But as my post #4, you wouldn't keep it an autonumber field.

The techniques used may vary depending on how the records are added, but code finding the max existing + 1 should always work. I can't think of a scenario where this would need to be more complicated (but I'm open to seeing what I've missed).

[mshmyob]

You could follow these directions from Microsoft for different versions of Access.

http://support.microsoft.com/kb/812718

cheers,

[n8kindt]

You could follow these directions from Microsoft for different versions of Access.

http://support.microsoft.com/kb/812718

cheers,

that's good stuff, mshmyob! luciegiles, if you already have a table set up i would go with that method

but yeah, NeoPa is also right. i forgot that you could change the autonumber field to an integer field. however, this can't be changed once there is data in the table.

you can make it work either way.

[NeoPa]

...
i forgot that you could change the autonumber field to an integer field. however, this can't be changed once there is data in the table.
...

Nate, if you check out the post again it does mention using LONG integer. This is the native type of an AutoNumber field and this you certainly CAN switch to after there is data already stored in the field ;)

[n8kindt]

Nate, if you check out the post again it does mention using LONG integer. This is the native type of an AutoNumber field and this you certainly CAN switch to after there is data already stored in the field ;)

you are right. sorry, i mistakenly thought you couldn't change it FROM an autonumber. but i confused it with changing TO an autonumber--which access will not allow you to do.