Cancel Print No Data with subreports?

[triplespiral]

I'm trying to ensure a report will not print if there is no data... I've
looked in the help and suggests to put

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no records to report", vbExclamation, "No Records"
Cancel = True
End Sub

for the Event Procedure in NoData

I've done exactly what it said, but its not working - does it not work with
subreports in a main report? I've tried with just the main report having the
code and then all of the subreports as well but it still prints.... argh.

 

[strive4peace]

If you are using the OpenReport parameters to limit your
main report, the subreports won't pick up the criteria

-----------------------------------
'code to save report filter
'-- use for Filtering Subreports
'or before Outputting Report

Sub SetReportFilter(pReportName as string, pFilter as string)

' written by Crystal
' (e-mail address removed)

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments","City='Denver' AND
dt_appt=#9/18/05#"

On Error Goto SetReportFilter_error

'---------- declare variables
Dim rpt As Report

'---------- open design view of report and set the
report object variable
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)

'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName

'---------- Release object variable
Set rpt = Nothing

Exit Sub

SetReportFilter_error:
msgbox err.description,,"ERROR " & err.number & "
SetReportFilter"
'press F8 to step thru code and fix problem
Stop
Resume
'next line will be the one with the error
End Sub
-----------------------------------


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[triplespiral]

thanks for the quick reply - I'm not entirely sure where I am to put this
code. I realize to replace the report name in the code but the filter
denver, etc has thrown me off a bit. (so not an access guru)

 

[strive4peace]

The code goes into a general module

Create a General Module

1. from the databae window, click on the Module tab
2. click on the NEW command button
3. type the code in (or paste)

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax errors, nothing will appear to happen
-- this is good

You call the code before you do OpenReport

SetReportFilter "MySubReportname","myFilterString"
doCmd.OpenReport "MainReportName", acPreview,,"filterString"

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[triplespiral]

I've copied and pasted it into a new module and ran the debug but get an
error on SetReportFilter saying Compile Error: Argument not optional.

Also - I have three subreports in the main report, does that mean I have to
name each as per below?

sorry - completely new at coding - trying my best.

 

[strive4peace]

you need to send 2 parameters to the SetReportFilter
function: the name of the report and the filter string

SetReportFilter "MySubReportname1","myFilterString"
SetReportFilter "MySubReportname2","myFilterString"
SetReportFilter "MySubReportname2","myFilterString"

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com