Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Saving Report Pages as separate PDF Files

You are printing several Customer Invoices as a single Report, running into several pages.  You have problems with this kind of Reports 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 will not only save the cost of stationery but also delivers the invoices to the customers’ mailbox instantly without delay.  Besides that customers can easily view the Invoices on their machines or take printouts at their end, if that become necessary.  In short, part of our tasks are passed on to the customers, saving our time and money.  I don’t think I can stretch it any more.

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.accdb) sample database:
    • Order Details
    • Products 

    NB: I will be using Tables from the Northwind.mdb database. But, the queries, Report and Code 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.
  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 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.
  6. Design a Report to print Sales Invoice using Invoice_Orders_1 Query as Record Source.

    Sample Report Design Image is given below:

    Sample Report Preview Image is the next one:

  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

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 Order Details Table for the Customer Invoice Report. Besides that it calculates the Total Value after 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 specific OrderID in the second Query (Invoice_Orders_1),  created using the earlier Query (Invoice_Orders_0) as 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 OrderId level Invoice is saved in 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 into 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.

Keeping that in focus let us continue to review what we are doing in the VBA Code lines.  You must call the Function using the following

Syntax:

Create_PDF  StartNumber,  EndNumber, ”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 Rpt_Invoice Report is Invoice_Orders_1 Query. This is redefined for each OrderId as criteria for printing the Rpt_Invoice in PDF format.  The PDF files are saved in the location specified as third parameter C:\My Documents.

Each line in the VBA Code is commented to indicate what it does in each step, please go through them to understand the code better.

Technorati Tags:
Share:

2 comments:

  1. Question: What if I have a distinct source file with user names (txt)? I would like to loop through that file and print a report for each user. I followed your logic, but fail to grasp what I need to modify to equate the start and end #

    ReplyDelete
  2. If the User-Names are part of the data then you can modify the code as follows:

    Open "C:\FolderName\param.txt" For Input As #1
    Do While Not EOF(1)
    'get the User Name
    Input #1, strName
    If len(trim(strName))> 0 Then
    .
    .
    .
    .
    End if
    Loop
    Close #1

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Menus and Toolbars Accesstips MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Variables Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts