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.
- Import the following Tables from the Northwind.mdb (or Access2007 Northwind.accdb) sample database:
- Order Details
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.
- Open a new Query in SQL View without selecting any Table/Query from the displayed list.
- 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];
- 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));
- Save the new Query with the name Invoice_Orders_1.
- 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:
- 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
Create_PDF StartNumber, EndNumber, ”PDF Files Target Folder”)
Create_PDF 10248,10265,”C:\My Documents”
x = Create_PDF(10248,10265,”C:\My Documents”)
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.