Dear Richard,

I have run into something all of a sudden. I can't believe it! My recordsets have been working perfectly until this evening.

I have a form that gives the user a program performance summary. On the current event of the form are several recordsets counting different categories of patients or treatment supporters. I paste one recordset below as example

Private Sub HowManyOccupiedPeers()
    Dim DB As Database
    Dim rs As Recordset
    Dim MySQL
    
    

    Dim W As String
    Dim s As String
    
    On Error Resume Next
    MySQL = " SELECT * FROM PeerEducatorsT WHERE TotalUnderCare  > 0 " 'summary of how many treatment supporters (Peer Educators) have TB patients to follow up
    

    Dim X
    
    Set DB = CurrentDb
    Set rs = DB.OpenRecordset(MySQL, dbOpenDynaset)
    
    On Error Resume Next
    rs.MoveLast
    X = rs.RecordCount
    X = Format(X, "00")
    
    
    PeersWithPatients = X
      
    rs.Close
    DB.Close
    
    Set rs = Nothing
    Set DB = Nothing
End Sub

These codes have been running perfectly until today when it gives me the above error. The VBA editor highlights the code rs.MoveLast.

Richard, how do i get around this without removing this summary form from the database? This form is very important in my database. It provides the users in the field a quick overview of the program performance in second.

Thank you,

MICAH

---

Reply from Richard Rost:

Check to make sure you HAVE records first before moving to the last one. If your recordset has 0 records, this error will occur. Try:

if rs.EOF then
   Peers = 0
else
   rs.MoveLast
   Peers = rs.RecordCount
end if

But of course this begs the bigger question: WHY are you using a recordset for this? You could probably do it easier with a single DCOUNT function.