title: How to generate a PDF file from Access Report?
body: <p>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.</p>
  
  <p>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 (<em>very</em>) little amount of ASP.Net and C# (how I got given this task is story for another time...)</p>
  
  <p>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.</p>
  
  <p>The code for the print function is below:</p>
  
  <pre><code>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" &amp; vbCrLf _
  &amp; "FROM tblJobs INNER JOIN tblLoads ON tblJobs.JobNo = tblLoads.JobNo" &amp; vbCrLf _
  &amp; "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]= " &amp; 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
  </code></pre>
  
  <p>With my original plan outlined above, I updated the below section to output to PDF:</p>
  
  <pre><code>Do Until iNumberCopies = 0
  
          DoCmd.OpenReport "rptInvoice2", acViewNormal, , "[Invoice No]= " &amp; lngInvoiceNo
          DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath &amp; MyFilename, True
      iNumberCopies = iNumberCopies - 1
      Loop
  </code></pre>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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.</p>