470,565 Members | 1,786 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Testing for an Empty Recordset

8,800 Expert 8TB
When you create a Recordset, you may want to know immediately whether that Recordset actually contains any Rows. There are Recordsets that don't return any Rows and you may need to take different steps based on this outcome. There are basically 3 Methods for testing for an Empty Recordset (Recordset that returns no Rows). We will be using DAO, but these Methods are equally applicable to ADO.
Expand|Select|Wrap|Line Numbers
  1. 'Common Code Block
  2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  3. Set MyDB = CurrentDB()
  4. Set MyRS = MyDB.OpenRecordset("qryEmployees", dbOpenDynaset)
  6. 'Method #1
  7. If Not MyRS.BOF And Not MyRS.EOF Then
  8.    'you will only be here if Rows are returned
  9. End If
  11. 'Method #2
  12. Do Until MyRS.EOF 
  13.    'if the Recordset returns no Rows, Loop will not be entered
  14. Loop
  16. 'Method #3
  17. If MyRS.Recordcount > 0 Then 
  18.    'you will only be here if Rows are returned
  19. End If
  21. NOTE:  If you are looking for an exact Record Count in addition
  22. to testing for an Empty Recordset, it is a good idea to traverse
  23. the Recordset beforehand as in:
  25. MyRS.MoveLast: MyRS.MoveFirst
  26. Debug.Print MyRS.RecordCount
  28. ------------------------------------------------------------------------------
  30. NOTE: Any explicit move in an Empty Recordset such as MoveNext, 
  31. MoveLast will result in a Runtime Error.
Apr 14 '07 #1
0 23356

Post your reply

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

Similar topics

5 posts views Thread by Bruce Duncan | last post: by
12 posts views Thread by Mike MacSween | last post: by
reply views Thread by Amsterdammer | last post: by
1 post views Thread by livre | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.