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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

can I make a dynamic autonumber?

Access Howto:

can I make a dynamic autonumber?

by  samara  Posted    (Edited  )
As we know, the autonumber field of MS Access doesnÆt change itself if you delete or change the order of your records. So I thought of the following function, hope that would help you all to solve this problemà

Function setRecordID(strObject As String, strField As String)
Dim myWS As DAO.Workspace
Dim mydb As DAO.Database
Dim myRS As DAO.Recordset
On Error GoTo err_setRecordID

Set myWS = DBEngine(0)
Set mydb = CurrentDb
Set myRS = mydb.OpenRecordset(strObject, dbOpenDynaset)

myWS.BeginTrans
' Do bulk changes to Recordset.
With myRS
.MoveFirst
Do While Not .EOF
.Edit
.Fields(strField) = .AbsolutePosition + 1
.Update
.MoveNext
Loop
End With
' If all updates done successfully, commit the transaction.
myWS.CommitTrans
Exit Function

err_setRecordID:
Select Case Err.Number
Case 3061
'Error in the function arguments
MsgBox "Error in your Object name or your Field name"
Case Else
'If any error occur while making the updates
'all of the changes will be rolled back (not saved).
MsgBox "Start the function again, updating problems"
myWS.Rollback
End Select

Exit Function

End Function

Guys, I tried it out and it works so fine.
Notes:
1) strObject could be a local, linked table, query, or an SQL statement.
2) StrField is the name of the field you want to autonumbered
3) If you use Access 97, please remove ôDAO.ö From the beginning of the function
Syntax:
HereÆs some examples of using the functionà
setRecordID( ôTable1ö, ôField1ö)
setRecordID(ôQuery1ö, ôField1ö)
setRecordID(ôSELECT * FROM table1ö, ôField1ö)

Disadvantage:
I encountered one disadvantage that you have to run this function after every time you add or delete records from your table.
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top