Solved Help understanding code from SO, looping to first record with button

[dev_omi]

I want my users to be able to cycle through images by clicking navigation buttons (but not Navigation Buttons lol). I found some code on Stack Overflow that works like a charm. So in a sense, my problem is already solved. But I want to actually learn this stuff rather than just lifting code off the internet, so I was hoping someone might be able to explain it to me.

Here is the SO post: vba - Next Button that cycles back to first on Access Form - Stack Overflow

They provide this function to check whether a form is on the last record:

Private Function LastRecordP() As Boolean
    With Me.RecordsetClone
        If Not .EOF Then
            .MoveLast
            .MoveFirst
            LastRecordP = Me.CurrentRecord = .RecordCount
        End If
    End With
End Function

I just don't get why the .MoveFirst and .MoveLast are there. I understand that these moves are taking place in the Recordset clone, not the actual Recordset. Are these moves necessary to get an accurate RecordCount?

 

[MajP]

It depends on the type of recordset. But often you have to span the recordset to get an accurate count. Not always required, but safe to do so.

The RecordCount property doesn't indicate how many records are contained in a dynaset–, snapshot–, or forward–only–type Recordset object until all records have been accessed. Once the last record has been accessed, the RecordCount property indicates the total number of undeleted records in the Recordset or TableDef object. To force the last record to be accessed, use the MoveLast method on the Recordset object. You can also use an SQL Count function to determine the approximate number of records your query will return.

Recordset.RecordCount property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

 

[ebs17]

MoveLast is necessary for a correct RecordCount. Access is economical and uses lazy loading. This means that in a query and thus in a recordset you only have the first records that you see. In order to load all records and thus be able to count, you need the MoveLast.
But first you have to check whether there are any records in the recordset at all. Otherwise you run into an error. This is done with the test for EOF. The MoveFirst is used to set the record pointer to the first record. This is an option and does not have to be.

 

[dev_omi]

Got it. Thank you both!

 

[MajP]

FYI, you will find that most of the time you can get an accurate count of the Form's recordset or recordsetclone without any code to move forward and back. I think as part of the loading it ends up accessing the records. So if you get lazy and forget to add the code to move last you likely will get away without any problems. It is much safer to include it.

Here is a demo. I have a form bound to "tblVendingMachines"

Public Sub test()
  Dim frm As Access.Form
  Set frm = Forms("frmvending")
  MsgBox frm.RecordsetClone.RecordCount  ' Gives correct count
 
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblVendingMachines", dbOpenDynaset)
  MsgBox rs.RecordCount  'returns count of 1

End Sub

I simply open the form (do not move or edit).

If I reference the form's recordset and call the recordcount it returns the correct number. In the latter I build the recordset dynamically. It returns a count of 1 (since only one record is accessed).

 

[dev_omi]

FYI, you will find that most of the time you can get an accurate count of the Form's recordset or recordsetclone without any code to move forward and back. I think as part of the loading it ends up accessing the records. So if you get lazy and forget to add the code to move last you likely will get away without any problems. It is much safer to include it.

Here is a demo. I have a form bound to "tblVendingMachines"

Public Sub test()
  Dim frm As Access.Form
  Set frm = Forms("frmvending")
  MsgBox frm.RecordsetClone.RecordCount  ' Gives correct count

  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblVendingMachines", dbOpenDynaset)
  MsgBox rs.RecordCount  'returns count of 1

End Sub

I simply open the form (do not move or edit).

If I reference the form's recordset and call the recordcount it returns the correct number. In the latter I build the recordset dynamically. It returns a count of 1 (since only one record is accessed).

Understood. I really appreciate the help (with both this and my previous question, which you also answered)!