473,385 Members | 1,944 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,385 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 24810

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...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

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.