How to count records and it's numerical value

Question

I am looking for the best way to count records in an underlying table (or query) and allow one to capture that number on the active form. In short, I would like to create an object on my form which tells the users which record out of how many he is currently looking at (e.g. This is record 3 of 6)

I know how to use the record count function but am having difficulty in transfering that number to an object on my form.

Answer 1

On my form I placed a label and called it lblRecords:

On the Form's Current event, Form's CurrentRecord to get the current record number. Although DCount is used below, please read through this article for examples better than DCount to count number of records.

Private Sub Form_Current()
    Me.lblRecords.Caption = "Record " & Me.CurrentRecord & " of " & DCount("ID", "Table1")
End Sub

Using ADODB, a record count can also be obtained.

EDIT

To get recordcount of the datasource bound to the form, one can do this:

Private Sub Form_Current()
    Me.lblRecords.Caption = "Record " & Me.CurrentRecord & " of " & RecordCount()
End Sub


Function RecordCount() As Integer

On Error GoTo ErrorHandler
    Dim RecordsClone As Object
    
    Set RecordsClone = Me.RecordsetClone
    RecordsClone.MoveLast
    RecordCount = RecordsClone.RecordCount
    Exit Function
    
ErrorHandler:
    RecordCount = "NA"
    
End Function

If the form is bound to a query that shows only a small subset of records from a table, Function RecordCount() will show correct information of the number of records.

But what if the data source of the form is changed dynamically using VBA? The above function will still work correctly. Here's an example of how one can change the data source of the form dynamically.

Public Sub ChangeFormQuery()
    Form_Form1.RecordSource = "select * from table1 where [id] between 3 and 4"
    Form_Form1.Requery
    Form_Form1.Refresh
End Sub