Solved

Access Export to PDF with Formatting

Posted on 2013-02-06
14
1,197 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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now