Sequential Rows in Microsoft Access

This is the second article on row numbers in Microsoft Access.

The first is about Random Rows in Microsoft Access.

What is it for?

Typically, the reason for assigning a sequential number to each record of a recordset is one of these:

• to add a sequential number to records where no other simple ID is present
• to enumerate and count the rows returned from a query or listed in a form
• to view or control the position of specific records
• to add a unique ID or a kind of serial number to records before an export

 

That can, in some cases, be obtained with no code other than a simple expression, while other cases may require a more robust or resistant method where code must be used. For this reason, three methods will be discussed here:

1. Add a sequential record number to each row in a form
2. Add a sequential row number to each record of a query
3. Add a sequential user maintained priority number to each record in a form

Each method has some distinct advantages and (perhaps) disadvantages that must be taken into consideration before deciding which method to use in a given scenario.

1. Record Numbers

These are similar to the Record Number displayed in the Navigation Bar of a form (left-bottom, in the status bar of the form).

Advantages

• Will always be sequential from top to bottom of the form, no matter how records are ordered, filtered, edited, deleted, or inserted
• For a form, the source can be a table; a query is not required

Disadvantages

• Will not, for the individual record, be static ("sticky")
• Belongs to the form, not the recordset
• May update slowly when browsing the form
• For forms only, not queries

Implementation

The function for this is passed the form itself as a parameter, then uses the RecordsetClone of the form to obtain the AbsolutPosition of the record, which always indicates where the record currently is positioned in the form.

' Creates and returns a sequential record number for records displayed
' in a form, even if no primary or unique key is present.
' For a new record, Null is returned until the record is saved.
'
' 2018-08-23. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RecordNumber( _
    ByRef Form As Access.Form, _
    Optional ByVal FirstNumber As Long = 1) _
    As Variant
    ' Error code for "There is no current record."
    Const NoCurrentRecord   As Long = 3021
   
    Dim Records             As DAO.Recordset
   
    Dim Number              As Variant
    Dim Prompt              As String
    Dim Buttons             As VbMsgBoxStyle
    Dim Title               As String
    On Error GoTo Err_RecordNumber
    If Form Is Nothing Then
        ' No form object is passed.
        Number = Null
    ElseIf Form.Dirty = True Then
        ' No record number until the record is saved.
        Number = Null
    Else
        Set Records = Form.RecordsetClone
        Records.Bookmark = Form.Bookmark
        Number = FirstNumber + Records.AbsolutePosition
        Set Records = Nothing
    End If
   
Exit_RecordNumber:
    RecordNumber = Number
    Exit Function
   
Err_RecordNumber:
    Select Case Err.Number
        Case NoCurrentRecord
            ' Form is at new record, thus no Bookmark exists.
            ' Ignore and continue.
        Case Else
            ' Unexpected error.
            Prompt = "Error " & Err.Number & ": " & Err.Description
            Buttons = vbCritical + vbOKOnly
            Title = Form.Name
            MsgBox Prompt, Buttons, Title
    End Select
   
    ' Return Null for any error.
    Number = Null
    Resume Exit_RecordNumber
End Function

The implementation in the form is extremely simple:

Create a TextBox to display the record number, and set the ControlSource of this to:

=RecordNumber([Form])

The returned number will equal the Current Record displayed in the form's record navigator (bottom-left).

Optionally, you may specify a first number other than 1, say 0, by using the second parameter:

=RecordNumber([Form],0)

NB: For localised versions of Access, when entering the expression, type:

=RecordNumber([LocalisedNameOfObjectForm])

for example:

=RecordNumber([Formular])

and press Enter. The expression will update to:

=RecordNumber([Form])

If the form can't add or delete records, you're done, but if it is, you will have to requery the textbox to update the numbers:

Private Sub Form_AfterDelConfirm(Status As Integer)
    Me!RecordNumber.Requery
End Sub

Private Sub Form_AfterInsert()
    Me!RecordNumber.Requery
End Sub

Other usage

You may also use the function from elsewhere to obtain the record number of an open form:

Dim Number As Variant

Number = RecordNumber(Forms(IndexOfSomeFormInFormsCollection))
' or
Number = RecordNumber(Forms("NameOfSomeOpenForm")

2. Row Numbers

These are created in a query, as a separate field of the resulting recordset.

Advantages

• The numbers will not update if records are deleted, and new records will be assigned the next higher number(s) as long as the query is not required
• The numbers will be defined by the ordering of the query
• If a form is bound to the query, the numbers will always stick to the individual record, no matter how the form is ordered or filtered, or (if the query or form is not updated/required) if records are added or deleted
• Generating the numbers takes one table scan only, thus browsing the query, or a form bound to it is very fast
• As the numbers are static ("sticky"), they are well suited for export or for use in append queries

Disadvantages

• If records are added or deleted, the assigned numbers may change after a re-query to maintain sequentiality
• If used in a form, and different filtering or sorting is applied, there is no method to regain sequentiality other than to revert to the original ordering and remove filtering

Implementation

The function to create the row numbers uses a collection to store these. The great advantage of this method is, that it only takes one table scan to calculate and store the numbers. From then on, the numbers are read directly from the collection, which is very fast. Thus, a query or form displaying the row numbers is not degraded when browsed or read multiple times.

In addition to the traditional numbering of the full recordset, the function also offers numbering of groups and numbering of records on the group level. This is quite fancy and a bit hard to explain without studying the code line by line, thus extensive in-line comments that explain every step has been included in the function for those interested.

' Builds consecutive row numbers in a select, append, or create query
' with the option of a initial automatic reset.
' Optionally, a grouping key can be passed to reset the row count
' for every group key.
'
' 2018-08-23. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RowNumber( _
    ByVal Key As String, _
    Optional ByVal GroupKey As String, _
    Optional ByVal Reset As Boolean) _
    As Long
   
    ' Uncommon character string to assemble GroupKey and Key as a compound key.
    Const KeySeparator      As String = "¤§¤"
    ' Expected error codes to accept.
    Const CannotAddKey      As Long = 457
    Const CannotRemoveKey   As Long = 5
 
    Static Keys             As New Collection
    Static GroupKeys        As New Collection
    Dim Count               As Long
    Dim CompoundKey         As String
   
    On Error GoTo Err_RowNumber
   
    If Reset = True Then
        ' Erase the collection of keys and group key counts.
        Set Keys = Nothing
        Set GroupKeys = Nothing
    Else
        ' Create a compound key to uniquely identify GroupKey and its Key.
        ' Note: If GroupKey is not used, only one element will be added.
        CompoundKey = GroupKey & KeySeparator & Key
        Count = Keys(CompoundKey)
       
        If Count = 0 Then
            ' This record has not been enumerated.
            '
            ' Will either fail if the group key is new, leaving Count as zero,
            ' or retrieve the count of already enumerated records with this group key.
            Count = GroupKeys(GroupKey) + 1
            If Count > 0 Then
                ' The group key has been recorded.
                ' Remove it to allow it to be recreated holding the new count.
                GroupKeys.Remove (GroupKey)
            Else
                ' This record is the first having this group key.
                ' Thus, the count is 1.
                Count = 1
            End If
            ' (Re)create the group key item with the value of the count of keys.
            GroupKeys.Add Count, GroupKey
        End If
        ' Add the key and its enumeration.
        ' This will be:
        '   Using no group key: Relative to the full recordset.
        '   Using a group key:  Relative to the group key.
        ' Will fail if the key already has been created.
        Keys.Add Count, CompoundKey
    End If
   
    ' Return the key value as this is the row counter.
    RowNumber = Count
 
Exit_RowNumber:
    Exit Function
   
Err_RowNumber:
    Select Case Err
        Case CannotAddKey
            ' Key is present, thus cannot be added again.
            Resume Next
        Case CannotRemoveKey
            ' GroupKey is not present, thus cannot be removed.
            Resume Next
        Case Else
            ' Some other error. Ignore.
            Resume Exit_RowNumber
    End Select
End Function

The typical usage in a select query is:

SELECT RowNumber(CStr([ID])) AS RowID, *
FROM SomeTable
WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));

The purpose of the WHERE clause is to call the function once and once only for a reset of the numbers.

As the call of the function to create a number contains the ID of the record, it will happen for each record, but the call for a reset does not, thus the query will call it first - and once only.

If you need to include a group key, it is easily done - just specify it as the second parameter:

SELECT RowNumber(CStr([ID]), CStr[GroupID])) AS RowID, *
FROM SomeTable
WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));

If you wish to create an append query, a similar technique can be used:

INSERT INTO TempTable ( [RowID] )
SELECT RowNumber(CStr([ID])) AS RowID, *
FROM SomeTable
WHERE (RowNumber("","",True)=0);

However, it will not always be feasible with an automatic reset. For example: If you wish to run the query several times, every time appending new records, and these records should be assigned one sequential series of numbers, the rest of the row numbers must be done manually.

So, first call the function this way to reset the counters:

RowNumber(vbNullString, , True)

Then run the append query - without a row number reset - as many times as needed:

INSERT INTO TempTable ( [RowID] )
SELECT RowNumber(CStr([ID])) AS RowID, *
FROM SomeTable;

Other usage

Though only intended for usage as shown in a query - opened either on its own or as the source for a form - the function can also be called from code. Say, you have a query or form open, you can easily look up the assigned row number for any ID of the recordset:

Dim Key As String


Key = CStr(IdToLookUp)
AssignedRowNumber = RowNumber(Key)

3. Priority Numbers

The purpose of assigning a priority (or rank) is to define a sort order for a list of records - different from any other possible sort order of the recordset. Typically, this will reflect a decision made by a human (a user), for example for scenarios like these:

• Order a list of order lines in an order - different from product numbers or the like
• Rank persons
• Assign priority to tasks
• Order steps of a checklist
• Order of options for answers to a question in a multiple-choice test

The assigned priority is stored in a separate numeric field of the table. Thus, they are persistent, and primarily intended to be maintained by a user having the records listed in a form.

The task for the function published here is to keep the full series of priorities in sequence when any of these is adjusted. This means, that if a number is changed, other numbers must be increased or decreased to maintain the continuous sequence of numbers.

Here we change the priority of record two.

If the priority is lowered (numeric value increased):

BeFORE CHANGE	CHANGE	AFTER CHANGE
1		1
2	4	4
3		2
4		3
5		5

If the priority is raised (numeric value decreased):

BeFORE CHANGE	CHANGE	AFTER CHANGE
1		1
2		3
3		4
4	2	2
5		5

Advantages

• The numbers will be persistent, no matter how the records are filtered or ordered
• When maintained in a form, a new record will automatically be assigned the next higher number (lowest priority)
• In the form, records can be inserted or deleted without breaking the sequentiality of the numbers
• Can easily, for example by a button-click, be reset to match the current sorting of the form
• Will not degrade browsing in any way

Disadvantages

• If records can be inserted or deleted from sources not maintaining the sequentiality of the numbers, this will be broken. However, sequentiality can be restored by a call to the function AlignPriority

Implementation

A form, a textbox, and a function that takes this textbox as an argument is required.

A numeric and unique key is required, typical an AutoNumber named ID. If the name is not ID, it must be specified as the second parameter of the function.

The function pulls all necessary information from the properties of this textbox, and then rearranges the full sequence of priority numbers to respect the change of value in this textbox by looping the RecordsetClone of the form.

Also here, the function is fully documented in-line, so you can read it line by line for the details:

' Set the priority order of a record relative to the other records of a form.
'
' The table/query bound to the form must have an updatable numeric field for
' storing the priority of the record. Default value of this should be Null.
'
' Requires:
'   A numeric, primary key, typical an AutoNumber field.
'
' 2018-08-31. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub RowPriority( _
    ByRef TextBox As Access.TextBox, _
    Optional ByVal IdControlName As String = "Id")
   
    ' Error codes.
    ' This action is not supported in transactions.
    Const NotSupported      As Long = 3246
    Dim Form                As Access.Form
    Dim Records             As DAO.Recordset
   
    Dim RecordId            As Long
    Dim NewPriority         As Long
    Dim PriorityFix         As Long
    Dim FieldName           As String
    Dim IdFieldName         As String
   
    Dim Prompt              As String
    Dim Buttons             As VbMsgBoxStyle
    Dim Title               As String
   
    On Error GoTo Err_RowPriority
   
    Set Form = TextBox.Parent
   
    If Form.NewRecord Then
        ' Will happen if the last record of the form is deleted.
        Exit Sub
    Else
        ' Save record.
        Form.Dirty = False
    End If
   
    ' Priority control can have any Name.
    FieldName = TextBox.ControlSource
    ' Id (primary key) control can have any name.
    IdFieldName = Form.Controls(IdControlName).ControlSource
   
    ' Prepare form.
    DoCmd.Hourglass True
    Form.Repaint
    Form.Painting = False
   
    ' Current Id and priority.
    RecordId = Form.Controls(IdControlName).Value
    PriorityFix = Nz(TextBox.Value, 0)
    If PriorityFix <= 0 Then
        PriorityFix = 1
        TextBox.Value = PriorityFix
        Form.Dirty = False
    End If
   
    ' Disable a filter.
    ' If a filter is applied, only the filtered records
    ' will be reordered, and duplicates might be created.
    Form.FilterOn = False
   
    ' Rebuild priority list.
    Set Records = Form.RecordsetClone
    Records.MoveFirst
    While Not Records.EOF
        If Records.Fields(IdFieldName).Value <> RecordId Then
            NewPriority = NewPriority + 1
            If NewPriority = PriorityFix Then
                ' Move this record to next lower priority.
                NewPriority = NewPriority + 1
            End If
            If Nz(Records.Fields(FieldName).Value, 0) = NewPriority Then
                ' Priority hasn't changed for this record.
            Else
                ' Assign new priority.
                Records.Edit
                    Records.Fields(FieldName).Value = NewPriority
                Records.Update
            End If
        End If
        Records.MoveNext
    Wend
   
    ' Reorder form and relocate record position.
    ' Will fail if more than one record is pasted in.
    Form.Requery
    Set Records = Form.RecordsetClone
    Records.FindFirst "[" & IdFieldName & "] = " & RecordId & ""
    Form.Bookmark = Records.Bookmark
   
PreExit_RowPriority:
    ' Enable a filter.
    Form.FilterOn = True
    ' Present form.
    Form.Painting = True
    DoCmd.Hourglass False
   
    Set Records = Nothing
    Set Form = Nothing
   
Exit_RowPriority:
    Exit Sub
   
Err_RowPriority:
    Select Case Err.Number
        Case NotSupported
            ' Will happen if more than one record is pasted in.
            Resume PreExit_RowPriority
        Case Else
            ' Unexpected error.
            Prompt = "Error " & Err.Number & ": " & Err.Description
            Buttons = vbCritical + vbOKOnly
            Title = Form.Name
            MsgBox Prompt, Buttons, Title
           
            ' Restore form.
            Form.Painting = True
            DoCmd.Hourglass False
            Resume Exit_RowPriority
    End Select
   
End Sub

It requires very little code in the form to work. 

If the textbox holding the priority field is named Priority, it will only be this:

After updating the Priority textbox:

Private Sub Priority_AfterUpdate()
    RowPriority Me.Priority
End Sub

After deleting or inserting a record, if that is allowed:

Private Sub Form_AfterDelConfirm(Status As Integer)
    RowPriority Me.Priority
End Sub

Private Sub Form_AfterInsert()
    RowPriority Me.Priority
End Sub

That's it.

Other usage

If the table with the priority field somehow can be scrambled by another process that is not aware of this field, or a table initially has no values filled in, a helper function is included, that will align the values to a given sort order.

It takes the recordset as the first parameter, and (re)arranges the priority field of this. If the field is not named Priority, pass its name as the second parameter.

The exact usage is described in the in-line comments in the header:

' Loop through a recordset and align the values of a priority field
' to be valid and sequential.
'
' Default name for the priority field is Priority.
' Another name can be specified in parameter FieldName.
'
' Typical usage:
'   1.  Run code or query that updates, deletes, or appends records to
'       a table holding a priority field.
'
'   2.  Open an updatable and sorted DAO recordset (Records) with the table:
'
'       Dim Records As DAO.Recordset
'       Set Records = CurrentDb("Select * From Table Order By SomeField")
'   3.  Call this function, passing it the recordset:
'
'       AlignPriority Records
'
' 2018-09-04. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub AlignPriority( _
    ByRef Records As DAO.Recordset, _
    Optional FieldName As String)
    Const FirstNumber       As Long = 1
    Const PriorityFieldName As String = "Priority"
   
    Dim Field               As DAO.Field
   
    Dim CurrentPriority     As Long
    Dim NextPriority        As Long
   
    If FieldName = "" Then
        FieldName = PriorityFieldName
    End If
    ' Verify that the field exists.
    For Each Field In Records.Fields
        If Field.Name = FieldName Then
            Exit For
        End If
    Next
    ' If FieldName is not present, exit silently.
    If Field Is Nothing Then Exit Sub
   
    NextPriority = FirstNumber
    ' Set each record's priority to match its current position as
    ' defined by the sorting of the recordset.
    Records.MoveFirst
    While Not Records.EOF
        CurrentPriority = Nz(Field.Value, 0)
        If CurrentPriority = NextPriority Then
            ' No update needed.
        Else
            ' Assign and save adjusted priority.
            Records.Edit
                Field.Value = NextPriority
            Records.Update
        End If
        Records.MoveNext
        NextPriority = NextPriority + 1
    Wend
   
End Sub

Further, if the priority sequence needs a full rearrange, it can be done with, say, a button click - typically when the records of the form have been sorted in some way. For example:

Private Sub ResetPriorityButton_Click()
    SetRowPriority Me.Priority
End Sub

That will call this function which simply reads the property AbsolutPosition of every record:

' Set the priority order of the records to match a form's current record order.
'
' The table/query bound to the form must have an updatable numeric field for
' storing the priority of the records. Default value of this should be Null.
'
' Usage:
'   To be called from, say, a button click on the form.
'   The textbox Me.Priority is bound to the Priority field of the table:
'
'       Private Sub ResetPriorityButton_Click()
'           SetRowPriority Me.Priority
'       End Sub
'
'
' 2018-08-27. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub SetRowPriority(ByRef TextBox As Access.TextBox)
    Const FirstNumber       As Long = 1
   
    Dim Form                As Access.Form
    Dim Records             As DAO.Recordset
   
    Dim FieldName           As String
       
    Set Form = TextBox.Parent
    Set Records = Form.RecordsetClone
   
    ' TextBox can have any Name.
    FieldName = TextBox.ControlSource
   
    ' Pause form painting to speed up rebuilding of the records' priority.
    Form.Painting = False
   
    ' Set each record's priority to match its current position in the form.
    Records.MoveFirst
    While Not Records.EOF
        If Records.Fields(FieldName).Value = FirstNumber + Records.AbsolutePosition Then
            ' No update needed.
        Else
            ' Assign and save adjusted priority.
            Records.Edit
                Records.Fields(FieldName).Value = FirstNumber + Records.AbsolutePosition
            Records.Update
        End If
        Record   s.MoveNext
    Wend
   
    ' Repaint form.
    Form.Painting = True
    Set Records = Nothing
    Set Form = Nothing
End Sub

Demo

The form Products in the attached sample database - a subset of the Northwind 2007 Sample database - shows all three numbering methods side by side, and also the ID of the records:

If you open it, you can try to sort and filter as you like and see, how the number sequences adopt.

Also, a button, Reset, is present to demonstrate a reset of the priority numbers.

Further, a form, ProductsSlow, is included. 

This demonstrates the traditional code-less method to obtain row numbers using an expression with DLookup, one relative to field ID, and one relative to field Product Code. Also, for reference, a field, Record, displaying the record number is present.

The important thing here to notice is, that none of these fields are sortable in this form. In form Products, row number and priority are both sortable fields, as they are retrieved from the source query.

In module Demo, a function, PriorityCleanTest, is included to show how to call the function AlignPriority described above.

Also, two queries are included, showing typical usage of the functions.

Finally, as a practical example, a form is included where a custom Record of Records  control is included:

Conclusion

Three quite different methods for sequential numbering of records and rows have been described and listed, each with its advantages and disadvantages. 

Combined with the first part on random enumeration, they are supposed to cover every real-life need.

Further information 

The sequential numbering is part of a project on the general numbering of records.

Random ordering is covered here: Random Rows in Microsoft Access

  

A sample database in Access 2016 is attached: RowNumbers 1.2.0.zip

All current code can also be found on GitHub: VBA.RowNumbers