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.
Typically, the reason for assigning a sequential number to each record of a recordset is one of these:
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:
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.
These are similar to the Record Number displayed in the Navigation Bar of a form (left-bottom, in the status bar of the form).
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
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")
These are created in a query, as a separate field of the resulting recordset.
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));
If the table has no index, it will be slightly different:
SELECT RowNumber(CStr([ID])) AS RowID, *
FROM SomeTable
WHERE (RowNumber("","",True)=0);
The purpose of the WHERE clause is to call the function once and once onlyfor 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;
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)
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:
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
|
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.
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
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:
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.
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 365 is attached: RowNumbers 1.4.2.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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)