By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Start Autonumber at a given value

P: 56
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.
Jun 2 '08 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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.
Jun 2 '08 #2

P: 56
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?
Jun 2 '08 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jun 2 '08 #4

ADezii
Expert 5K+
P: 8,597
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 fForceAutoNumber() 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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fForceAutoNumber(strTableName As String, lngAutoNumToStart As Long)
  2. ' Purpose:      set the AutoNumber field in strTableName to begin at lngAutoNumToStart.
  3. ' Arguments:    strTableName = name of table to modify.
  4. '               lngAutoNumToStart = the number you wish to begin from.
  5. ' Sample use:   Call SetAutoNumber("tblInvoice", 1000) - Allen Browne
  6. Dim db As DAO.Database          ' Current db.
  7. Dim tdf As DAO.TableDef         ' TableDef of strTableName.
  8. Dim i As Integer                ' Loop counter
  9. Dim fld As DAO.Field            ' Field of strTableName.
  10. Dim strAutoNumField As String   ' Name of the AutoNumber field.
  11. Dim vMaxID As Variant           ' Current Maximum AutoNumber value.
  12. Dim sSQL As String              ' Append/Delete query string.
  13. Dim sMsg As String              ' MsgBox string.
  14.  
  15. lngAutoNumToStart = lngAutoNumToStart - 1        ' Assign to 1 less than desired value.
  16.  
  17. ' Locate the auto-incrementing field for this table.
  18. Set db = CurrentDb()
  19. Set tdf = db.TableDefs(strTableName)
  20.  
  21. For i = 0 To tdf.Fields.Count - 1
  22.   Set fld = tdf.Fields(i)
  23.     If fld.Attributes And dbAutoIncrField Then
  24.       strAutoNumField = fld.Name
  25.         Exit For
  26.     End If
  27. Next
  28.  
  29. If Len(strAutoNumField) = 0 Then
  30.   sMsg = "No AutoNumber field found in table """ & strTableName & """."
  31.   MsgBox sMsg, vbInformation, "Cannot set AutoNumber"
  32. Else
  33.   vMaxID = DMax(strAutoNumField, strTableName)      'MAXIMUM AutoNumber value
  34.     If IsNull(vMaxID) Then vMaxID = 0
  35.       If vMaxID >= lngAutoNumToStart Then
  36.         sMsg = "Supply a larger number. """ & strTableName & "." & _
  37.         strAutoNumField & """ already contains the value " & vMaxID
  38.           MsgBox sMsg, vbInformation, "Too low."
  39.       Else
  40.         ' Insert and delete the record.
  41.          sSQL = "INSERT INTO " & strTableName & " ([" & strAutoNumField & "]) SELECT " & _
  42.                  lngAutoNumToStart & " AS lngAutoNumToStart;"
  43.            db.Execute sSQL, dbFailOnError
  44.          sSQL = "DELETE FROM " & strTableName & " WHERE " & strAutoNumField & " = " & _
  45.                 lngAutoNumToStart & ";"
  46.            db.Execute sSQL, dbFailOnError
  47.       End If
  48.     End If
  49. End Function
Sample Call given your criteria of next AutoNumber starting at 500:
Expand|Select|Wrap|Line Numbers
  1. Call fForceAutoNumber("Your Table Name Here", 500)
Jun 16 '08 #5

n8kindt
100+
P: 221
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("ExistingAutoNumberField", "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
Jun 17 '08 #6

ADezii
Expert 5K+
P: 8,597
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("ExistingAutoNumberField", "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).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If Me.NewRecord Then
  3.     Me![EmployeeID] = DMax("[EmployeeID]", "Employees") + 1
  4.   End If
  5. End Sub
Jun 17 '08 #7

n8kindt
100+
P: 221
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).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If Me.NewRecord Then
  3.     Me![EmployeeID] = DMax("[EmployeeID]", "Employees") + 1
  4.   End If
  5. End Sub
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.
Jun 17 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
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).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If Me.NewRecord Then
  3.     Me![EmployeeID] = DMax("[EmployeeID]", "Employees") + 1
  4.   End If
  5. End Sub
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).
Jun 18 '08 #9

mshmyob
Expert 100+
P: 903
You could follow these directions from Microsoft for different versions of Access.

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

cheers,
Jun 18 '08 #10

n8kindt
100+
P: 221
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.
Jun 18 '08 #11

NeoPa
Expert Mod 15k+
P: 31,186
...
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 ;)
Jun 19 '08 #12

n8kindt
100+
P: 221
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.
Jun 23 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.