Access 2007: create a report per group

[EORTIZ]

I have a report that is grouped by a field. I have looked for ways in which it would be possible to obtain an individual report for each group to export to PDF. Since if I have all groups in the same report, it is saved in only one PDF file when I exported. So right now, I modify manually the query from the report is based on to select only one group every time and obtain each PDF as a separate file for each group.

I want to know how I could automate to have individual reports in PDF files for each group without having to manually, modify the query to select the group.

I would greatly appreciate the help because so far it has been unsuccessful my search. Thanks!

[puppydogbuddy]

This might work.
1. Go to your report, highlight the GroupID, invoke the property sheet and set the "New Page" property (for each group) to yes.
2. In the query that is the source of the report, create an alias column for the Sequence. Your revised query would look something like this....replace the illustrative names I used with the actual names you use.

SELECT ((SELECT COUNT(*) FROM tblYourTable
WHERE tblYourTable.GroupID < tblYourTable.GroupID) + 1) AS Sequence,
 tblYourTable.GroupID, Name From tblYourTable Order By GroupID;

3. Now that you have a consecutive sequence, you can print your reports in an incremental fashion with your open report statement in your report launcher as shown below:

Dim X as Integer            ' variable used to store the Max value of Sequence
Dim Y as Integer           ' variable used for loop counter

X = DMax("[Sequence]", "YourQuery")

For Y = 1 to X
    'execute the Access Version of the report in preview mode
    DoCmd.OpenReport "XXXXXX", acViewPreview, , "Sequence = " & Y

    'next execute the code that uses the "outputto" statement that can be used to direct the report to a pdf printer as can be done natively with Access 2007 (illustrated below) or by using Lebans ReportToPDF on his web site at:   [URL]http://www.lebans.com[/URL]  for Access versions prior to 2007.

      DoCmd.OutputTo acOutputReport, "yourReportName", acFormatPDF, "c:\yourReportName"  & ".pdf", False

Next Y

[NeoPa]

Design the report to print everything, then have a loop in your code which cycles through each group you want and open (& print) the report with the matching filter.

This will produce each report as a separate PDF result file.