Recordset .EOF returning true when there are multiple records

[rsmccli]

Access 2002
Hi. I have a command button that will "approve" all records currently being looked at by an "approver". For some reason, even though there are multiple records that exist in the recordsetclone, EOF is returning true. I think this may have something to do with the sort order of the underlying query, but I'm not sure; at any rate, I don't want to change the sort order. I thought you had to check for BOF and EOF, or at least EOF before doing any recordset "Move" type commands. If I stop the code after declaring the recordset and do a recordcount, it says that there are records in the recordset, but no looping occurs because EOF returns True.

I don't get it. I would appreciate any help. Here is the code behind the command button.

Private Sub cmdApproveAll_Click()
Dim db As DAO.database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = Me.Child.Form.RecordsetClone
 
With rs
  If Not .BOF And Not .EOF Then
    .MoveFirst
      Do Until .EOF = True
        If .Fields("New") = True Then
          .Edit
          .Fields("Approved") = True
          .Fields("Approver") = CurrentUser()
          .Fields("AppDenDate") = Now()
          .Fields("Denied") = False
          .Update
          .MoveNext
        Else
          .MoveNext
        End If
      Loop
  Else
  End If
End With
 
rs.Close
Set rs = Nothing
Set db = Nothing
 
Me.Requery
 
End Sub

[Stewart Ross]

Hi. Recordsets are usually very predictable, and do not behave as you appear to be experiencing. I think you will need to do some systematic debugging here. Establish the facts first: does the RecordsetClone actually return records? Add the following lines after the recordsetclone line (shown for clarity):

Set rs = Me.Child.Form.RecordsetClone
rs.movelast
rs.movefirst
msgbox "Recordcount is " & rs.recordcount & " - EOF is " & rs.eof

The messagebox should show the record count and the status of the EOF property. If the messagebox shows a non-zero value yet EOF returns -1 (True) there is a problem with the database. If the recordcount is 0 then EOF will also be True (-1). If this is the case there are no records in the recordsetclone recordset, and you need to investigate why.

-Stewart

[Stewart Ross]

Hi again. I should have remembered that trying to move to a record in an empty recordset will cause an error, so add

on error resume next

at the top of the debug code to avoid interrupting the messagebox display in the event that the recordset really is empty. You can take all additional statements you put in out again after the source of the error is found.

You probably know already, but just to explain why Movelast is immediately followed by Movefirst: if the recordset is based on a table then the Recordcount property is accurate, but if it is based on a query (as all forms should be) the Recordcount property does not return a fully accurate value unless the full recordset has been traversed first - hence the Movelast. This is followed by Movefirst to reset the record pointer to the first record in the recordset, ready for loop processing.

-Stewart

[rsmccli]

Thank you for your help.