Print groups in separate reports

Question

Thanks for taking the time to read my question.

I have a report that can be filtered by either date, date and Team, Team, or not at all.

I want to send this report out via email. I'd like to print it to PDF and then email it. I can do this part.

I am wondering if it is possible to print each top (or main) group, in this case Team, as a report. That way each Team report would be it's own file. Maybe I'm looking at this the wrong way. If so, please point me in the right direction.

Thanks,
Brad

Answer 1

You can restrict a report when outputting it to a PDF file by restricting it's RecordSource query with a parameter, but not by filtering it, as the OutputTo method of the DoCmd object does not have an equivalent of the WhereCondition argument of the OpenReport method.

You'll find an example as InvoicePDF.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

You might have to copy the text of the link into your browser's address bar (not the link location).  For some reason it doesn't always seem to work as a hyperlink.

You'll see that the rptInvoices report's query, qryPDFInvoice, has the following WHERE clause:

WHERE Invoices.InvoiceNumber=[Forms]![frmInvoice]![InvoiceNumber]

To make this optional, equivalent to your optional Team parameter, it would be changed to:

WHERE (Invoices.InvoiceNumber=[Forms]![frmInvoice]![InvoiceNumber]
OR [Forms]![frmInvoice]![InvoiceNumber] IS NULL)

To send three versions of the report to PDF files you'd need to call the OutputTo method three times, changing the value of the parameter each time.  You could do this by making the parameter an unbound text box control in a dialogue form and, with a button on the same form, set the value of the text box to the appropriate Team value each time before calling the OutputTo method.  The form could also include parameters for the date, similarly optionalized by testing OR <parameter> IS NULL.  Note that each OR operation should be parenthesized to force then to evaluate independently, so the WHERE clause of the report's query might be something like this:

WHERE (Team = Forms!YourForm!txtTeam
OR Forms!YourForm!txtTeam IS NULL)
AND (DateColumn >= Forms!YourForm!txtStartDate
OR Forms!YourForm!txtStartDate IS NULL)
AND (DateColumn < Forms!YourForm!txtEndDate+1
OR Forms!YourForm!txtEndDate IS NULL);

This would allow you to select a team and/or define a date range.  The code to send the three versions of the report to PDF files would assign each of the three Team values to txtTeam, calling the OutputTo method after doing so for each, and assigning a different file name to the PDF file each time.  This could be generated from the Team value and the current date/time for instance.

---

Ken Sheridan, Stafford, England

Answer 2

Brad,

 

    Apparently, you need to generate group-wise parts of access report. Using generic code, this can be done by grabbing the name of grouping source field in report's open event and building an array of group values using group header's format event during forced preliminary complete cycle of formatting.

 

    The information gathered above can be used to generate report parts group-wise, which can be saved / sent as required.

 

    The approach outlined above is demonstrated in my sample db named  Report_SendPartsByGrpLevel. It is in Access 2000 file format and is available at Rogers Access Library. It provides output in html format. You could try to adapt it suitably for pdf format. Link:

http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45
 
    The sample db covers three different grouping styles for a book list (i.e. author, publisher and topic). For a given report selected via list box, the user can select one or more eMail destinations by clicking the check box against various addresses displayed in a subform. On clicking the relevant command button, report parts (group-wise) get sent to all selected addresses and are also saved in an earmarked folder in HTML format.

 

    Brief description is given below. It must be ensured that items (a) & (b) under the para headed by "Important:" are properly complied with.
 
Best wishes,
A.D.Tejpal
------------

 

Report_SendPartsByGrpLevel  (Sample Db)

Brief Description

================================

    This sample db demonstrates outputting of selected access report group-wise in HTML format. For each different value of the group header, portion of report pertaining to that group value gets saved as a separate html file, in folder named ReportParts, located in the parent folder housing the sample db. If no such folder exists, it gets created programmatically.

 

    User selection of report is carried out via list box at left. If desired, copies of report parts group-wise can also be sent as attachments to eMail. User can select destination eMail addresses in subform at right, by selecting the check box named SendMark. If more than one address is selected, eMails with attachments get sent to all the selected addresses.

 

    Three different reports are included, grouping books in following alternative styles:

    (a)  Grouped by author

    (b)  Grouped by publisher

    (c)  Grouped by topic

 

    Code in report's open event as well as group header's format event is of generic nature. It uses the preliminary formatting cycle to build an array of group values, which are later used to filter the report whole sending / saving the group-wise parts, via subroutine named  SendReportPartsByGrpLevel() in general module. This subroutine takes two arguments (report name and eMail address (optional)). If second argument is not supplied, no eMail gets sent (though report parts group-wise are still saved in ReportParts folder, in HTML format).

 

    Name of each saved file as well as report part caption reflect the group value as per which the report has been filtered. Spaces in group value, if any, are replaced by underscores, before being concatenated into file name.

 

    Important:

    (a) Make sure that a control bound to the grouping field and bearing the same name is present in the group header section.
    (b) A calculated control having [Pages] as part of its expression must be present in the report. This is required for ensuring complete preliminary round of formatting, which is used to build up an array of group values.

 

    Version: Access 2000 File Format

 

    References: DAO 3.6,  Microsoft Scripting Runtime

======================================

 

----- Original Message -----

From: mbrad

Newsgroups: Msdn.en-US.accessdev

Sent: Tuesday, April 24, 2012 03:04

Subject: Print groups in separate reports

Thanks for taking the time to read my question.

I have a report that can be filtered by either date, date and Team, Team, or not at all.

I want to send this report out via email. I'd like to print it to PDF and then email it. I can do this part.

I am wondering if it is possible to print each top (or main) group, in this case Team, as a report. That way each Team report would be it's own file. Maybe I'm looking at this the wrong way. If so, please point me in the right direction.

Thanks,
Brad

---

A.D. Tejpal

Answer 3

Thank you so much to both of you for such full and excellent answers!

I will give them a try.

Have a great day.

Brad