By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,776 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,776 IT Pros & Developers. It's quick & easy.

Recordset .EOF returning true when there are multiple records

P: 52
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdApproveAll_Click()
  2. Dim db As DAO.database
  3. Dim rs As DAO.Recordset
  4. Set db = CurrentDb
  5. Set rs = Me.Child.Form.RecordsetClone
  7. With rs
  8.   If Not .BOF And Not .EOF Then
  9.     .MoveFirst
  10.       Do Until .EOF = True
  11.         If .Fields("New") = True Then
  12.           .Edit
  13.           .Fields("Approved") = True
  14.           .Fields("Approver") = CurrentUser()
  15.           .Fields("AppDenDate") = Now()
  16.           .Fields("Denied") = False
  17.           .Update
  18.           .MoveNext
  19.         Else
  20.           .MoveNext
  21.         End If
  22.       Loop
  23.   Else
  24.   End If
  25. End With
  27. rs.Close
  28. Set rs = Nothing
  29. Set db = Nothing
  31. Me.Requery
  33. End Sub
Apr 28 '08 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 2.5K+
P: 2,545
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):
Expand|Select|Wrap|Line Numbers
  1. Set rs = Me.Child.Form.RecordsetClone
  2. rs.movelast
  3. rs.movefirst
  4. 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.

Apr 28 '08 #2

Expert Mod 2.5K+
P: 2,545
Hi again. I should have remembered that trying to move to a record in an empty recordset will cause an error, so add
Expand|Select|Wrap|Line Numbers
  1. 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.

Apr 29 '08 #3

P: 52
Thank you for your help.
Apr 29 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.