Solved Email Report

[adamsro]

Hello,

I have a report that is emailed out. For the report itself i have a onload event which will display information depending on the workgroup the report is for. This works fine when viewing the report but when i send the email it does not assuming it's because the onload event is not called when it generates the report to email

Question is where should the report onload event be to make this work when the report is emailed out

 

[Gasman]

What is the code in the onload event?

 

[adamsro]

If WorkGroup = "control" Then
controlpm.Visible = True
End If

 

[Gasman]

And how are you emailing the report?

 

[Mark_]

This should be passed to the report, possibly in OpenArgs.
You should have some code that effectively does the following;

'Work out the FILE_NAME we want to save the report as so it can be Emailed.
' Open the report hidden so our filter can be applied
' Use a variable, hardcoded name, or table entry as appropriate to generage the proper report and WHERE clause.
DoCmd.OpenReport REPORT_NAME, acPreview, , WHERE_CLAUSE, acHidden, OpenArgs
                
' then output the report as a PDF, passing it the file name we want to save it as.
DoCmd.OutputTo acOutputReport, REPORT_NAME, acFormatPDF, FILE_NAME

' After the report is generated, close as we get ready to print the next record.
DoCmd.Close acReport, REPORT_NAME

This way you can generate the report, make sure you are limiting it to the proper records set, then output to a known name to be attached to your Email.

 

[Gasman]

Try opening report in preview, send object the report, then close the report.

 

[Mark_]

Try opening report in preview, send object the report, then close the report.

Process I'm testing right now creates about 200 individual reports that get emailed. Tried "Preview" and the thing flashed enough I was getting a headache... Found opening "Hidden" had a lot less of what my ex would call "Blinky lights".

 

[June7]

I presume Workgroup is a field in report RecordSource.

What section is control in? If you don't want to filter out those records, try code in OnFormat event of section control is in. Executes only for PrintPreview or direct to printer, not ReportView. I would use:
Me.controlpm.Visible = Me.WorkGroup = "control"

Otherwise, use expression in textbox ControlSource to return Null: =IIf(WorkGroup = "control", [this field], Null)

 

[Gasman]

Process I'm testing right now creates about 200 individual reports that get emailed. Tried "Preview" and the thing flashed enough I was getting a headache... Found opening "Hidden" had a lot less of what my ex would call "Blinky lights".

I got that tip from ChatGPT TBH, but I remembered about opening report and then emailing from another thread.

 

[adamsro]

And how are you emailing the report?

using a macro

 

[Gasman]

using a macro

View attachment 119375

So try opening the report first as indicated in post #5.

 

[adamsro]

I presume Workgroup is a field in report RecordSource.

What section is control in? If you don't want to filter out those records, try code in OnFormat event of section control is in. Executes only for PrintPreview or direct to printer, not ReportView. I would use:
Me.controlpm.Visible = Me.WorkGroup = "control"

Otherwise, use expression in textbox ControlSource to return Null: =IIf(WorkGroup = "control", [this field], Null)

I do not see the onFormat event anywhere

 

[Gasman]

I do not see the onFormat event anywhere

 

[Pat Hartman]

Try opening report in preview, send object the report, then close the report.

I wouldn't do this. There are more efficient methods. It is a viable solution for only a few records. It will be very slow if you have more than a few reports to create and export.

There are a couple of solutions. Here's one

Change the report's RecordSource to a query that selects records based on the value of a hidden control.
Create a VBA loop that iterates through the recordset.
Copy the workgroup to the hidden control.
Do NOT open the report first. Simply use the OutputTo method to create the PDF with the correct name.
Then email the report if you want to.

 

[adamsro]

I presume Workgroup is a field in report RecordSource.

What section is control in? If you don't want to filter out those records, try code in OnFormat event of section control is in. Executes only for PrintPreview or direct to printer, not ReportView. I would use:
Me.controlpm.Visible = Me.WorkGroup = "control"

Otherwise, use expression in textbox ControlSource to return Null: =IIf(WorkGroup = "control", [this field], Null)

OnFormat event works as expected thanks again to everyone for your help

 

[Pat Hartman]

Tried "Preview" and the thing flashed enough I was getting a headache... Found opening "Hidden" had a lot less of what my ex would call "Blinky lights".

Think about the overhead you incur to render the report, even hidden. Do NOT open the report. It is not necessary if you control the selection criteria for the report. Unlike the OpenReport method which allows you to pass arguments, the OutputTo does not. That means you need to control the selection criteria using a different method. You may as well learn how to do this efficiently. Or not. Your choice.