Skip export of Report with No Data and Move to Next (1 Viewer)

[Bgwade]

I have the below code to loop through multiple filters and export reports.

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 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 Report_NoData (Cancel As Integer)
Cancel = True
End Sub

I then added the following based off of previous thread "Report on No Data Event" but it somehow cancelled out the filters.

Case 2501 'Action OpenReport was cancelled.
DoCmd.Hourglass False
Resume Next

I also tried adding the following without success.

Private Sub Report_NoData (Cancel As Integer)
Cancel = True
On Error Resume Next
End Sub

and finally

Private Sub Report_NoData (Cancel As Integer)
Cancel = True
If Err.Number = 2501
then
Resume Next

Any ideas? Thank you.

 

[theDBguy]

I then added the following based off of previous thread "Report on No Data Event" but it somehow cancelled out the filters.

This would be what I would suggest, but what do you mean by it cancels out the filters?

 

[Bgwade]

This would be what I would suggest, but what do you mean by it cancels out the filters?

Each report had all locations listed and blank reports were still exported.

 

[MarkK]

You need to handle error 2501 in Private Sub cbVBA_Click(), not in the report's code.

 

[theDBguy]

Each report had all locations listed and blank reports were still exported.

I see. You may have to step through the code to understand at what stage to better handle the situation or use a different approach, such as using DCount() instead.

 

[theDBguy]

I received an error on the first line.

That's expected; however, your current error handler simply uses Resume Next, which means the line to export the empty report will be executed still. You'll need to add more logic to skip that line too.

 

[Bgwade]

You need to handle error 2501 in Private Sub cbVBA_Click(), not in the report's code.

I placed the following

Do While Not rs.EOF
OP1 = "C:\Use\for\path\name\" & rs(0) & "\"
If Err.Number = 2501 then Resume Next
DoCmd.OpenReport "Report1", acViewPreview, , "Location = '" & rs(0) & "'", acHidden....

and still received an error on the first DoCmd.OpenReport line. I then tried the following and the same issue of all locations being printed on all reports and blank reports continuing to be exported occured.

Do While Not rs.EOF
On Error Resume Next
OP1 = "C:\Use\for\path\name\" & rs(0) & "\"
DoCmd.OpenReport "Report1", acViewPreview, , "Location = '" & rs(0) & "'", acHidden

Is there a specific location within the cbVBA_Click() that it should go? I am pretty novice.

 

[LarryE]

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.

I then added the following based off of previous thread "Report on No Data Event" but it somehow cancelled out the filters.

I also tried adding the following without success.

and finally

Any ideas? Thank you.

You might try the following which tests each report for data before exporting:

Dim OP1 As String
Dim rs As Recordset
Dim Active As Report
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
    Set Active = Screen.ActiveReport
        If Active.HasData = 0 Then
            DoCmd.Close acReport, Active.Name
            MsgBox "THIS REPORT HAS NO DATA."
        Else
            DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, OP1 & rs(0) & "Expiring.pdf"
            DoCmd.Close acReport, "Report1", acSaveNo
        End If
    DoCmd.OpenReport "Report2", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
    Set Active = Screen.ActiveReport
        If Active.HasData = 0 Then
            DoCmd.Close acReport, Active.Name
            MsgBox "THIS REPORT HAS NO DATA."
        Else
            DoCmd.OutputTo acOutputReport, "Report2", acFormatPDF, OP1 & rs(0) & "Interviewed.pdf"
            DoCmd.Close acReport, "Report2", acSaveNo
        End If
    DoCmd.OpenReport "Report3", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
    Set Active = Screen.ActiveReport
        If Active.HasData = 0 Then
            DoCmd.Close acReport, Active.Name
            MsgBox "THIS REPORT HAS NO DATA."
        Else
            DoCmd.OutputTo acOutputReport, "Report3", acFormatPDF, OP1 & rs(0) & "Onboard.pdf"
            DoCmd.Close acReport, "Report3", acSaveNo
        End If
Loop
Set rs = Nothing

 

[MarkK]

If each report closes itself on NoData, then you can just trap the error and move on, like....

Private Function GetPDFName(i As Integer)
    GetPDFName = Split("Expiring.pdf Interviewed.pdf Onboard.pdf")(i - 1)
End Function

Private Function GetOutFile(Location As String, i As Integer)
    GetOutFile = "C:\Use\for\path\name\" & Location & "\" & GetPDFName(i)
End Function

Private Sub cbVBA_Click()
    With CurrentDb.OpenRecordset("SELECT Location FROM tblProcessingFO")
        Do While Not .EOF
            DoReporting .Fields(0)
            .MoveNext
        Loop
        .Close
    End With
End Sub

Private Sub DoReporting(Location As String)
On Error GoTo handler
    Dim i As Integer
    Dim rpt As String
    
    For i = 1 To 3
        rpt = "Report" & i
        DoCmd.OpenReport rpt, acViewPreview, , "Location = '" & Location & "'", acHidden
        DoCmd.OutputTo acOutputReport, rpt, acFormatPDF, GetOutFile(Location, i)
skip:
        DoCmd.Close acReport, rpt
    Next
    Exit Sub
    
handler:
    If Err = 2501 Then Resume skip:
End Sub

 

[arnelgp]

you may also try something like this:

Private Sub cbVBA_Click()

Dim OP1 As String
Dim rs As Recordset

'arnelgp
Dim i As Long

Set rs = CurrentDb.OpenRecordset("select Location from tblProcessingFO")

On Error GoTo err_handler

Do While Not rs.EOF
first_report:
    i = i + 1
    
    OP1 = "C:\Use\for\path\name\" & rs(0) & "\"
    
    DoCmd.OpenReport "Report1", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
    DoCmd.OutputTo acOutputReport, "Repor1", acFormatPDF, OP1 & rs(0) & "Expiring.pdf"
    DoCmd.Close acReport, "Report1", acSaveNo
    

second_report:
    i = i + 1
    DoCmd.OpenReport "Report2", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
    DoCmd.OutputTo acOutputReport, "Report2", acFormatPDF, OP1 & rs(0) & "Interviewed.pdf"
    DoCmd.Close acReport, "Report2", acSaveNo
    
third_report:
    i = i + 1
    
    DoCmd.OpenReport "Report3", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
    DoCmd.OutputTo acOutputReport, "Report3", acFormatPDF, OP1 & rs(0) & "Onboard.pdf"
    DoCmd.Close acReport, "Report3", acSaveNo
    
next_record:
    rs.MoveNext
Loop

exit_here:
rs.Close
Set rs = Nothing
Exit Sub

err_handler:
    If Err = 2501 Then
        Err.Clear
        Select Case i Mod 3
            Case 1
                Resume second_report
            Case 2
                Resume third_report
            Case 3
                Resume next_record
        End Select
    Else
        Resume exit_here
    End If
End Sub

 

[Bgwade]

You might try the following which tests each report for data before exporting:

Dim OP1 As String
Dim rs As Recordset
Dim Active As Report
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
    Set Active = Screen.ActiveReport
        If Active.HasData = 0 Then
            DoCmd.Close acReport, Active.Name
            MsgBox "THIS REPORT HAS NO DATA."
        Else
            DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, OP1 & rs(0) & "Expiring.pdf"
            DoCmd.Close acReport, "Report1", acSaveNo
        End If
    DoCmd.OpenReport "Report2", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
    Set Active = Screen.ActiveReport
        If Active.HasData = 0 Then
            DoCmd.Close acReport, Active.Name
            MsgBox "THIS REPORT HAS NO DATA."
        Else
            DoCmd.OutputTo acOutputReport, "Report2", acFormatPDF, OP1 & rs(0) & "Interviewed.pdf"
            DoCmd.Close acReport, "Report2", acSaveNo
        End If
    DoCmd.OpenReport "Report3", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
    Set Active = Screen.ActiveReport
        If Active.HasData = 0 Then
            DoCmd.Close acReport, Active.Name
            MsgBox "THIS REPORT HAS NO DATA."
        Else
            DoCmd.OutputTo acOutputReport, "Report3", acFormatPDF, OP1 & rs(0) & "Onboard.pdf"
            DoCmd.Close acReport, "Report3", acSaveNo
        End If
Loop
Set rs = Nothing

I entered this but received a Loop Without Do Error. Any suggestions? The only thing I changed was I removed the MsgBox so that the user wouldn't have to acknowledge each no data report.

 

[Bgwade]

Private Sub cbVBA_Click()

Dim OP1 As String
Dim rs As Recordset

Dim i As Long

Set rs = CurrentDb.OpenRecordset("select Location from tblProcessingFO")

On Error GoTo err_handler

Do While Not rs.EOF
first_report:
i = i + 1

OP1 = "C:\Use\for\path\name\" & rs(0) & "\"

DoCmd.OpenReport "Report1", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
DoCmd.OutputTo acOutputReport, "Report1", acFormatPDF, OP1 & rs(0) & "Expiring.pdf"
DoCmd.Close acReport, "Report1", acSaveNo


second_report:
i = i + 1
DoCmd.OpenReport "Report2", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
DoCmd.OutputTo acOutputReport, "Report2", acFormatPDF, OP1 & rs(0) & "Interviewed.pdf"
DoCmd.Close acReport, "Report2", acSaveNo

third_report:
i = i + 1

DoCmd.OpenReport "Report3", acViewPreview, , "Location = '" & rs(0) & "'", acHidden
DoCmd.OutputTo acOutputReport, "Report3", acFormatPDF, OP1 & rs(0) & "Onboard.pdf"
DoCmd.Close acReport, "Report3", acSaveNo

next_record:
rs.MoveNext
Loop

exit_here:
rs.Close
Set rs = Nothing
Exit Sub

err_handler:
If Err = 2501 Then
Err.Clear
Select Case i Mod 3
Case 1
Resume second_report
Case 2
Resume third_report
Case 3
Resume next_record
End Select
Else
Resume exit_here
End If
End Sub[/CODE]

I tried this code but it didn't cycle through all the locations.

 

[theDBguy]

I tried this code but it didn't cycle through all the locations.

Hi. Did you also try the code in post #9? If that doesn't work, another possible approach is to remove the error handler and simply check for the expected potential error. For example,

On Error Resume Next
DoCmd.OpenReport...
If err.Number=0 Then
    DoCmd.OutputTo...
    DoCmd.Close...
Else
    err.Clear
End If

Just a thought...

 

[LarryE]

I entered this but received a Loop Without Do Error. Any suggestions? The only thing I changed was I removed the MsgBox so that the user wouldn't have to acknowledge each no data report.

I could not test it but I did forget
rs.MoveNext
before the Loop

I don't know why you need any recordset anyway. Why can't you just open the reports and do whatever then quit the routine? Why do you need to open a recordset and loop through it? Are you keeping report names in a table or something?

 

[gemma-the-husky]

personally I often test for an empty query first. I know it's duplicating the query, and may be not really necessary, but it's straightforward

if dcount("*","select Location from tblProcessingFO")=0 then
    'code here
   'maybe an exit sub, or a goto
end if

Set rs = CurrentDb.OpenRecordset("select Location from tblProcessingFO")