Solved

Access Export to PDF with Formatting

Posted on 2013-02-06
14
1,248 Views
Last Modified: 2013-02-07
Hi,

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.


Thanks.
Sample.pdf
Test-DB.accdb
0
Comment
Question by:keschuster
  • 9
  • 4
14 Comments
 
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?
0
 
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, _
         Title:=strTitle
      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, _
         autostart:=False
      
      'Create new mail message and send to employee
      Set msg = appOutlook.CreateItem(olMailItem)
      With msg
         .To = strEMailAddress
         .Subject = strSubject
         .Body = strBody
         .Attachments.Add strReportFile
         .Send
      End With
   
NextEmployee:
      rstEmployees.MoveNext
   Loop
   
   strTitle = "Done"
   strPrompt = lngEmployeeCount & " PDFs created and emailed"
   MsgBox prompt:=strPrompt, _
      buttons:=vbInformation + vbOKOnly, _
      Title:=strTitle

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   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
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
   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.
0
 
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
    Else
       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


JeffCoachman
Test-DB.accdb
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:keschuster
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.
0
 
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...

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38861003
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 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
Access-EEQ28021931PrintHighlight.accdb
0
 

Author Comment

by:keschuster
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)
Sample.pdf
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38861616
0
 

Author Comment

by:keschuster
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.
0
 
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
0
 

Author Comment

by:keschuster
ID: 38861750
Do you know a way to merge the Source PDF (External File) and the Report created in the tool?
0
 
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...

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38864128
Thanks,

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

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

JeffCoachman
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

830 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