472,808 Members | 1,786 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,808 developers and data experts.

Testing for an Empty Recordset

ADezii
8,834 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)
  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
0 24601

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

Similar topics

5
by: Bruce Duncan | last post by:
I'm calling a SQL stored procedure with the folowing code. It seems to work fine but I want to know if it doesn't return any rows and the oRsCatList.eof is not working. lcDisplayCatList = "Y"...
12
by: Mike MacSween | last post by:
rst.eof = true and rst.bof = true or rst.movelast rst.recordcount = 0 ?
5
by: jonman | last post by:
Hello, I'm a bit of a newbie when it comes to Access (and DB's in general). I've got a form that allows the assembles a SQL string (that I've tested interactively, and proven that it returns...
0
by: Amsterdammer | last post by:
Ik moet diverse Access95 databases overzetten naar een MySQL database met behulp van Visual Basic 6. Dat gaat goed, totdat ik de inhoud van een memo-veld over moet zetten naar een string. De...
8
code green
by: code green | last post by:
I have been working with a script I have inherited that uses the ADODB class. I want to run a query that checks a record is unique across the primary key and date field in a MsSql DB before...
2
by: hackmagic | last post by:
Hi, i have a form that normally has a Recordset containing only one record bound to it. i have replaced the navigation buttons with my own and the 'New Record' button assigns an empty Recordset...
3
by: Twanne | last post by:
Hi, I'm trying to get some values from one table to an other. Now I select all dates from one table and I search with them in another table. When there is a record found for that date it should...
3
by: Flo100 | last post by:
Hi, I have a recordset which I have populated with a query. I have an empty recordset. I would like to take value of a field in every single record, which is a string. Search if the 4rth...
4
by: ipez75 | last post by:
Hello everyone, I have a web application written in asp 6.0, my problem is that I execute a sql server store procedure and I get an empty recordset, while executing the same sp on query anlyzer I...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
0
by: lllomh | last post by:
How does React native implement an English player?
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.