Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


DoCmd.SendObject acSendReport: Send unique reports

Posted on 2009-02-19
Medium Priority
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.
Question by:ShawnGray
  • 3
  • 2
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 23683356
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 Comment

ID: 23683528
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.



Author Comment

ID: 23700190
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.

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1500 total points
ID: 23700484
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

is it clearer now?

Author Comment

ID: 23707476
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,
 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

Open in new window


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question