423,095 Members | 2,030 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Testing for an Empty Recordset

ADezii
Expert 5K+
P: 8,584
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)
  5.  
  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
  10.  
  11. 'Method #2
  12. Do Until MyRS.EOF 
  13.    'if the Recordset returns no Rows, Loop will not be entered
  14. Loop
  15.  
  16. 'Method #3
  17. If MyRS.Recordcount > 0 Then 
  18.    'you will only be here if Rows are returned
  19. End If
  20.  
  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:
  24.  
  25. MyRS.MoveLast: MyRS.MoveFirst
  26. Debug.Print MyRS.RecordCount
  27.  
  28. ------------------------------------------------------------------------------
  29.  
  30. NOTE: Any explicit move in an Empty Recordset such as MoveNext, 
  31. MoveLast will result in a Runtime Error.
Apr 14 '07 #1
Share this Article
Share on Google+