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.
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.
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 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.
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. -
Public Function fForceAutoNumber(strTableName As String, lngAutoNumToStart As Long)
-
' Purpose: set the AutoNumber field in strTableName to begin at lngAutoNumToStart.
-
' Arguments: strTableName = name of table to modify.
-
' lngAutoNumToStart = 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.
-
-
lngAutoNumToStart = lngAutoNumToStart - 1 ' Assign to 1 less than desired value.
-
-
' Locate the auto-incrementing field for this table.
-
Set db = CurrentDb()
-
Set tdf = db.TableDefs(strTableName)
-
-
For i = 0 To tdf.Fields.Count - 1
-
Set fld = tdf.Fields(i)
-
If fld.Attributes And dbAutoIncrField Then
-
strAutoNumField = fld.Name
-
Exit For
-
End If
-
Next
-
-
If Len(strAutoNumField) = 0 Then
-
sMsg = "No AutoNumber field found in table """ & strTableName & """."
-
MsgBox sMsg, vbInformation, "Cannot set AutoNumber"
-
Else
-
vMaxID = DMax(strAutoNumField, strTableName) 'MAXIMUM AutoNumber value
-
If IsNull(vMaxID) Then vMaxID = 0
-
If vMaxID >= lngAutoNumToStart 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 " & _
-
lngAutoNumToStart & " AS lngAutoNumToStart;"
-
db.Execute sSQL, dbFailOnError
-
sSQL = "DELETE FROM " & strTableName & " WHERE " & strAutoNumField & " = " & _
-
lngAutoNumToStart & ";"
-
db.Execute sSQL, dbFailOnError
-
End If
-
End If
-
End Function
Sample Call given your criteria of next AutoNumber starting at 500: - Call fForceAutoNumber("Your Table Name Here", 500)
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
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). -
Private Sub Form_Current()
-
If Me.NewRecord Then
-
Me![EmployeeID] = DMax("[EmployeeID]", "Employees") + 1
-
End If
-
End Sub
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). -
Private Sub Form_Current()
-
If Me.NewRecord Then
-
Me![EmployeeID] = DMax("[EmployeeID]", "Employees") + 1
-
End If
-
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.
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). -
Private Sub Form_Current()
-
If Me.NewRecord Then
-
Me![EmployeeID] = DMax("[EmployeeID]", "Employees") + 1
-
End If
-
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).
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 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 ;)
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)
|
by: marko |
last post by:
I would like my autonumber field to start at 100000. How can i do that?
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
| |