databasedev.co.uk - database solutions and downloads for microsoft access

Record Counter, Record X of Y, using custom form navigation in Microsoft Access:

Customising Microsoft Access forms, adding your own menus and command buttons is part of creating a Microsoft Access database application and designing an Access graphical user interface. Microsoft Access forms can contain the built in Access navigation buttons and record counter, or you may wish to add your own.

If you decide to remove the standard Microsoft Access navigation options from your database forms, which also removes the Microsoft Access record counters, it would be a good idea to include your own custom designs, so that a user of the database can see what record they are currently on, and how many records are contained in the database table.

Microsoft Access standard navigation buttons and the record counter is removed by setting the option to No, in the properties of the form for the Navigation Buttons. If you remove these, you will need to add your own navigation to allow the user to move through the records displayed in the form.

The standard display appears like below:

Microsoft Access Standard Navigation Buttons and Record Counter

If we decide to create our own navigation buttons on the form (we can use the Microsoft Access built in wizards) or add buttons and attach your own code, you will also need to account for the record counter.

To create a record counter you will need to use an unbound text box and apply code using the OnCurrent event of the form. If we look at the following example, you will see that it includes command buttons that will allow us to navigate through the form, and also includes a record counter. To enhance the design, the record counter is included in a text box, but formatting has been applied to the text box (it is formatted as flat, with transparent borders and the text colour has been changed to match the rest of the forms design)

Microsoft Access Custom Navigation Buttons and Record Counter

In the OnCurrent event of the Microsoft Access form, we add the following code, that includes the record count (record X of Y) into a text box on the form (named txtRecordNo).

We use the OnCurrent, as this event will change every time the form moves to a different record (i.e. The Current record):

Private Sub Form_Current()

' Provide a record counter for using with
' custom navigation buttons (when not using
' Access built in navigation)

    Dim rst As DAO.Recordset
    Dim lngCount As Long

    Set rst = Me.RecordsetClone

    With rst
        .MoveFirst
        .MoveLast
        lngCount = .RecordCount
    End With
    
'Show the result of the record count in the text box (txtRecordNo)

    Me.txtRecordNo = "Record " & Me.CurrentRecord & " of " & lngCount
        
End Sub