Check report for data

[Becker]

I have the following code to save a report as a pdf, email it as an attachment and then delete the pdf. It works fine.

Dim iCfg As Object
Dim iMsg As Object
Set iCfg = CreateObject("CDO.Configuration")
Set iMsg = CreateObject("CDO.Message")
With iCfg.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "exc2007.futuraind.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Update
End With

'Stop
With iMsg
.Configuration = iCfg
.Subject = "Late Orders"
stremail = DLookup("email", "reminder_date")
.to = stremail
.TextBody = "Attached is a list of items that have not been ordered yet.  Open the TR Card database for more information."
.AddAttachment "G:\DATA\FUTURA\FINISHG\Titrations_TRcards\TR Cards\TR Master Database\unpurchased orders.pdf"
.from = ""
.sender = ""
.send
End With
Set iMsg = Nothing
Set iCfg = Nothing
Kill "G:\DATA\FUTURA\FINISHG\Titrations_TRcards\TR Cards\TR Master Database\unpurchased orders.pdf"
DoCmd.SetWarnings False
DoCmd.OpenQuery "reminder_date query"
DoCmd.SetWarnings True

I don't want the email to send if the report does not contain data though, so I included this in the code.

DoCmd.OpenReport "unpurchased orders"
If Reports![unpurchased orders].HasData = True Then
'run the email code from above
else exit sub
end if

Checking the report for data however does not work because it says the report is not open. I think because the code runs too fast for the report to finish loading. Any ideas on how the accomplish this?

[Seth Schrock]

You could use the DCount function to check if there are greater than 0 records in the data source for the report. I have done this before and it works great.

If DCount(*, [QueryName]) > 0 Then
    'run your code
Else
    MsgBox("There are no records")
End If

[zmbd]

Seth's method is one way - and may be the best depending on when your code is ran... i.e. from a command button.

Taking a poke at the empty light socket:
It looks like on line 19 that you already have created a report and then in line 26 you delete the report.

In the code that creates the report in Line19, check for the records there... either use Seth's suggestion or use the NoDataEvent http://msdn.microsoft.com/en-us/libr.../ff837041.aspx to prevent the document from being created.

Then In the code you posted above, or before what ever point you call this code ... use the following:
If Len(Dir(strFile ))>0 Then "code to run" End If. where strFile is the full name and path to the document to see if the file exsists and if so, then run the code, if not...

-z

[Becker]

Thanks Seth! That's exactly what I needed.