469,267 Members | 1,643 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

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 27065
"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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Guy Erez | last post: by
2 posts views Thread by A.J.M. van Rijthoven | last post: by
4 posts views Thread by Macroman | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.