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.
The third is about Ranking 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:
- Add a sequential record number to each row in a form
- Add a sequential row number to each record of a query
- 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
Ranking rows is covered here: Ranking 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
I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.
Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.
Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.
