Printing multiple reports excluding empty reports

[Sreemike]

Previous threads in this forum have extensively discussed many ways of printing subreports whilst ignoring empty subreports. Interestingly, I came across another thread few years ago describing multiple reports printing using standard module. The VBA script can be, for example:
Public Function OpenMyReports()
DoCmd.OpenReport "Reportname1"
DoCmd.OpenReport "Reportname2"
etc...
End Function.
So we can call on this function using codes from a switchboard item.
However, if the report has no values, only labels within that report with no values are printed.
But is there a way of not printing empty reports while using this code?
Could it be that additional codes may be necessary somewhere else?

 

[Simon_MT]

Look at On No Data Event on the Report

Function NoData_Reports()
'    MsgBox "There was nothing to report", vbOKOnly, "Report Printing"
Dim MyObjectName As String
    MyObjectName = CodeContextObject.Name
    DoCmd.Close acReport, , MyObjectName
End Function

You may not need the MsgBox.

Simon

 

[vbaInet]

You will also need to trap for the error number 2501.

So in the On No Data event:

Private Sub Report_NoData(Cancel As Integer)
[COLOR=Red]    Cancel = True[/COLOR]
End Sub

Then you can either trap the error or supress and ignore it:

Public Function OpenMyReports()
    On Error Resume Next
    
    With DoCmd
        .OpenReport "Reportname1"
        .OpenReport "Reportname2"
        etc...
    End With
    
    Err.Clear
End Function

 

[Sreemike]

Thanks guys. Codes suggested by Simon MT didn't work for me as the empty report still displayed. But just adding cancel=true & error catches did the trick as explained by vbaInet. I am actually printing reports that are specific test results of individual patients. Each patient has his/her unique ID(autonumber in table) and I use parameter query to ask for this ID in order for reports to be printed. So to print multiple results of different tests (all individual reports) I am being prompted to enter the Patient ID number (as the parameter value) prior to printing each report. Can you guys think of any way I only need to enter this ID once and all the reports with data be printed?

 

[vbaInet]

So what you do is:

1. Create a Pop-up form that will contain the textbox you will enter this ID. Also drop a button on there to use to run your reports
2. Let's say you call the textbox txtSearchID and the form is called frmParameter, you will enter the full reference to the textbox under the Criteria row of the appropriate field like this:

[Forms]![frmParameter]![txtSearchID]

3. The button will Open the first report and become Invisible (or stay visible if you wish).

So anytime you want to open the series of reports, open the pop-up form, enter your ID and hit the button.

 

[Sreemike]

Once again many thanks to you vbaInet. Sometimes I may have data in all eight of my reports, as such all eight will be printed. Do you think it is advisable to add DoEvents to be added between each print DoCmd? eg:
Public Function OpenMyReports()
DoCmd.OpenReport "Reportname1"
DoEvents
DoCmd.OpenReport "Reportname2"
DoEvents
etc...
Also, can you tell me how to hide the pop-up form once executed?

 

[vbaInet]

You mean after each OpenReport command? No it's not necessary in this case. The loading of the report will complete before the next one is fired up.

Re the pop-up form, just set its Visible property to No.

Remember to close it after you close your last report.

 

[Sreemike]

With regard to referencing the textbox from a form as you had described above ([Forms]![frmParameter]![txtSearchID]), is it possible to reference a field in a report which is already displaying into the criteria of a query field? For example, in my case, the Patient ID displayed in a generated report. If that is too difficult, how about referencing a value of a field in a resulting query into a criteria of another query. Hope I make sense.

 

[vbaInet]

If all you're asking is you want to re-use that value in the textbox across all forms and close the form immediately, then create a function to return the value of a vaiable, pass the value of the textbox into a public variable, refer to the function in your queries. That way you can close the form immediately.