473,326 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Check for Query Results with no Records

I have the following code:

Private Sub cmdDatasheet_Click()
DoCmd.OpenQuery "qryCourse_Details"
End Sub

I would like to check to make sure that this above query has zero rows,
then show a message box that says "There are no results in your query".

I can not put the following code in the forms open event but that
doesn't help as the query result is in a datasheet, not a form.

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Your request returned no records."
Cancel = True
End If

Is there any way to check if the results from a DoCmd.OpenQuery has
zero rows or not?
Marcus
******

Nov 13 '05 #1
5 28006
"Marcus" <to*******@yahoo.ca> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...
I have the following code:

Private Sub cmdDatasheet_Click()
DoCmd.OpenQuery "qryCourse_Details"
End Sub

I would like to check to make sure that this above query has zero rows,
then show a message box that says "There are no results in your query".

I can not put the following code in the forms open event but that
doesn't help as the query result is in a datasheet, not a form.

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Your request returned no records."
Cancel = True
End If

Is there any way to check if the results from a DoCmd.OpenQuery has
zero rows or not?
Marcus
******

Why not build a form? It would take so little time and then you can use
your suggested solution. If you feel this is too much work, then why not
simply open the query with no rows? Would the MsgBox really explain the
situation any better than a query with no rows?
If you decide you need to open the query, then you could write a function:

Public Function QueryReturnsRecords(strQueryName As String) As Boolean

Where you open the query with code and check for rst.eof - does this make
sense? If the query is quick then this may be a possibility.

Nov 13 '05 #2
the normal place for this is in a report, hence the NoData event.

Nov 13 '05 #3
Marcus, I think you just need this:

Private Sub cmdDatasheet_Click()
If DCount("*","qryCourse_Details") > 0 then
DoCmd.OpenQuery "qryCourse_Details"
Else
Msgbox "No records ... "
End if
End Sub

Arno R
"Marcus" <to*******@yahoo.ca> schreef in bericht news:11**********************@l41g2000cwc.googlegr oups.com...
I have the following code:

Private Sub cmdDatasheet_Click()
DoCmd.OpenQuery "qryCourse_Details"
End Sub

I would like to check to make sure that this above query has zero rows,
then show a message box that says "There are no results in your query".

I can not put the following code in the forms open event but that
doesn't help as the query result is in a datasheet, not a form.

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Your request returned no records."
Cancel = True
End If

Is there any way to check if the results from a DoCmd.OpenQuery has
zero rows or not?


Marcus
******

Nov 13 '05 #4
Which procedure would be more efficient, or does it not really matter
which one is used?

Private Sub cmdDatasheet_Click()
If Nz(Dcount("CourseId","qryCourse_Details"), 0) = 0 then
MsgBox "No records were found."
Else
DoCmd.OpenQuery "qryCourse_Details"
End If
End Sub

Or

Private Sub cmdDatasheet_Click()
If Nz(DCount("CourseId","qryCourse_Details"*), 0) > 0 then
DoCmd.OpenQuery "qryCourse_Details"
Else
MsgBox "No records were found."
End if
End Sub
Marcus
*********

Nov 13 '05 #5
Marcos,
The difference (if any) between your examples does NOT matter.
You could try to speedup DCount and other domain aggregate functions by Google-searching for Domain Function Replacements
at http://www.trevor.easynet.co.uk/AccFAQ/
You can skip the Nz-part however, not needed here ...

Arno R

"Marcus" <to*******@yahoo.ca> schreef in bericht news:11**********************@z14g2000cwz.googlegr oups.com...
Which procedure would be more efficient, or does it not really matter
which one is used?

Private Sub cmdDatasheet_Click()
If Nz(Dcount("CourseId","qryCourse_Details"), 0) = 0 then
MsgBox "No records were found."
Else
DoCmd.OpenQuery "qryCourse_Details"
End If
End Sub

Or

Private Sub cmdDatasheet_Click()
If Nz(DCount("CourseId","qryCourse_Details"*), 0) > 0 then
DoCmd.OpenQuery "qryCourse_Details"
Else
MsgBox "No records were found."
End if
End Sub
Marcus
*********

Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Guy Erez | last post by:
Hi, I'm running queries with MySql 4.0.17 that return thousands of records. Because I need to present them in GUI, I returieve the results in chunks using LIMIT, for example - get first 100,...
4
by: M Wells | last post by:
Hi All, I have a table that currently contains approx. 8 million records. I'm running a SELECT query against this table that in some circumstances is either very quick (ie results returned in...
2
by: A.J.M. van Rijthoven | last post by:
I want to open a form that is based on a query. When the output of the query results in 0 records, I don't want to open the form but just display a messagebox. Is there a way to check if the result...
4
by: Macroman | last post by:
MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM, 40Gb Hard drive Table 1 has 167,000 records and contains the following fields tblone_custID tblone_easting tblone_northing ...
10
by: sesling | last post by:
I have created a query that will pull information from our database for the operators. This query will pull in on average 50,000 records. The operators need to refine the search results. I have...
1
by: mamun | last post by:
Hi All, I am doing it for the first time in C# (using Visual Studio 2005), First I need to check if data exists in a table for a variable. If so then run the second query and display the...
7
by: sesling | last post by:
Currently I am using 3 text boxes (status1, status2, status3) on a form and passing the values to a query. The operator will enter one of three statuses (Open, Closed, Pending) in one or more text...
1
by: Euge | last post by:
Hi, I really hope someone will be able to help me with this one as I am sure im just missing something simple. I have an unbound form which has 20 yes/no unbound check boxes. The purpose of...
5
by: slickdock | last post by:
I need to break my query into 3 groups: First 60 records (records 1-60) Next 60 records (records 61-121) Next 60 records (records 122-182) Of course I could use top values 60 for the first...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.