Saving Report Pages as separate PDF Files

Introduction.

You are printing several Customer Invoices as a single Report, running into several pages.  You have problems with this kind of Report in segregating the Invoices and physically mailing them to the customers. 

If all Invoices can be saved on disk in separate PDF Files then it is easy to send them through emails to the customers.  This saves the cost of stationery and also delivers the invoices to the customer's mailbox instantly, without delay.  Besides that, customers can easily view the Invoices on their machines or take printouts at their end, if that becomes necessary.  In short, part of our task is passed on to the customers, saving our time and money.  I don’t think I can stretch it anymore.

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 

   NB: I will be using Tables from the Northwind.mdb database. But, the queries, reports and Codes will be running under Access2007. The Products Table is not used directly in our Query or Report but the ProductID Combobox in the Order Details table references this table for product description.

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 Invoice_Orders_1 Query as Record Source.

   Sample Report Design Image is given below:

   

   Save the Report with the name Rpt_Invoice. Sample Report Preview Image is the next one:

   
   
   

   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. Besides that, it calculates the Total Value after the Discount of each record in the Query. But, in this Query, we have not set any criteria to select any specific OrderID or Range of OrderIDs for printing the Invoices.

We are setting criteria to select a specific OrderID in the second Query (Invoice_Orders_1),  created using the earlier Query (Invoice_Orders_0) as a source. The two-step query simplifies the SQL and we must take the SQL into the VBA Code to redefine the Query, with changing OrderIDs, so that each Order-Id level Invoice is saved in a separate PDF file on Disk. 

We have one more Query (the third Query saved in SQLParam String Variable is used directly in the Set rst = db.OpenRecordset(SQLParam, dbOpenDynaset) statement) that is totally confined to the Create_PDF() Function.  When the Create_PDF() Function is called you must give the Order Start Number, Order End Number, and Target Folder Address, where the PDF Files are to be saved as function parameters. The third Query extracts all the Order Numbers within the Start Number and End Number range.  These will be taken one by one to print Invoices in individual 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, then 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 its’ 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 to indicate what it does at each step, please go through them to understand the code better.

Technorati Tags: Utility

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