Solved Export Single Record Report to PDF (1 Viewer)

[kevnaff]

Hello All,

I have created a command button on my form to create a report and save this as a PDF, which works perfectly using the code below:

Dim FolderPath As String, FileName As String
    
    'Exports report as PDF to Folder Path with Code Number as the file name
    
    FolderPath = "G:\Medical Engineering\Medipro\Scrap Notification Letters\"
    FileName = FolderPath & Me.[Code No] & ".pdf"
    DoCmd.OutputTo acReport, "ReportScrapNotification", acFormatPDF, FileName, False

However this exports all of the records in this table to the .PDF file. I am hoping to just export the record which the form is filtered to.

Is anybody aware of how this could be solved?

Thanks all

 

[sonic8]

Before using OutputTo to export the report, you must open it (hidden) with the filter supplied in the WhereCondition of the Docmd.OpenReport statement.
See Output a dynamically filtered Access report to PDF for a more detailed description.

 

[kevnaff]

Before using OutputTo to export the report, you must open it (hidden) with the filter supplied in the WhereCondition of the Docmd.OpenReport statement.
See Output a dynamically filtered Access report to PDF for a more detailed description.

Thanks sonic8,

I've added the following code and now it's working perfectly:

    Dim reportName As String
    Dim criteria As String
    Dim MSG As String
    Dim O As Outlook.Application
    Dim M As Outlook.MailItem
    Dim FolderPath As String, fileName As String
    
    DoCmd.Save
    DoCmd.RefreshRecord

    
    
    reportName = "ReportScrapNotification"
    FolderPath = "G:\Medical Engineering\Medipro\Scrap Notification Letters\"
    fileName = FolderPath & Me.[Code No] & ".pdf"
    criteria = "[ScrapNotifyID] = " & Me.[ScrapNotifyID]
    
    'Exports report as PDF to Folder Path with Code Number as the file name
    
    DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
    DoCmd.OutputTo acReport, "ReportScrapNotification", acFormatPDF, fileName, False
    DoCmd.Close acReport, reportName, acSaveNo

Thanks for your help.

 

[Pat Hartman]

Although this method works fine for a couple of instances of a report, it is very inefficient if you have a lot. For example, I have to export at least 100 reports at one time every month with commission data. Since the OutputTo method does not have the WHERE option that the OpenReport method does, you have to use a different technique. Usually, I modify the query of the report to reference a hidden form control. So, the Where clause of the report is:

Where MyID = Forms!MyForm!txtHiddenMyID

Then in the loop that prints the report, instead of running an OpenReport method, I copy the "MyID" for this report to the hidden control on the form and when the OutputTo runs, the report gets its necessary criteria from the hidden field on the form rather than having to open the report separately.

 

[GPGeorge]

Although this method works fine for a couple of instances of a report, it is very inefficient if you have a lot. For example, I have to export at least 100 reports at one time every month with commission data. Since the OutputTo method does not have the WHERE option that the OpenReport method does, you have to use a different technique. Usually, I modify the query of the report to reference a hidden form control. So, the Where clause of the report is:

Where MyID = Forms!MyForm!txtHiddenMyID

Then in the loop that prints the report, instead of running an OpenReport method, I copy the "MyID" for this report to the hidden control on the form and when the OutputTo runs, the report gets its necessary criteria from the hidden field on the form rather than having to open the report separately.

How about creating a tempvar with the value for the criteria and use that tempvar to filter the records in the query? It can be set once for all reports or reset prior to each report being created. It works a lot like the hidden text field on a form, but doesn't require that form to be open and the hidden text field populated.

 

[Pat Hartman]

You can use a TempVar if you want to. I don't like them so I never use them The technique is the same. You loop through the control recordset and for each report you want to print, you change the value of the TempVar.