Saving Report Pages as separate PDF Files

Introduction.

When printing multiple customer invoices as a single report, you often face the challenge of separating them for physical mailing.

A more efficient solution is to save each invoice as a separate PDF file on disk. This makes it simple to send invoices directly to customers via email. The benefits are immediate: reduced stationery costs, faster delivery straight to the customer’s inbox, and greater convenience for customers, who can view the invoices on their devices or print them if needed.

In short, part of the workload shifts to the customer—saving both time and money on your end.

Prepare for a Sample Run.

Let us try this with one or two Tables from the Northwind.mdb sample database.

1. Import the following Tables from the Northwind.mdb (or Access2007 Northwind) sample database:
   • Order Details
   • Products 

   Note: The examples use tables from the Northwind.mdb database. However, the queries, reports, and code will be executed in Access 2007. The Products table is not used directly in the query or report, but the ProductID combo box in the Order Details table references it to display product descriptions.

2. Open a new Query in SQL View without selecting any Table/Query from the displayed list.

Data Preparation Queries.

3. Copy and paste the following SQL string into the new Query’s SQL editing window and save the Query with the name Invoice_Orders_0:

   SELECT [Order Details].OrderID,
    [Order Details].ProductID,
    [Order Details].Quantity,
    [Order Details].UnitPrice,
    [Order Details].Discount,
    [Quantity]*((1-[Discount])*[UnitPrice]) AS TotalValue
   FROM [Order Details];

4. After saving and closing the above Query, create another Query, using Invoice_Orders_0 as the source, with the following SQL:

   SELECT Invoice_Orders_0.*
   FROM Invoice_Orders_0
   WHERE (((Invoice_Orders_0.OrderID)=10258));

5. Save the new Query with the name Invoice_Orders_1.

Design Sample Report.

6. Design a Report to print Sales invoices using the Invoice_Orders_1 Query as the Record Source.

   Sample Report Design Image is given below:

   

   Right-click to open Large Image in New Tab/Window.

   Save the Report with the name Rpt_Invoice. Sample Report Preview Image:

   

   Right-click to open Large Image in New Tab/Window.

   
   

   The Create_PDF() Function.

   
   
7. Copy and paste the following VBA Code into a Standard Module and save it:

   Public Function Create_PDF(ByVal OrderStart As Integer, ByVal OrderEnd As Integer, ByVal strPath As String)
   '--------------------------------------------------------------------------------
   'Author : a.p.r. pillai
   'Date   : January 2012
   'Rights : All Rights(c) Reserved by www.msaccesstips.com
   '--------------------------------------------------------------------------------
   'Function Parameters:
   ' 1. - OrderID Start Number
   ' 2. - OrderID End Number
   ' 3. - Target Folder Address, sample: C:\My Documents
   '--------------------------------------------------------------------------------
   Dim strsql_1 As String, strsql As String, criteria As String
   Dim db As Database, rst As Recordset, QryDef As QueryDef
   Dim int_Order As Integer, outFile As String, T As Date
   Dim SQLParam As String, i As Integer, msg As String
   
   'Invoice Query Definition, Order Number must be added at the end as criteria
   strsql_1 = "SELECT Invoice_Orders_0.*  FROM Invoice_Orders_0 "
   strsql_1 = strsql_1 & " WHERE (((Invoice_Orders_0.OrderID)="
   
   'Query definition for Order Numbers between OrderStart and OrderEnd numbers
   SQLParam = "SELECT DISTINCT [Order Details].OrderID FROM [Order Details] "
   SQLParam = SQLParam & "WHERE ((([Order Details].OrderID) Between " & OrderStart & " And " & OrderEnd & ")) "
   SQLParam = SQLParam & " ORDER BY [Order Details].OrderID;"
   
   Set db = CurrentDb
   'open the OrderIDs parameter list to process one by one
   Set rst = db.OpenRecordset(SQLParam, dbOpenDynaset)
   'open the Report Query definition to incorporate OrderID criteria
   Set QryDef = db.QueryDefs("Invoice_Orders_1")
   
   i = 0 'take a count of invoices printed
   Do While Not rst.EOF 'cycle through the parameter list
     'get the order number
     int_Order = Nz(rst!OrderID, 0)
     If int_Order > 0 Then 'if any blank record ignore
        i = i + 1
        'create the criteria part for the Invoice Query
        criteria = int_Order & "));"
        'complete the Invoice SQL by adding the criteria.
        strsql = strsql_1 & criteria
        'Redefine the Invoice Query to print the Invoice
        QryDef.sql = strsql
        db.QueryDefs.Refresh
     
        'PDF file's target path and Order Number is the file name.
        outFile = strPath & "\" & int_Order & ".PDF"
   
        'Save the report as pdf file.
        DoCmd.OutputTo acOutputReport, "Rpt_Invoice", "PDFFormat(*.pdf)", outFile, False, "", 0, acExportQualityPrint
     
     '2 seconds delay loop to give enough time for Access to create the file on disk.
     T = Timer
     Do While Timer < T + 2
       DoEvents
     Loop
    End If
     rst.MoveNext
   Loop
   rst.Close
   
   msg = "Order Start Number: " & OrderStart & vbCr & vbCr
   msg = msg & "Order End Number: " & OrderEnd & vbCr & vbCr
   msg = msg & "Invoices Printed: " & i & vbCr & vbCr
   msg = msg & "Target Folder: " & strPath
   
   MsgBox msg, , "Create_PDF()"
   
   Set rst = Nothing
   Set db = Nothing
   Set QryDef = Nothing
   
   End Function

How it Works.

Now, let us take a look at what preparations we have made so far:

The first query (Invoice_Orders_0) selects the required fields from the Order Details table for the Customer Invoice Report. It also calculates the total value of each record after applying the discount. However, this query does not include any criteria for selecting a specific OrderID or range of OrderIDs for invoice printing.

The second query (Invoice_Orders_1) is based on Invoice_Orders_0 and applies criteria to select a specific OrderID. Using a two-step query structure keeps the SQL simpler. Later, we incorporate the SQL into VBA code to dynamically update the criteria with different OrderIDs, so that each invoice can be generated and saved as a separate PDF file.

In addition, we use a third query—defined as a SQL string variable (SQLParam) within the Create_PDF() function. This query is executed through the statement:

Set rst = db.OpenRecordset(SQLParam, dbOpenDynaset) 

When calling the Create_PDF() function, you must provide three parameters: the Order Start Number, the Order End Number, and the target folder path where the PDF files should be saved. The third query retrieves all order numbers within the specified range, and each order is processed individually to generate separate invoice files.

NB:  To make this sample exercise simple, we are using only the transaction file to print the Invoices.  As you can see from the Report specimen shown above, it doesn’t have any Customer Address printed on it.  If this is required, we must consider setting relationships with the Customer Address Table in the Report Query and include the address fields also. The main idea behind this whole exercise is to save the report of individual Invoices in separate PDF files, rather than going into their details or refinement.

Let us keep that point in mind, and we will continue to review what we are doing in the VBA Code lines.  You must call the Function using the following:

Syntax:

Create_PDF  Start_Number,  End_Number, ”PDF Files Target Folder”)

Example-1:

Create_PDF 10248,10265,”C:\My Documents”
Example-2:
x = Create_PDF(10248,10265,”C:\My Documents”)

You may call the function from a Command Button Click Event Procedure, after setting the Parameter values in Text Boxes on the Form

Example-3:
Create_PDF Me![txtSNumber], Me![txtENumber], Me![txtPathName]

With the Start Number and End Number values, the Parameter Query is redefined to extract all the Order Numbers between those two numbers from the Order Details table so that they can be used for extracting Order-wise items for printing individual Invoices.  The SELECT DISTINCT clause suppresses duplicates from the parameter list.

The data source of the Rpt_Invoice Report is Invoice_Orders_1 Query. This is redefined for each Order-Id as criteria for printing the Rpt_Invoice in PDF format.  The PDF files are saved in the location specified as the third parameter, C:\My Documents.

Each line in the VBA Code is commented at each step. Please go through them to understand the code.

Technorati Tags: Utility

• Dynamic Lookup in Query Column
• Creating a Watermark on MS-Access Reports
• iSeries Date in exported Data
• Change the Query Top Values property with VBA
• Creating Animated Command Buttons