Mail Merge - Printing option

[neveu]

Hello forum,

i made a report in Access to create invoices of separate pages.
what i need next is to have this each page saved separately ( individual file) in PDF somewhere on my drive.the name of the file should be the invoice number that appears on the invoices which is in field [Invoice Number].
i searched through Access printing option but this is not possible.

can you give me some advices?

 

[apr pillai]

It is kind of difficult to do, but not impossible. We need a VBA Function, two Queries and the Report to start with.

A sample run with the Order Details Table from Northwind.accdb database given below:

1. Create a Query with the following SQL and name the Query as Query_1:

SELECT [Order Details].*
FROM [Order Details]
WHERE ((([Order Details].OrderID)=10252));

1. Design a sample Report with the fields: OrderID, ProductID, Quantity, UnitPrice.
2. Select OrderID in Grouping & Sorting and display the Group-Footer Section.
3. Create a Textbox in the Group Footer Section and position it below the Quantity Column and write the expression: =Sum([Quantity]) in the Control Source Property.
4. Click on the Group Footer Section and Display its Property Sheet (F4).
5. Select After Section in the Force New Page Property.
6. Save the Report with the name Report3.
7. Create a second Query with the following SQL and save it with the name Query_param:

SELECT TOP 5 [Order Details].OrderID
FROM [Order Details]
GROUP BY [Order Details].OrderID
ORDER BY [Order Details].OrderID;

Copy and Paste the following Functiion into a Standard VBA Module and save it:

Public Function CreatePdf()
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

strsql_1 = "SELECT [Order Details].* "
strsql_1 = strsql_1 & "FROM [Order Details] WHERE ((([Order Details].OrderID)="


Set db = CurrentDb
Set QryDef = db.QueryDefs("Query_1")
Set rst = db.OpenRecordset("Query_Param", dbOpenDynaset)

Do While Not rst.EOF
  int_Order = rst!OrderID

  criteria = int_Order & "));"

  strsql = strsql_1 & criteria
  QryDef.sql = strsql
  db.QueryDefs.Refresh
  
  'change this line for correct path on your disk
  outFile = "C:\MDBS\" & int_Order & ".PDF"

  DoCmd.OutputTo acOutputReport, "Report3", "PDFFormat(*.pdf)", outFile, False, "", 0, acExportQualityPrint

'3 seconds delay loop to give time for each pdf file to create on disk
  T = Timer
  Do While Timer < T + 3
    DoEvents
  Loop
  rst.MoveNext
Loop
rst.Close

Set rst = Nothing
Set db = Nothing
Set QryDef = Nothing

End Function

1. Click somewhere in the middle of the code and press F5 to run the code. You may run the code with a Command Button click from a Form.
2. Check the target location on your disk whether the pdf files have been created or not. You must be using Access2007 or later version.

Hope you got the idea how to go about it using the code with your own Data and Report, with changes at appropriate places.

 

[neveu]

thank you,
will give it a try.

thank you very much . i really appreciate it a lot.

all the best,
neveu