473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Start Autonumber at a given value

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
12 7513
NeoPa
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
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
221 100+
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
8,834 Expert 8TB
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
221 100+
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
32,556 Expert Mod 16PB
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
904 Expert 512MB
You could follow these directions from Microsoft for different versions of Access.

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

cheers,
Jun 18 '08 #10
n8kindt
221 100+
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
32,556 Expert Mod 16PB
...
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
221 100+
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

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

Similar topics

3
by: Joeandtel | last post by:
Is there a way to start the Primary Auto_Number at a specific number? Usually it starts at 1, but I would like to have it start at 5,000
33
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and...
35
by: Traci | last post by:
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key...
16
by: John Baker | last post by:
Hi: I know this is a strange question, but I have inherited a system where files are copied and records re auto numbered (as an index field) )frequently, and I am wondering how high the number...
22
by: Dariusz Kuliñski / TaKeDa | last post by:
I guess that was asked milion times, but I don't have good luck finding working answer on google. Most of the answers tell what to do, but not how. My situation is that I want to have ID in...
4
by: yf | last post by:
A KB article "http://support.microsoft.com/default.aspx?scid=kb;en-us;209599" tells that the maximum number of records that a table may hold if the PRIMARY key data type is set to AUTONUMBER is...
1
by: jimfortune | last post by:
Sometimes I use Autonumber fields for ID fields. Furthermore, sometimes I use those same fields in orderdetail type tables. So it's important in that case that once an autonumber key value is...
5
by: Apple | last post by:
May anyone can teach me how to assign a autonumber, I want to create a number that is starting with year(auto change to year 2006) + autonumber (eg. 2005-0001, 2005-0002)
5
by: marko | last post by:
I would like my autonumber field to start at 100000. How can i do that?
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.