Code to find record number in recordset.

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.
Fred FisherPhotographerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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

0
Fred FisherPhotographerAuthor 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.
0
Fabrice LambertFabrice LambertCommented:
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

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
You can obtain the count of records from the RecordsetClone:

TotalCount = Me.RecordsetClone.RecordCount

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
Fred FisherPhotographerAuthor 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.
0
Fred FisherPhotographerAuthor 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.
0
Gustav BrockCIOCommented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fred FisherPhotographerAuthor 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.
0
Fabrice LambertFabrice LambertCommented:
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 ?
0
Gustav BrockCIOCommented:
Hmm … 1500 or 7000 records isn't very much, and can be browsed in a snap, so something else must be going on.
0
Gustav BrockCIOCommented:
I used this in my latest article:

Sequential Rows in Microsoft Access
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.