Hello to everyone out there on this rainy day in Detroit,

I need help in suppressing a report from printing or previewing in print preview mode if there are zero records in the query that the report is bound to. Right now, the report prints with #Error all over.

Does someone know how I can do that? What event should I act on or disable?

I was thinking on the report open event, check if the query's recordset is null then msgbox the user, then stop the report from going to print preview. But it's not working, well it's displaying the msgbox but I don't know how to disable the report from going to the report preview state.

Thanks for any help.

FJA

What is triggering the opening of the report? Could have a button or menu item that queries to see if there was a record returned then exectutes a the report if there is a record found


if rs.recordcount <> 0 then
  'DO COMMAND THAT EXECUTES THE REPORT
end if

Try putting cancel = true to the ON_NoData Event of the report. You may want to give the user a message first.

ie:

msgbox "There was no information for the XXX report"
cancel = true

Thank you to both of your suggestions.

With regard to the second suggestion of setting cancel= true. I'm unsure as to where you're suggesting I do that.

When I go to the report event of "On No Data", I have the option of selecting one of the below:
1. event procedure
2. autoexec
3. mcrPageReset

The event procedure of On No Data displays the following:

Private Sub Report_NoData(Cancel As Integer)

End Sub

where Cancel is an integer parameter not a true/false data type. When I set cancel = -1, assuming -1 = true, it did not work to cancel the report from opening. It still opens w/ the #error.

FJA

I have it in the NoData event of the report. Sorry for the mix up above- the instructor was just starting class when I scrawled that off..... You have a point about datatypes there- but with "cancel = true" it runs in my app. For grins I tried it with "cancel = vbtrue" and it ran also. The way I set it up is the report launches, and the Report OnOpen launches a dialog to populate the string for date parameter.
If you are using macros maybe someone can jump in and help you with that- I'm not that familiar with them.
HTH,
Loralee

See below:
============================================
Private Sub Report_NoData(Cancel As Integer)
On Error GoTo Error_Handler

    MsgBox "There are no patients who had an NPP sent on " & strDate
    Cancel = True

Exit Sub

Error_Handler:
   MsgBox "The CTU Database has encountered an error." & _
   vbCrLf & "Please contact your database support person and tell them this:" & _
   vbCrLf & "Error Number: " & Err.Number & " Description: " & Err.Description

Exit Sub

End Sub