INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Howto:

Make an intelligent autonumber field? by samara
Posted: 25 Jul 00

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 3078
            'invalid Object name
            MsgBox "invalid Object 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.

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close