Send Reports by Email (1 Viewer)

J

JPR

Registered User.

Local time

Yesterday, 20:47

Joined

Jan 23, 2009

Messages

192

• Jun 11, 2019

• #1

Hello,

I have a form with a combo that is used as criteria to a queries.
I would like these queries to be used as record source of a standard reports which should then be attached to an email in pdf format.

Users will select the query from the combo and the report generated into pdf. I would like some help to obtain something similar (sorry about the code, but it's just to give an idea)

If Me.mycombo = "Clients" Then
myreport.RowSource = " myquery"

DoCmd.SendObject acReport, "myreports", "PDFFormat(*.pdf)", "", "", "", "Hello", "", True, ""

Thank you for any help.

 

June7

AWF VIP

Local time

Yesterday, 19:47

Joined

Mar 9, 2014

Messages

5,465

• Jun 11, 2019

• #2

Can't set report RecordSource property directly with VBA like that.

Why would the RecordSource need to be changed? If the fields in report don't change, apply filter criteria to report instead. One way to accomplish is to have dynamic parameters in query that reference controls on form. Then code to send report:

Docmd.SendObject acSendReport, "report name", acFormatPDF, , , , "some text", "some text"

Using SendObject to send report as attached PDF is common topic.

 

theDBguy

I’m here to help

Staff member

Local time

Yesterday, 20:47

Joined

Oct 29, 2018

Messages

21,453

• Jun 11, 2019

• #3

Hi. Just curious, how many queries are we talking about?

 

J

JPR

Registered User.

Local time

Yesterday, 20:47

Joined

Jan 23, 2009

Messages

192

• Jun 11, 2019

• #4

Since I use dynamic combos I would say less then 10.

 

theDBguy

I’m here to help

Staff member

Local time

Yesterday, 20:47

Joined

Oct 29, 2018

Messages

21,453

• Jun 11, 2019

• #5

JPR said:

Since I use dynamic combos I would say less then 10.

Click to expand...

It's still more than what I was hoping, but I'll tell you what I was thinking anyway. I thought maybe you could just create a separate report for each query and then just send each one based on the selection. For example:

Code:Copy to clipboard

DoCmd.SendObject acSendReport, Me.ComboName, acFormatPDF, ...

 

June7

AWF VIP

Local time

Yesterday, 19:47

Joined

Mar 9, 2014

Messages

5,465

• Jun 11, 2019

• #6

I am confused. What do you mean by 'dynamic combos' and why does that require 10 queries?

I prefer VBA to build filter criteria then apply to form or report when opening. So another alternative is to first OpenReport, run SendObject, Close report.

 

GinaWhipp

AWF VIP

Local time

Yesterday, 23:47

Joined

Jun 21, 2011

Messages

5,900

• Jun 12, 2019

• #7

Hmm, perhaps you could just modify the below...
https://www.access-diva.com/vba3.html

 

K

KHallmark

Registered User.

Local time

Yesterday, 20:47

Joined

Jul 11, 2019

Messages

12

• Jul 11, 2019

• #8

Go to External Data tab and click Export to e-mail

 

S

sxschech

Registered User.

Local time

Yesterday, 20:47

Joined

Mar 2, 2010

Messages

792

• Jul 12, 2019

• #9

Post #5 reminded me of another way around that I used to do a long time ago. Instead of having to make multiple reports where only difference were criteria. Assuming the report contains the fields provided by the queries, you could create a standard query name and use that as the source for the report. Then when the user selects the query, the code would copy the query to the standard name and then the report will run fine (unless the report has or doesn't have fields provided by the query that are needed for output).

Examples:
Query1
Query2

User selects query2 from combo
code copies query2 to qryStandard

I had also used that method by adding fields needed for different types of reports then all the reports used the standard query and that gave more options for different layouts between the queries and reports by having 2 combo boxes on the form one to choose a query and the other the report.