Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1297
  • Last Modified:

Access Export to PDF with Formatting


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.

  • 9
  • 4
1 Solution
Jeffrey CoachmanMIS LiasonCommented:
I'm confused...
You Report looks just like the PDF.?

Ar you saying that you want the first serial number highlighted in yellow?
Helen FeddemaCommented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

keschusterAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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...

Jeffrey CoachmanMIS LiasonCommented:
Jeffrey CoachmanMIS LiasonCommented:
...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
keschusterAuthor Commented:
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)
Jeffrey CoachmanMIS LiasonCommented:
keschusterAuthor Commented:
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.
Jeffrey CoachmanMIS LiasonCommented:
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
keschusterAuthor Commented:
Do you know a way to merge the Source PDF (External File) and the Report created in the tool?
Jeffrey CoachmanMIS LiasonCommented:
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...

Jeffrey CoachmanMIS LiasonCommented:

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

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 9
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now