Email parameterised report

[daviedaft]

Hi guys

I'm having a very similar problem to this one on Experts Exchange:

I am attempting to, from an Access button's onClickEvent, e-mail a report as an attachment to a recipient (of the user's choosing...the recipients won't be automated)...that is, I would like to have the onClick event simply open the default mail client, and create a blank message with a given report as the attachment...

Now, here's the monkey wrench: The report in question is based on a parameter query. The query pulls a values from a drop-down list value on the form, passes it into the query, and then the report is run from that query.

I have the syntax to e-mail a non-parameter-query-based report (A) from access, and the syntax to open the parmeter-based query report (B):

A: DoCmd.SendObject acSendReport, "TestRpt", acFormatRTF
B: DoCmd.OpenReport "qry_PayorRpt_Month", acViewPreview, , "Month = '" & Forms!frm_rpts!cmb_Month & "'"

My question is, how do I combine both methods (DoCmd.SendObject and DoCmd.OpenReport), such that I can pass the parameters into the query, run the query, obtain the report, and then e-mail THAT report as an attachment??

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_22910154.html

Turns out that the guy who had that problem is in fact referencing a control on a form as a parameter, so was able to email the report without passing any parameters dynamically.

My problem is that my parameters are dynamic.

Basically I loop through a recordset to get my affected user, then pass their username as a parameter into another parameterised query which is the record source of a report - effectively I set the report's record source dynamically.

My problem is that I want to email that report, but can't pass parameters into a report if I'm using the SendObject method.

I am having a dumb moment as I can't think how else to do this.

Anyone got any ideas or done something similar?

TIA

 

[namliam]

You could substitute the parameter "hardcoded" into your sql before sending the report?
Or stick it on a form then have the query reference the form?

 

[daviedaft]

You could substitute the parameter "hardcoded" into your sql before sending the report?
Or stick it on a form then have the query reference the form?

Thanks namliam, I was just thinking about this during a teleconference (better than being asleep ) and decided to go with sticking the username into a hidden textbox on the form, then refer to that textbox in the query...

Not the most elegant way of doing it, but it works