The totally amateur guide to printing a grouped access report to separate PDF files.

[Stephan]

Our office runs MS Access 2003. I’ve been working on a little database. It’s what you could probably call a “skills” database. A report on the different skill sets is generated from the database and uploaded to the web. Prior to my volunteering to automate all the work that was being done manually people were editing separate look a like word documents, printing to PDF and uploading to the intranet. A lot of it seemed like a waste of time to me. Me and my big mouth.

Up until a short while ago I was stumped as to how I could;

1. Print each group to a separate file, and;
2. Print the files as PDF.
I worked out how to output the reports as RTF (though not separately, but as everyone will no doubt know you lose all your report formatting (lines, graphics, etc) when outputting your report as RTF. It wasn’t good enough.

I hit on the idea of hacking together the code that let me print to RTF and the lebans code that lets you print a report to PDF. This is how I did it;

Ingredients;

Files
dynapdf.dll
StrStorage.dll
And Module modReportToPDF
from http://www.lebans.com/

An access database with a query based report that is grouped on one particular data item. Lets call it DB1.
A form (menu or otherwise) that could hold a combobox and a command button . Lets call it FormA.


Procedure

1. Copy dll files to same folder as the database or system folder as described on http://www.lebans.com/
2. Copy Module modReportToPDF to the module library of database DB1
3. Create a global string variable for use as the individual file names. Lets call it GroupVar

e.g - Global Groupvar as String

4. Create an unbound combobox on FormA. Lets call it cboCombo0
Rowsource Type = Table/Query
Rowsource = SELECT TABLE.iNDEXFIELDNAME, TABLE.GROUPFIELDNAME (IF NOT INDEX FIELD) FROM TABLE ORDER BY [GROUPFIELDNAME];
If Index field is the field your report is grouped on then the bound column would be 1. If it’s a second field then the bound column would be 2.

5. Put a command button on Form A (lets call it cmdReportGen.
6. Open your report in Design View
7. Make a note of the name of the field that displays your group name
8. In the “On Format” event for the section of the report within which your group field is place the following; Groupvar = fieldname
9. Place the following code (appropriately amended) against the “On Click” event of cmdReportGen;

Private Sub cmdReportGen_Click()
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
'Adapted to print multiple PDF files based on report grouping
'and save files named for report groups - Stephan 15 September 2010

Dim intCounter As Integer
Dim cboCode As ComboBox
Dim blRet As Boolean
Dim stDocName As String

stDocName = "ReportName"

Set cboCode = Me![cboCombo0]

'If Your Data Type is a String
For intCounter = 0 To cboCode.ListCount - 1
DoCmd.OpenReport stDocName, acViewPreview, , "[Field name for Report grouping] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
blRet = ConvertReportToPDF(stDocName, vbNullString, _
"D:\Temp\" & GroupVar & ".pdf", False, True, 150, "", "", 0, 0, 0)
'The variable "GroupVar" has beeen declared globally and is initialised at the "OnFormat" event of report generation
'It's then passed to the file name here and cleared just before the end of each loop
GroupVar = ""
DoCmd.Close acReport, stDocName
Next

MsgBox "Your files have been saved to D:\temp with individual category names", vbOKOnly

End Sub


There’s no error control and it’s hacked to bits but it works. I hope somebody else can use it.

Cheers
Stephan