DoCmd.SendObject acSendReport: Send unique reports

Posted on 2009-02-19
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
    LVL 119

    Expert Comment

    by:Rey Obrero
    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

    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

    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 119

    Accepted Solution

    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

    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

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now