Send email if there is no report! (1 Viewer)

[DaniBoy]

Hello,
Ok, I got the email thing to work, its now sending the report once every 24 hrs!!! yyeeeaaahhhh!!! BUT, jeje!!! I got another task to do:

I have the report to cancel if there in no data and the sendobject never sends an email, I want it to actualy send an email saying that there is no report for that day ect. This is what I did:

Report Event On No Data:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no Reports at this Time", , "AM Yard Waste Tracker", , , Cancel
Cancel = True

End Sub

Email Timer Event:

Private Sub Form_Timer()
If Format(Now(), "hh:mm:ss") = "12:24:00" Then
DoCmd.SendObject acSendReport, "PM Email Tracker Report", "Microsoft Excel 97-2002", "dcallejas@smtp.co.alachua.fl.us", , , "Trackers", , False

Else

DoCmd.SendObject , , , "dcallejas@smtp.co.alachua.fl.us", , , "No Report to Send", "There is no Report for Today", False

End If

End Sub

What happens when there is no report, it sends the email "No Report To Send" but it loops over and over again, and I have to shut down Access to stop it.

Why is it looping?

Did anyone tried this?

DaniBoy

 

[DaniBoy]

Is this understandable? Has anyone tried it on a form?

Thanks
DaniBoy

 

[bdp]

Its looping because you have your OnTimer event to check the time and if its not "12:24:00" then it does the else statement which is send an email saying there is no report. It will keep looping because that is the only other choice you gave it to do.

In an nested IF Statement, check to to see if the time is 12:24:00. If true, create another If statement to check to see if the report has data. If it does, send off the report, otherwise send off the email that says there is no data to report. Do nothing if the first condition is false.

If Format(Now(), "hh:mm:ss") = "12:24:00" then

If (however you are checking to see if there is data) then

DoCmd.SendObject acSendReport, "PM Email Tracker Report", "Microsoft Excel 97-2002", "dcallejas@smtp.co.alachua.fl.us", , , "Trackers", , False

Else 'the condition is false, there is no data to report

DoCmd.SendObject , , , "dcallejas@smtp.co.alachua.fl.us", , , "No Report to Send", "There is no Report for Today", False


ENd iF

End IF

 

[DaniBoy]

Thank you so much!! Will Try it.

DaniBoy

 

[DaniBoy]

This is how I tell it to see if the Report has no Data!!!!

Report Event On No Data:

Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no Reports at this Time", , "AM Yard Waste Tracker", , , Cancel
Cancel = True

End Sub


I dont know how to do the Following:


" In an nested IF Statement, check to to see if the time is 12:24:00. If true, create another If statement to check to see if the report has data. If it does, send off the report, otherwise send off the email that says there is no data to report. Do nothing if the first condition is false.

If Format(Now(), "hh:mm:ss") = "12:24:00" then

If (however you are checking to see if there is data) then

DoCmd.SendObject acSendReport, "PM Email Tracker Report", "Microsoft Excel 97-2002", "dcallejas@smtp.co.alachua.fl.us", , , "Trackers", , False

Else 'the condition is false, there is no data to report

DoCmd.SendObject , , , "dcallejas@smtp.co.alachua.fl.us", , , "No Report to Send", "There is no Report for Today", False

Any Ideas on how to apply the "Report Event No data" on VBA Code for this problem?

 

[bdp]

You can create a global (module level) variable. In the On Open Event of the form, set the variable to True (there is data) and in the No_Data even set the variable to false.

Then open the report if the time is 12:24:00. After the report is opened it will return either True or False. Then in the if statement, check that global value.

The way I would do it would be to create a recordset on the query used by the report and depending on how the query is set up, check the EOF and BOF of the recordset or if the recordcount is greater than 1. Then whatever the recordset returns (records or no records) then you would send the relevant message:

If Format(Now(), "hh:mm:ss") = "12:24:00" then

'open a recordset based on the query used for the report

If NOT (rs.EOF and rs.BOF) then 'or use rs.Recordcount >1 if it always returns an empty record

DoCmd.SendObject acSendReport, "PM Email Tracker Report", "Microsoft Excel 97-2002", "dcallejas@smtp.co.alachua.fl.us", , , "Trackers", , False

Else 'the condition is false, there is no data to report

DoCmd.SendObject , , , "dcallejas@smtp.co.alachua.fl.us", , , "No Report to Send", "There is no Report for Today", False


ENd iF

End IF

 

[DaniBoy]

Thanks

Let me see if I can acomplish that!!!

DaniBoy