Report w/ Query as Datasource

[chris]

I'm trying to set-up a message box when my report is open that will
say there is no data for the report if there are no records in the
query. I've been able to do this for a form, but it will not work
with my report. Here is the code I use on the Open Event of a form:

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no Open SCAR's", vbInformation
DoCmd.Close
Exit Sub
Else
DoCmd.RunMacro "max"
End If

What do I need to do for this to work on a report?

Thank you!

Chris

[Allen Browne]

Just cancel the NoData event of the report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"chris" <ch**********@yahoo.comwrote in message
news:11********************@k78g2000cwa.googlegrou ps.com...

I'm trying to set-up a message box when my report is open that will
say there is no data for the report if there are no records in the
query. I've been able to do this for a form, but it will not work
with my report. Here is the code I use on the Open Event of a form:

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no Open SCAR's", vbInformation
DoCmd.Close
Exit Sub
Else
DoCmd.RunMacro "max"
End If

What do I need to do for this to work on a report?

[chris]

Thank you! I actually didn't see that Event. I added the message box
code and close command to the event. The trouble I have now is that I
get an error telling me that I can't close the report because I have
code running under the detail section on the On Format Event. How can
I skip or cancel this code if there is no data?
On Jan 31, 11:23 am, "Allen Browne" <[e-mail]>
wrote:

Just cancel the NoData event of the report.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"chris" <[e-mail] in message

news:11********************@k78g2000cwa.googlegrou ps.com...

I'm trying to set-up a message box when my report is open that will
say there is no data for the report if there are no records in the
query. I've been able to do this for a form, but it will not work
with my report. Here is the code I use on the Open Event of a form:

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There are no Open SCAR's", vbInformation
DoCmd.Close
Exit Sub
Else
DoCmd.RunMacro "max"
End If

What do I need to do for this to work on a report?- Hide quoted text -

- Show quoted text -

[Tim Marshall]

chris wrote:

If Me.RecordsetClone.RecordCount = 0 Then

IMO, much better to use:

if Me.RecordsetClone.eof then

What do I need to do for this to work on a report?

Use the "On No Data Event" on the report properties.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[chris]

RecordsetClone is not working with the report

On Jan 31, 11:25 am, Tim Marshall
<[e-mail]:

chris wrote:

If Me.RecordsetClone.RecordCount = 0 Then

IMO, much better to use:

if Me.RecordsetClone.eof then

What do I need to do for this to work on a report?

Use the "On No Data Event" on the report properties.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

chris wrote:

Thank you! I actually didn't see that Event. I added the message box
code and close command to the event.

Do this instead of the close command:

Private Sub Report_NoData(Cancel As Integer)

MsgBox "The criteria you have selected has yielded no results."

Cancel = True

End Sub

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

chris wrote:

RecordsetClone is not working with the report

I did not recommend using it. I should have made it clearer my
preferred method was to do with forms, sorry.

See my response after your post following Allen's response.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[chris]

Your previous message worked great. Thank you!
On Jan 31, 1:12 pm, Tim Marshall
<[e-mail]:

chris wrote:

RecordsetClone is not working with the report

I did not recommend using it. I should have made it clearer my
preferred method was to do with forms, sorry.

See my response after your post following Allen's response.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me