CODEWITHSUNDEEP
ms-accesspdf-generation
NickK
NickK

Reputation: 3

How to generate a PDF file from Access Report?

I'm having trouble trying to export an Access report to PDF format. Basically, I'm working on an old (built in 2001) Access database that uses forms as a user interface. Currently, you can send jobs from a "Jobs" form into an "Invoice" form. Once all the jobs are there, you simply click "Invoice All", specify a date and number of copies and this prints using an Access report as the template.

I've been tasked with adding a save as PDF function, but being a web designer by trade, I have very limited knowledge of Access and VB, but I do know a (very) little amount of ASP.Net and C# (how I got given this task is story for another time...)

In my mind I've approached this by creating a new PDF button on the Access form where they print the invoices. My thoughts are that I could simply duplicate the code for the printing and update to output to a PDF file instead. I can kind of get this working, but not how I'd like.

The code for the print function is below:

Private Sub cmdOpenGroupInvoice_Click()
Dim db As DAO.Database
Dim rsGetCustomerInvoice As DAO.Recordset
Dim rsInvoice As DAO.Recordset
Dim rsInvoiceAll As DAO.Recordset
Dim lngCusID As Long
Dim lngJobNo As Long
Dim iCountInvoice
Dim lngInvoiceNo As Long
Dim iNumberCopies As Integer
Dim sSQLGetInv As String
Dim sSQLInv As String
Dim datInvoiceDate As Date

sSQLGetInv = "SELECT tblJobs.JobNo,tblJobs.NetDespatchRef, tblLoads.Sales, tblLoads.PODName, tblLoads.TotalSales, tblLoads.Cost, tblLoads.Profit, tblJobs.SendToInvoice, tblJobs.Invoiced, tblJobs.MarkForHistory, tblJobs.CustomerID" & vbCrLf _
& "FROM tblJobs INNER JOIN tblLoads ON tblJobs.JobNo = tblLoads.JobNo" & vbCrLf _
& "WHERE (((tblJobs.SendToInvoice)=Yes) AND ((tblJobs.Invoiced)=No) AND ((tblJobs.MarkForHistory)=No));"


Set db = CurrentDb
Set rsGetCustomerInvoice = db.OpenRecordset(sSQLGetInv, dbOpenDynaset)
If rsGetCustomerInvoice.EOF Then
Beep
If MsgBox("There are no jobs to invoice", _
    vbCritical + vbOKOnly, _
    "No Jobs To Invoice") = vbOK Then
    Exit Sub
End If
End If
rsGetCustomerInvoice.MoveLast
Debug.Print rsGetCustomerInvoice.RecordCount
rsGetCustomerInvoice.MoveFirst
Do Until rsGetCustomerInvoice.EOF = True
Set rsGetCustomerInvoice = db.OpenRecordset(sSQLGetInv, dbOpenDynaset)
If rsGetCustomerInvoice.EOF Then
    rsGetCustomerInvoice.Close
    db.Close
Set rsGetCustomerInvoice = Nothing
Set db = Nothing
    DoCmd.Close acForm, "frmInvoiceDate"
Exit Sub
End If
Debug.Print rsGetCustomerInvoice.RecordCount
datInvoiceDate = CVDate(txtInvoiceDate)
lngInvoiceNo = GiveMeAnInvoiceNo()
lngCusID = rsGetCustomerInvoice.Fields!CustomerID
Call AddNewInvoice(lngInvoiceNo, datInvoiceDate, True)

Debug.Print iCountInvoice
lngJobNo = rsGetCustomerInvoice![JobNo]
Call SendThisJobToSageAll(lngCusID, datInvoiceDate, lngInvoiceNo)
Call InvoiceAll(lngCusID, lngInvoiceNo)
Dim strPODName As String
If Not IsNull(rsGetCustomerInvoice!NetDespatchRef) Then
If IsNull(rsGetCustomerInvoice![PODName]) Then
strPODName = " "
Else
strPODName = rsGetCustomerInvoice![PODName]
End If
'Call NetDesTrackingJobCompleate(rsGetCustomerInvoice![NetDespatchRef], rsGetCustomerInvoice![JobNo], strPODName)
End If
iCountInvoice = iCountInvoice - 1
'Debug.Print I
iNumberCopies = txtNumberOfCopies
Do Until iNumberCopies = 0

    DoCmd.OpenReport "rptInvoice2", acViewNormal, , "[Invoice No]= " & lngInvoiceNo
iNumberCopies = iNumberCopies - 1
Loop
Form_frmInvoicing.Requery
rsGetCustomerInvoice.MoveNext
Loop
DoCmd.Close acForm, "frmInvoiceDate"
rsGetCustomerInvoice.Close

db.Close
Set rsGetCustomerInvoice = Nothing

Set db = Nothing


End Sub

With my original plan outlined above, I updated the below section to output to PDF:

Do Until iNumberCopies = 0

        DoCmd.OpenReport "rptInvoice2", acViewNormal, , "[Invoice No]= " & lngInvoiceNo
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, True
    iNumberCopies = iNumberCopies - 1
    Loop

Now this works and it does attempt to output a PDF file. The trouble is, it's runs the report and creates an invoice for every job in the system, rather than applying the report to JUST the jobs which are marked for invoicing.

I'm hoping this is happening because I've put the code in the wrong location, but I have a gut feeling that it's more complicated than that.

It's a bit of a long shot posting it on here, but I really appreciate any help at this point. I've also tried to keep this as short as possible, so if there's any details which aren't clear, I'll help out.

Upvotes: 0

Views: 20639

Answers (2)

Paul Green
Paul Green

Reputation: 111

I asked the same question a couple of years ago on UtterAccess..

There is a free library to do exactly what you need here: http://www.lebans.com/reporttopdf.htm

My original thread at UA is here : http://www.utteraccess.com/forum/Automatically-PDF-send-t1353547.html

I successfully used the library for a couple of years in several projects, mainly to generate quotes and invoices.

Hope this helps PG

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91326

That is quite convoluted, so I think the simplest thing to do, if you do not wish to dive in and tidy up, is to modify the query that the report is based on.

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("MyReportQuery")
sSQL = "SELECT Whatever FROM MyTable WHERE [Invoice No]= " & lngInvoiceNo
qdf.SQL = sSQL

DoCmd.OutputTo acOutputReport, "rptInvoice2", acFormatPDF, _
    MyPath & MyFilename, True

Unless you have Access 2007 with the Save as PDF Add-on or 2010, you may be best installing say, cutePDF, and printing to the PDF printer using DoCmd.PrintOut

Upvotes: 4

Related Questions