Report
The simplest way to number records on a report despite of filtering or filtering as it always work is to set the below properties in an unbound text box:
Control Source: =1
Running Sum: Over Group
That’s all. The unbounded text box will automatically give a sequent number for each record.
Form
The easiest way to number records in a form, Just open the VBA code window and paste the below code in an empty area:
Private Function SerialNumber(ByVal sourceForm As Form) As Variant
On Error GoTo Err:
SerialNumber = Null
With sourceForm.RecordsetClone
.Bookmark = sourceForm.Bookmark
SerialNumber = .AbsolutePosition + 1
End With
Err:
If Err.Number <> 3021 Then MsgBox Err.Number & ":" & Err.Description, vbExclamation, "SerialNumber"
End Function
Then add a textbox control in the form and set its Control Source to:
=SerialNumber([Form])
Please note that you will need to refresh the form to update the serial numbering whenever you need to delete a record. You can download our example for more clarification.
For more pro MS Access Programs, please visit our home page here.
To use Microsoft Access Programs.
You’ll need to have Microsoft Office installed including the bundle of MS Access or to download the free Microsoft Access 2016 run-time.