We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

DoCmd.SendObject acSendReport: Send unique reports

ShawnGray
ShawnGray asked
on
Medium Priority
708 Views
Last Modified: 2013-11-28
Sample attached; sending report by email from form.
Problem: it sends the every report to every person.
Each person in "PaymentHistoryQryList" should get their report only.
Sample.mdb
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
shawngray,
you have to apply filter to your report, as you iterate to your recordset

the northwind sample database shows how this is done in the Orders form.
see the codes behind the Print invoice button click

Author

Commented:
Hey Capricorn1,
The NWind sample is filtering the report based on the current record on the form.
I may be missing something (won't be the first time) but if I'm attempting to send to each client only the data that relates to them, I don't want to go through hundreds of records one at a time.

Shawn

Author

Commented:
Hey Cap,
I've been looking at the NWind.Invoices structure.  
The code behind the print invoices uses a filter but the filter is based on the record that is currently opened in the form.  Applying that to my sample feels like a chicken and egg problem.  I need the Filter but the I can't apply the filter without an active record.

Sure could use a little more help on this.

Thanks,
Shawn
CERTIFIED EXPERT
Top Expert 2016
Commented:
using the recordset,

  rs.MoveFirst    '<<< '** the first record now is the  active record
    Do Until rs.EOF

    'do the filtering here
     

    rs.MoveNext     '<<< the next record will become the active record
    Loop

is it clearer now?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Yes, thanks.
I was able to create my query structure like the example which contains the necessary filter.
Although I had to add a line in the code.  For some reason it was sending only the first report to each person.  

Not as clean as you would have done it but I think it'll get the job done.
If you don't see any problems with this I'll accept your last solution.

Thanks again,
Shawn
 rs.MoveFirst 
 Do Until rs.EOF
 DoCmd.SendObject acSendReport, "History", acFormatHTML, rs!Email, , , "Test sub", "test msg", , True
 
 DoCmd.GoToRecord , , acNext  '<<<Without this it was sending the first report only to each person
 
 rs.MoveNext 
 Loop

Open in new window

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.