I have the below code to loop through multiple filters and export reports.
I now am trying to add code to NOT export reports with no data. Because there are so many locations that these three reports are being filtered by I would prefer for this action to be done without the user having to acknowledge each NoData report. I have applied the following to each report's On No Data event, but received an error (2501) and the actions stopped.Private Sub cbVBA_Click()
Dim OP1 As String
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("select Location from tblProcessingFO")
Do While Not rs.EOF
OP1 = "C:\Use\for\path\name\" & rs(0) & "\"
DoCmd.OpenReport "Report1", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
DoCmd.OpenReport "Report2", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
DoCmd.OpenReport "Report3", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
DoCmd.OutputTo acOutputReport, "Repor1", acFormatPDF, OP1 & rs(0) & "Expiring.pdf"
DoCmd.OutputTo acOutputReport, "Report2", acFormatPDF, OP1 & rs(0) & "Interviewed.pdf"
DoCmd.OutputTo acOutputReport, "Report3", acFormatPDF, OP1 & rs(0) & "Onboard.pdf"
DoCmd.Close acReport, "Report1", acSaveNo
DoCmd.Close acReport, "Report2", acSaveNo
DoCmd.Close acReport, "Report3", acSaveNo
rs.MoveNext
Loop
Set rs = Nothing
End Sub
I then added the following based off of previous thread "Report on No Data Event" but it somehow cancelled out the filters.Private Sub Report_NoData (Cancel As Integer)
Cancel = True
End Sub
I also tried adding the following without success.Case 2501 'Action OpenReport was cancelled.
DoCmd.Hourglass False
Resume Next
and finallyPrivate Sub Report_NoData (Cancel As Integer)
Cancel = True
On Error Resume Next
End Sub
Any ideas? Thank you.Private Sub Report_NoData (Cancel As Integer)
Cancel = True
If Err.Number = 2501
then
Resume Next