Add Sequence Number to a Table

There are times you want each record to have a unique identifier.  This function will add a unique sequence number to each record in any table.  It assumes you have a field in the target table that will store the sequence numbers.

Public Function AddSequenceToTable(sTableName As String, sFieldName As String, Optional iStartValue As Long)
‘Purpose: Adds a sequence number starting from 1 to the designated table and field
CalledFrom:
‘Calls  : basErrorHandler.ErrorHandler()
‘Assumes: sTable is the target table for the sequence number.  sFieldName is the target fieldname of that table.
‘Written: MM
‘Updated:
‘Attribu: na
‘Returns: True/False indicating success.
‘——————————————————————
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long
 
Set db = CurrentDb()
Set rs = db.OpenRecordset(sTableName)
 
If IsNull(iStartValue) Then
    iStartValue = 0
Else
End If
Do While Not rs.EOF
    iStartValue = iStartValue + 1
   With rs
        .Edit
            .Fields(sFieldName).Value = iStartValue
        .Update
        .MoveNext
    End With
Loop
 On Error GoTo ErrorHandling:
 ExitProc:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
  Exit Function
 ErrorHandling:
  Select Case Err.Number
    Case Else
        Call ErrorHandler(“frm.BasicUtils”, Err.Number, Err.Description)
        GoTo ExitProc
  End Select
 End Function
Advertisements
Aside | This entry was posted in Access Tips and tagged , . Bookmark the permalink.

One Response to Add Sequence Number to a Table

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s