Check for Query Results with no Records

[Marcus]

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
******

[Justin Hoffman]

"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.

[pietlinden]

the normal place for this is in a report, hence the NoData event.

[Arno R]

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
******

[Marcus]

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
*********

[Arno R]

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
*********