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
‘Calls  : basErrorHandler.ErrorHandler()
‘Assumes: sTable is the target table for the sequence number.  sFieldName is the target fieldname of that table.
‘Written: MM
‘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
End If
Do While Not rs.EOF
    iStartValue = iStartValue + 1
   With rs
            .Fields(sFieldName).Value = iStartValue
    End With
 On Error GoTo ErrorHandling:
    Set rs = Nothing
    Set db = Nothing
  Exit Function
  Select Case Err.Number
    Case Else
        Call ErrorHandler(“frm.BasicUtils”, Err.Number, Err.Description)
        GoTo ExitProc
  End Select
 End Function
Aside | This entry was posted in Access Tips and tagged , . Bookmark the permalink.

1 Response to Add Sequence Number to a Table

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s