Code to find record number in recordset.

Fred Fisher
Fred Fisher used Ask the Experts™
on
I have several forms that I want to replace the small hard to see recordset counter at the bottom of the form with my own code.  What I have so far is:

Private Sub Form_Current()
   Dim rst As DAO.Recordset
     Dim lngCount As Long
     Set rst = Me.RecordsetClone
        With rst
            .MoveLast
            lngCount = .RecordCount
       End With
     Me.txtSongCount = "This is record " & Me.CurrentRecord & " of " & lngCount
End Sub

When the code gets to the line .MoveLast is results in an error Run-time error '3021', No current record.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can use the AbsolutePosition to obtain the record number:

' 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.
'
' Implementation, typical:
'
'   Create a TextBox to display the record number.
'   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, specify another first number than 1, say, 0:
'
'       =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 delete records, insert this code line in the
'   AfterDelConfirm event:
'
'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           Me!RecordNumber.Requery
'       End Sub
'
'   If the form can add records, insert this code line in the
'   AfterInsert event:
'
'       Private Sub Form_AfterInsert()
'           Me!RecordNumber.Requery
'       End Sub
'
' Implementation, stand-alone:
'
'   Dim Number As Variant
'
'   Number = RecordNumber(Forms(IndexOfFormInFormsCollection))
'   ' or
'   Number = RecordNumber(Forms("NameOfSomeOpenForm"))
'
'
' 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

Open in new window

Fred FisherPhotographer

Author

Commented:
Great that works, however I would like it to display that value in the box as "This is record 1 of 254".  That is what I was trying to get to.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
If the moveLast member function fail, it can mean that the recordset is empty.

Check if BOF and EOF arn't true at the same time.
Sample code:
Dim db As DAO.Database
Set db = CurrentDb

Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT …….")
If not (rs.BOF and rs.EOF) Then
    '// Recordset isn't empty, do whatever you want
Else
    '// Recordset is empty, react accordingly.
End If

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can obtain the count of records from the RecordsetClone:

TotalCount = Me.RecordsetClone.RecordCount

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<Great that works, however I would like it to display that value in the box as "This is record 1 of 254".  That is what I was trying to get to.>>

 That really is a bad idea.   The only way to ensure a accurate count is to do a .Movelast.   The issue with that is that your forcing the database engine to finish loading the entire recordset before it can continue.   This can cause considerable delays for the user depending on how big the recordset is.

 If it's under 1,000 recs, you won't notice much, but after that you can start to introduce seconds of delay.

Jim.
Fred FisherPhotographer

Author

Commented:
Almost there.  I have everything working in the main form but I have one issue in the subform.  When I enter the first song the container for the counter reads "This is record of".  When I enter more songs it stays the same unless I click the cursor (give focus) to one of the preceding song that was entered.  Then the counter updates but I have to click in a preceding song when each song is entered to get it to update.
Fred FisherPhotographer

Author

Commented:
Jim, so how does Access do it when the navigation button and record selection properties are set to "Yes" or does this suffer the same performance problem.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Access does a MoveLast. You will see on the form, that the total sometimes is delayed.

Anyway, this works for me:

In the form, insert two (hidden) textboxes: RecordCount and RecordNumber
Assign the two ControlSource these expressions respectively:

=RecordCount([Form])
=RecordNumber([Form])

Open in new window

Create your "Record of Records" textbox, and assign this ControlSource:

=IIf(IsNull([RecordNumber]),[RecordCount],[RecordNumber]) & " of " & [RecordCount]

Open in new window


Insert code in code-behind-module of the form:

Private Sub Form_AfterDelConfirm(Status As Integer)

    Me!RecordCount.Requery
    
End Sub

Private Sub Form_AfterInsert()
        
    Me!RecordCount.Requery

End Sub

Private Sub Form_Current()

    Static NewRecord    As Boolean
    
    If NewRecord <> Me.NewRecord Then
        Me!RecordCount.Requery
        NewRecord = Me.NewRecord
    End If
    
End Sub

Open in new window

Create a module holding these two functions:

' Returns the count of records in form Form.
'
' Implementation, typical:
'
'   Create a TextBox to display the record count.
'   Set the ControlSource of this to:
'
'       =RecordCount([Form])
'
'   NB: For localised versions of Access, when entering the expression, type
'
'       =RecordCount([LocalisedNameOfObjectForm])
'
'   for example:
'
'       =RecordCount([Formular])
'
'   and press Enter. The expression will update to:
'
'       =RecordCount([Form])
'
'   If the form can delete records, insert this code line in the
'   AfterDelConfirm event:
'
'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           Me!RecordCount.Requery
'       End Sub
'
'   If the form can add records, insert this code line in the
'   AfterInsert and OnCurrent events respectively:
'
'       Private Sub Form_AfterInsert()
'           Me!RecordCount.Requery
'       End Sub
'
'       Private Sub Form_Current()'
'           Static NewRecord    As Boolean
'
'           If NewRecord <> Me.NewRecord Then
'               Me!RecordCount.Requery
'               NewRecord = Me.NewRecord
'           End If
'       End Sub
'
' 2018-09-14. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RecordCount( _
    ByRef Form As Access.Form) _
    As Long

    Dim Records As DAO.Recordset
    Dim Count   As Long

    Set Records = Form.RecordsetClone
    If Not Records.EOF Then
        Records.MoveLast
    End If
    Count = Records.RecordCount + Abs(Form.NewRecord)
    Records.Close

    RecordCount = Count

End Function

Open in new window

And (please note minor modification):

' 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.
'
' Implementation, typical:
'
'   Create a TextBox to display the record number.
'   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, specify another first number than 1, say, 0:
'
'       =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 delete records, insert this code line in the
'   AfterDelConfirm event:
'
'       Private Sub Form_AfterDelConfirm(Status As Integer)
'           Me!RecordNumber.Requery
'       End Sub
'
'   If the form can add records, insert this code line in the
'   AfterInsert event:
'
'       Private Sub Form_AfterInsert()
'           Me!RecordNumber.Requery
'       End Sub
'
' Implementation, stand-alone:
'
'   Dim Number As Variant
'
'   Number = RecordNumber(Forms(IndexOfFormInFormsCollection))
'   ' or
'   Number = RecordNumber(Forms("NameOfSomeOpenForm"))
'
'
' 2018-09-14. 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
    ElseIf Form.NewRecord = True Then
        ' No record number on a new record.
        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

Open in new window

The only difference between this setup and the native record navigator counter is, that it doesn't update the numbers before a delete confirm, but after. This can, however, be regarded as an improvement, so I have made no attempt to resolve it.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<Jim, so how does Access do it when the navigation button and record selection properties are set to "Yes" or does this suffer the same performance problem.>>

  When you open a form (or set the record source), Access starts building the cursor for the form.   Depending on how costly that is, Access may decide to start displaying records before it has completed that task.   You'll see this when you open a form and the records are displayed, but the bottom of the form still lacks a record count.   Then at some point later, you will see the record count pop in.  This is the point where the background task was completed.   Again, you will only see this on larger recordsets.   If the recordset is small, you will see the count right away.

 However when you open a form and immediately do a .movelast, you are forcing the process of building the cursor to complete and everything stops until that is done.   It is not what is called an asynchronous operation, which is what Access does (it continues to build the recordset in the background while it does other things), but a synchronous one.   So the delay to the user can be significant.

 The closest you could come to doing the same thing would be to have the timer event firing, reading the recordcount and displaying it.   At some point, that count would be accurate and you could stop firing the timer.

Jim.
Fred FisherPhotographer

Author

Commented:
Jim,

Thanks for the info.  I had a very large music database that I wrote in Access in 1998-2001 which I had moved from an R:Base that I had wrote.  I had close to 7,000 records in the Songs table and I did not notice an degradation in the performance with less computing power than is now available.  In 2002 I abandoned that program for something that I thought would be more user friendly but it was not and I abandoned that in 2010.  I do not have that old Access database ( I sure wish I had ) so I am rebuilding it now.  I am having to relearn Access again.

I did however notice the degradation in my new Songs table with just over 1,500 records in it on a very sophisticated computer that I use for photo and video editing.   I have also noticed the delay in the the forms when the navigation button and record selection properties are set to "Yes" or does this suffer the same performance problem.   It is frustrating that the controls for the form navigation are so small.  My screen resolution is set for 3840x2160 and the windows scaling is set to 175%.

I have decided to just use the default navigation controls for now.  It would sure be nice is Access 2018 fixed some of this but I doubt it.  Thank again for your input.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Instead of moving to the last record, what about Calling DCount or running a SELECT COUNT query with the same criterias as the query used to instanciate the recordset ?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Hmm … 1500 or 7000 records isn't very much, and can be browsed in a snap, so something else must be going on.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I used this in my latest article:

Sequential Rows in Microsoft Access

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial