Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Access Export to PDF with Formatting

Posted on 2013-02-06
Medium Priority
Last Modified: 2013-02-07

Can someone help me with exporting a report from MS access into PDF with some formatting.

I would need the tool to export the report to the number of records we have the in the table or the query. Every record it exports has a serial number, based on the export it needs to get highlighted. In the end all of these output files need to be merged and exported into one PDF document.

I have attached a sample output file as to how it should look.

Question by:keschuster
  • 9
  • 4
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38860097
I'm confused...
You Report looks just like the PDF.?

Ar you saying that you want the first serial number highlighted in yellow?
LVL 31

Expert Comment

by:Helen Feddema
ID: 38860120
I think you need something like the following code sample, which creates a filtered report for each record in a recordset, then exports the filtered report to a PDF file:

Public Sub SendPDFEmails()
'Created by Helen Feddema 24-Jan-2010
'Last modified by Helen Feddema 24-Jan-2010

On Error GoTo ErrorHandler

   Dim appOutlook As New Outlook.Application
   Dim dbs As DAO.Database
   Dim lngCount As Long
   Dim lngEmployeeCount As Long
   Dim lngID As Long
   Dim msg As Outlook.MailItem
   Dim rpt As Access.Report
   Dim rstEmployees As DAO.Recordset
   Dim strAttachmentsPath As String
   Dim strBody As String
   Dim strEmployeeName As String
   Dim strEMailAddress As String
   Dim strPrompt As String
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReportFile As String
   Dim strReportName As String
   Dim strSQL As String
   Dim strSubject As String
   Dim strTitle As String
   strAttachmentsPath = GetProperty("AttachmentsPath", "") & "\"
   strSubject = GetProperty("MessageSubject", "Your custom report")
   strBody = GetProperty("MessageBody", "Your current report is attached as a PDF")
   strReportName = "rptEmployeeInvoices"
   Set dbs = CurrentDb
   Set rstEmployees = dbs.OpenRecordset("qryEMailEmployees")
   lngEmployeeCount = rstEmployees.RecordCount
   Debug.Print lngEmployeeCount & " employees need reports"

   If lngEmployeeCount = 0 Then
      strTitle = "No reports to send"
      strPrompt = "No employees need reports; canceling"
      MsgBox prompt:=strPrompt, _
         buttons:=vbExclamation + vbOKOnly, _
      GoTo ErrorHandlerExit
   End If
   Do While Not rstEmployees.EOF
      lngID = rstEmployees![EmployeeID]
      strEmployeeName = rstEmployees![Salesperson]
      strEMailAddress = rstEmployees![Email]
      strReportFile = strAttachmentsPath & "Employee Invoices" _
         & " for " & strEmployeeName & ".pdf"
      Debug.Print "PDF save name and path: " & strReportFile
      'Create filtered query as report record source
      strRecordSource = "qryInvoices"
      strQuery = "qryInvoicesPerEmployee"
      If lngID <> 0 Then
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[EmployeeID] = " & lngID & ";"
      End If
      Debug.Print "SQL for " & strQuery & ": " & strSQL
      lngCount = CreateAndTestQuery(strQuery, strSQL)
      'Output customized report to PDF
      DoCmd.OutputTo objecttype:=acOutputReport, _
         objectname:=strReportName, _
         outputformat:=acFormatPDF, _
         outputfile:=strReportFile, _
      'Create new mail message and send to employee
      Set msg = appOutlook.CreateItem(olMailItem)
      With msg
         .To = strEMailAddress
         .Subject = strSubject
         .Body = strBody
         .Attachments.Add strReportFile
      End With
   strTitle = "Done"
   strPrompt = lngEmployeeCount & " PDFs created and emailed"
   MsgBox prompt:=strPrompt, _
      buttons:=vbInformation + vbOKOnly, _

   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in SendPDFEmails procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 6-Dec-2009

On Error Resume Next
   Dim qdf As DAO.QueryDef
   'Delete old query
   Set dbs = CurrentDb
   dbs.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   'Create new query
   Set qdf = dbs.CreateQueryDef(strTestQuery, strTestSQL)
   'Test whether there are any records
   Set rst = dbs.OpenRecordset(strTestQuery)
   With rst
      CreateAndTestQuery = .RecordCount
   End With
   Exit Function

   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
End Function

Open in new window

This code also emails the filtered reports, but you can leave that part out.

If you need to combine all the PDFs into a single file, you may need to do this in Adobe, or create a report that puts the data for each serial number on a separate page.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38860175
What version of Access are you using...?
I will presume Access 2007 or newer...

If your ultimate Goal is to high-lite the first record Serial Number you can use code like this on the Detail format event:

Dim lngCounter As Long

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If lngCounter = 0 Then
        Me.Serial_No.BackColor = vbYellow
       Me.Serial_No.BackColor = vbWhite
    End If
    lngCounter = lngCounter + 1
End Sub

(Then you must open the report explicitly in Print Preview to view the Highlighting...)
(There may be a way to do this with conditional formatting so you can open the Report in "Report View" and also see the Highliting)

To export the Report (with the highlighting) you can simply right click the Report in the Navigation Pane and select: Export-->PDF or XPS

...or use code like this on a button on a form:
DoCmd.OutputTo acOutputReport, "YourReportName", acFormatPDF, "C:\YourFolder\YourPDFName.pdf", True
PDF with First SN Highited yellow
See my attached modified version of your DB

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 38860237
In this case, i would need a PDF extract with 5 pages,

In the First Page : First Serial No should be highlighted
In the Second Page : Second Serial No should be highlighted
In the Third Page : Third Serial No should be highlighted
In the Fourth Page : Fourth Serial No should be highlighted
In the Fifth Page : Fifth Serial No should be highlighted

Note all the records should be visible in each page

I have attached a sample PDF in the question.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38860571
Well you can't do this *easily*
To do *exactly* what you are asking for here specifically would really be a Custom project.

I can see printing the report for as many records as there are, but then you would have to combine the PDF's (with a third party utility, or the full version or Acrobat) *Then* figure out how highlite the corresponding record on each...

Perhaps there is an "Easier" way, but I am not seeing it right away.

I'll play around with this for a while, but let's see if another Expert can see another way...

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38861003
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 38861041
...or better this if the ID will not always be perfectly sequential, for example:
-Holes in the numbering because of deleted records.
-If you ever use an ID field that needs to be filled in manually.
-You use a key with a non-standrd numbering scheme/sequence

Author Comment

ID: 38861466
This works great...Thanks a lot..... i forgot to mention another requirement.

Can we have an external PDF document merged with the one which gets created at the beginning of each page... like a standard cover page for each page (based on the GroupNumber)
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38861616

Author Comment

ID: 38861665
i probably didn't explain it clearly...

I have another PDF file on the system example at c:\XYZ.pdf (2 page document). These two pages need to get inserted before each page of the PDF file created in the database.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38861709
Then this will not be possible...
Perhaps you should be doing all of this *before* you create the PDF's...

In other words, add the source data from this "Other PDF", to the source data of your Report.

Put another way, here is what the sequence ought to be (AFAICT)
Gather *all* the source Data in MS Access
Create a Report from this data
Set all the "appearance" modifications for the report (highlighting, page breaks, Grouping, sorting, ...etc)
Create the PDF from the report

Author Comment

ID: 38861750
Do you know a way to merge the Source PDF (External File) and the Report created in the tool?
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38861836
What "Tool"?

You can "Merge" pdf documents with several utilities, (do a Google search of: Merge combine PDF)
...but the best way is to buy the full version of Acrobat

..but you cannot "Merge" a pdf with "other data" directly...

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38864128

Sorry I could not get you precisely what you needed...

But this was a thought provoking question and interesting to work on...


Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Implementing simple internal controls in the Microsoft Access application.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question