How to loop around report with no data?

[TomYC]

I have two reports, each triggered by a command button. If either one of them has no data, I have a message appear, provided by the following code in the On No Data Event:

MsgBox "No data matches the specified criteria.", vbInformation
Cancel = True

But I have a third command button, which I want to trigger running both reports, and outputting each one as a Snapshot file, then emailing it. If one of the two reports has no data, I want to just skip it, not have an error message that stops the process.

So what is the syntax for a "If Has Data..." statement, and what is the name of the object or control to which I should attach such syntax? I also want to be able to send a message from Outlook, with both reports attached if they have data, but with only one attachment if only one report has data !!

my current code for running and sending reports is below:

Private Sub cmdSendReport_Click()
On Error GoTo Err_cmdSendReport_Click

' Here is the Unit report; the Referral report follows.


Dim stDocName As String

stDocName = "r_detail_unit_TY"
'DoCmd.OpenReport stDocName, acPreview
'+++++++
Dim stPrintFileName As String
Dim stSubjectLine As String
Dim stMessageBody As String
Dim stRptRecipient As String
Dim stCCRecipient As String
Dim stDate As String
Dim stUnit As String
stDocName = "r_detail_unit_TY"
stUnit = Forms![f_rpt]![cbUnit]
stDate = Format(Forms![f_rpt]![cbFromDate], "mmddyy")

stPrintFileName = stUnit & "_" & stDate
stSubjectLine = "Patient Safety Rounds: " & stUnit & " Report for " & stDate
stMessageBody = "Here is your Patient Safety Rounds Report & Referral Report in MS Snapshot format. " & txtMessageBody
stRptRecipient = Forms![f_rpt]![txtEmail]
stCCRecipient = "" & Forms![f_rpt]![txtCCRecipient]

DoCmd.OutputTo acReport, stDocName, "Snapshot Format", "S:\Walkround\Report\" & stPrintFileName & ".snp", False
'
'
'Here is the referral report.
'

Dim stPrintFileName2 As String
stDocName = "r_safety_comment_unit_page"


stDate = Format(Forms![f_rpt]![cbToDate], "mmddyy")
stUnit = [Forms]![f_rpt]![cbUnit]
stPrintFileName2 = "Patient_Safety_Rounds_Safety_Comments_" & stUnit & "_" & stDate

'DoCmd.OutputTo acReport, stDocName, "Snapshot Format", "S:\Walkround\Report\" & stPrintFileName2 & ".snp", False
'


'+++
'Set Reference to OUTLOOK Library
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = stRptRecipient
.CC = stCCRecipient
.Subject = stSubjectLine
.HTMLBody = stMessageBody
.Attachments.Add "S:\Walkround\Report\" & stPrintFileName & ".snp", olByValue, 1, "XXX_SNP"
' .Attachments.Add "S:\Walkround\Report\" & stPrintFileName2 & ".snp", olByValue, 1, "XXX_SNP"
.Send
End With
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If Err = 287 Then ' user cancelled sending mail
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If
Exit Sub

'++++



On Error GoTo Err_cmdSendReport_Click


Exit_cmdSendReport_Click:
Exit Sub

Err_cmdSendReport_Click:
MsgBox Err.Description
Resume Exit_cmdSendReport_Click

End Sub

 

[GKChesterton]

So I gather the challenge is you can't take advantage of the "OnNoData" event because it's a report event. From within this module, for each report I would produce a recordset that matches the report data. Then I would test for .EOF and execute accordingly.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​