reports that return no data

[Mike Hughes]

I have a command button on a form. When the button is clicked a macro runs the report and emails it to a pre-determined email address.

I would like to know if there is a way to stop this process if there is no data returned in the report. In other words if the macro is run and there is not data picked up to populate the report I just want the process to stop there and not send out an email with a report with no data.

Is this possible and how could I do it?

Thanks

 

[pbaldy]

I can't think offhand if adding

Cancel = True

to the report's No Data event will stop the export. You can test it. If not, one way would be to test the query under the report with a DCount() and see if it returns records, and only continue if it does.

 

[dkinley]

Here is one way I overcame something like that, although I don't use macros so don't know if it pertains.

Like PBaldy said, use the NoData event of the report. You could do something like this in the report ....

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data for the report. Cancelling whatever ... ", vbOKOnly, _
          "No Cases"
    Cancel = True
End Sub

On the form, here is some code you could use behind the command button (again, no macros on the button).

Private Sub cmdButtonName_Click()
On Error GoTo Err_cmdButtonName_Click
 
    [COLOR=green]'run the stuff you want [/COLOR]
    
Exit_cmdButtonName_Click:
    Exit Sub
Err_cmdButtonName_Click:
    'traps the nodata event from report
    If err.Number = 2501 Then
        Resume Exit_cmdButtonName_Click
    Else
        MsgBox err.Description
        Resume Exit_cmdButtonName_Click
    End If
End Sub

You could use the msgbox whereever, but this will trap the error returned to the form when the report is closed and allow your application to continue without the user being confused by a standard Access message.

-dK

 

[Mike Hughes]

Would you be willing to show me where the lines of code would go. I failed to mention that this command button runs several reports. Each morning I only have to click on one button and get all my reports for the day done. Here is what I have:

Option Compare Database

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim stDocName As String

stDocName = "CHECK RECIPIENTS NOT 3600000 FIPS"
DoCmd.RunMacro stDocName

stDocName = "BORN OUT OF WEDLOCK NO PAT EST BLANK"
DoCmd.RunMacro stDocName

stDocName = "BORN OUT OF WEDLOCK YES PAT EST BLANK"
DoCmd.RunMacro stDocName

stDocName = "BORN OUT OF WEDLOCK YES - PAT EST L"
DoCmd.RunMacro stDocName

stDocName = "SET WARNINGS NO"
DoCmd.RunMacro stDocName
stDocName = "ALL CASES NOT CLOSED Q"
DoCmd.OpenQuery stDocName

stDocName = "SET WARNINGS NO"
DoCmd.RunMacro stDocName
stDocName = "ALL CASES WITH NCP OR PF Q"
DoCmd.OpenQuery stDocName

stDocName = "SET WARNINGS NO"
DoCmd.RunMacro stDocName
stDocName = "ALL CASES NOT CLOSED Without Matching ALL CASES WITH NCP OR PF Q"
DoCmd.OpenQuery stDocName

stDocName = "SET WARNINGS NO"
DoCmd.RunMacro stDocName
stDocName = "CLIENT FOR UNMATCHED CASES Q"
DoCmd.OpenQuery stDocName

stDocName = "SET WARNINGS NO"
DoCmd.RunMacro stDocName
stDocName = "CASES WITH NO MEMBERS Q"
DoCmd.OpenQuery stDocName

stDocName = "CASES WITH NO MEMBERS"
DoCmd.RunMacro stDocName

stDocName = "SORD WITHOUT OBLE"
DoCmd.RunMacro stDocName

stDocName = "CASES WITH A COURT ID ORDER TYPE MISMATCH"
DoCmd.RunMacro stDocName

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

 

[Mike Hughes]

I got it with your help.
In the report event I just put in:

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
End Sub

The report doesn't get created and there is no error message.

Thanks again