Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

604 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