Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
formatting an auto number
- Thread starter amillia
- Start date
Hi,
The only method I know is to append a record with the number 279, then delete it. The next record added will be 280.
To append the record, you can use this string:
INSERT INTO yourTable( yourAutoNumberField )
SELECT 279 AS Expr1;
You must populate each required field - this is normally the most annoying part!
Cheers
Steve
The only method I know is to append a record with the number 279, then delete it. The next record added will be 280.
To append the record, you can use this string:
INSERT INTO yourTable( yourAutoNumberField )
SELECT 279 AS Expr1;
You must populate each required field - this is normally the most annoying part!
Cheers
Steve
MUCH more important than how to do this, though, is that you shouldn't bother. Autonumbers will NOT be sequential. They should NOT be used to represent values that mean anything to anyone. Autonumbers will be unique in a table. They are used to distinguish one record from another. They should NOT be used to count things or to otherwise represent real-world data.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developers' section of the site for some helpful fundamentals.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developers' section of the site for some helpful fundamentals.
- Thread starter
- #4
LoriAnne,
My point is not that you shouldn't use an autonuber to automatically assign a number to a record. That's exactly what they're for, and they are great for that. My point is that you should be very careful about what your expectations are for the numbers it generates. If you add a redcord and delete it and then add another record, there will be a gap in the numbers. That's part of the deal. Autonumber columns will have gaps in them. What you said in your first post made it sound as if you were attaching some importance to what the number in the filed was. That's where you get into trouble. The number will be unique. You can count on that. But the number will not indicate how many records there are in the table, and it's important to keep that in mind. That's my only point here.
MichaelRed, a user here, has posted some code to generate sequential numbers. I haven't checked it out in detail, but he ceratinly seems to know what he's doing, so I would guess that it's pretty durn good. He maintains that autonumbers are not always unique, and I think that's a totally incorrect statement, but there's no reason his code won't work.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developers' section of the site for some helpful fundamentals.
My point is not that you shouldn't use an autonuber to automatically assign a number to a record. That's exactly what they're for, and they are great for that. My point is that you should be very careful about what your expectations are for the numbers it generates. If you add a redcord and delete it and then add another record, there will be a gap in the numbers. That's part of the deal. Autonumber columns will have gaps in them. What you said in your first post made it sound as if you were attaching some importance to what the number in the filed was. That's where you get into trouble. The number will be unique. You can count on that. But the number will not indicate how many records there are in the table, and it's important to keep that in mind. That's my only point here.
MichaelRed, a user here, has posted some code to generate sequential numbers. I haven't checked it out in detail, but he ceratinly seems to know what he's doing, so I would guess that it's pretty durn good. He maintains that autonumbers are not always unique, and I think that's a totally incorrect statement, but there's no reason his code won't work.
Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995
Take a look at the Developers' section of the site for some helpful fundamentals.
Don't know if you got your answer but I just had a need to number records sequentially beginning with number 3. Here is the code I used:
'Function to renumber records beginning with number 3'
Function RenumberRecords()
Dim rs2002_B_RECORDData As Recordset 'insert your table name to replace 2002_B_RECORD'
Dim RecordNumb As Integer
Dim RecordNumbStr As String
Set rs2002_B_RECORDData = DBEngine.Workspaces(0).Databases(0).OpenRecordset("2002_B_RECORD", dbOpenTable)
RecordNumb = 2
rs2002_B_RECORDData.MoveFirst
Do While Not (rs2002_B_RECORDData.EOF)
RecordNumb = RecordNumb + 1
RecordNumbStr = String(8 - Len(Trim(Format$(RecordNumb))), "0"
& Trim(Format$(RecordNumb))
rs2002_B_RECORDData.Edit
rs2002_B_RECORDData!SEQNUMBER = RecordNumbStr
rs2002_B_RECORDData.Update
rs2002_B_RECORDData.MoveNext
Loop
End Function
'Function to renumber records beginning with number 3'
Function RenumberRecords()
Dim rs2002_B_RECORDData As Recordset 'insert your table name to replace 2002_B_RECORD'
Dim RecordNumb As Integer
Dim RecordNumbStr As String
Set rs2002_B_RECORDData = DBEngine.Workspaces(0).Databases(0).OpenRecordset("2002_B_RECORD", dbOpenTable)
RecordNumb = 2
rs2002_B_RECORDData.MoveFirst
Do While Not (rs2002_B_RECORDData.EOF)
RecordNumb = RecordNumb + 1
RecordNumbStr = String(8 - Len(Trim(Format$(RecordNumb))), "0"
& Trim(Format$(RecordNumb))rs2002_B_RECORDData.Edit
rs2002_B_RECORDData!SEQNUMBER = RecordNumbStr
rs2002_B_RECORDData.Update
rs2002_B_RECORDData.MoveNext
Loop
End Function
- Status
Similar threads
- Locked
- Question
- Locked
- Question