Currently, I have a report that checks for expired licenses for employees who are in positions requiring a license. I have it set up in a macro which runs a separate query for each department (i know there is a better way to do this, but i don't know how), generates a report of the output, then sends the report to whatever contact i have specified for that department.

My problem is that when the report is blank, i don't want to send anything. I've tried setting the condition for the send object to:

[Reports]![OHA License Query FULL]![Full Name] Is Not Null

As the full name is popluated any time there is a record, this should work. but it doesn't. it works opposite. If i change it to:

[Reports]![OHA License Query FULL]![Full Name] Is Null

It works the way I want it to. This would be fine, except it makes no sense and I insist on understanding something before I implement it.

With the "Is Not Null" as the condition for the sendobject command, it should only send out populated reports, right? and skip the empty ones?

Currently, I have a report that checks for expired licenses for employees who are in positions requiring a license. I have it set up in a macro which runs a separate query for each department (i know there is a better way to do this, but i don't know how), generates a report of the output, then sends the report to whatever contact i have specified for that department.

My problem is that when the report is blank, i don't want to send anything. I've tried setting the condition for the send object to:

[Reports]![OHA License Query FULL]![Full Name] Is Not Null

As the full name is popluated any time there is a record, this should work. but it doesn't. it works opposite. If i change it to:

[Reports]![OHA License Query FULL]![Full Name] Is Null


It works the way I want it to. This would be fine, except it makes no sense and I insist on understanding something before I implement it.

With the "Is Not Null" as the condition for the sendobject command, it should only send out populated reports, right? and skip the empty ones?


Hello,
Try putting this code in the No Data part of the reports Events tab
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There are no data for this report. Canceling report...", vbInformation
Cancel = True
End Sub

Hopefully it'll work - it does for me??


Regards

CarolW

you mean in the report itself right?

you mean in the report itself right?

Yes- in the reports 'Design View'. Click the box in the very top left corner and go into the Events tab.
In between Private Sub and End Sub paste the following code:
MsgBox "There is no data for this report. Canceling report...", vbInformation
Cancel = True

Regards

CarolW

pardon my ignorance, i'm well versed in queries and tables but macros are a new beast to me :) when the criteria is met and it runs the code, will that stop the sendobject too? right now i have it set to just send the report, with just the email popping up for editing with each report. i not only want the report not to open when it is empty, but i also want it to stop the sending of it, which if i understand correctly it doesn't need to actually open the report to do. does that make sense?

sorry if i'm not being clear, i just have never had so much trouble getting a macro to work!!!

EDIT: Nevermind. I tested it and it works! Thanks so much!

pardon my ignorance, i'm well versed in queries and tables but macros are a new beast to me :) when the criteria is met and it runs the code, will that stop the sendobject too? right now i have it set to just send the report, with just the email popping up for editing with each report. i not only want the report not to open when it is empty, but i also want it to stop the sending of it, which if i understand correctly it doesn't need to actually open the report to do. does that make sense?

sorry if i'm not being clear, i just have never had so much trouble getting a macro to work!!!

EDIT: Nevermind. I tested it and it works! Thanks so much!

What I gave you was not a macro but rather code for an event .
Pleased it worked & your welcome...

CarolW

CarolW