Agfdhyk

My organization uses a MS Access database to store incident reports. I have a report called "Case Details" and I want to have the option to save the report that's currently open as a PDF. I've got it working for the most part, the only issue is that when saving the PDF, it's saving all of the records, not just the record that was being viewed.

How do I fix it so that it only saves the record being viewed, using the record's ID?

Here's the code for the save button.

Private Sub Command1626_Click()

DoCmd.OutputTo acOutputReport, "Case Details", acFormatPDF, "G:PoliceRestrictedSaved Reports " & Me.txtPDFRef & ".pdf", True

End Sub

The "txtPDFRef" box on the report contains the following code, which is used to name the report. (Case Number - Officer - Date Saved)

="CR" & Format(Date(),"yy") & "-000" & [ID] & " - " & [ReportingOfficer] & " - " & Format(Date(),"yyyymmdd")

It seems the only way is to filter the report to the record to be printed.
So it's essential to determine the ID of the record to be printed.

It's not clear for me, if your button is located on the report itself or maybe on a form.

Button on the report itself

I tried to get it running when the button is on the report itself.
To determine the current ID it seems to be necessary that the calling button is located in the detail section of the report.

Then you can filter the report, export it to pdf and restore the maybe before applied filter.

The only backdraw is, that I found no way to view the last viewed record in the report after the code executed.
Instead you are on the first record.

This would be the code for the button:

Dim currentFilter As String

currentFilter = Me.Filter



Dim currentFilterOn As Boolean

currentFilterOn = Me.FilterOn



'If field type of ID is a number use this:

Me.Filter = "[ID] = " & Me.ID

'If field type of ID is a string use this:

'Me.Filter = "[ID] = '" & Me.ID & "'"



Me.FilterOn = True



DoCmd.OutputTo acOutputReport, Me.Name, acFormatPDF, "G:PoliceRestrictedSaved Reports " & Me.txtPDFRef & ".pdf", True



Me.Filter = currentFilter

Me.FilterOn = currentFilterOn

Button on a form

If the button is for example on a form and you just want to print the regarding report, then you could use this code in this button:

Const REPORT_NAME As String = "Case Details"



Dim condition As String

'If field type of ID is a number use this:

condition = "[ID] = " & Me.ID

'If field type of ID is a string use this:

'condition = "[ID] = '" Me.ID & "'"



DoCmd.OpenReport REPORT_NAME, acViewPreview, , condition, acHidden



'You would have to provide txtPDFRef in another way, because the report isn't open yet.

DoCmd.OutputTo acOutputReport, REPORT_NAME, acFormatPDF, "G:PoliceRestrictedSaved Reports " & Me.txtPDFRef & ".pdf", True



DoCmd.Close acReport, REPORT_NAME

It closes the report after saving to pdf. Be aware that you maybe have to provide txtPDFRef in a different way. But maybe it exists on the form too with the same name, that would make it run immediately.

Simplest solutions:

Option 1

Open the report in preview with a filter, then use outputTo without the ObjectName argument.

docmd.openReport "Case Details",,"id = " & id

docmd.outputTo objectType:=acOutputReport, outputFormat:=acFormatPDF, outputFile:= somefileName

docmd.close acOutputReport, "Case Details"

Option 2 (even simpler)

In design view, change the reports'filter property to refer to a control in the calling form.

Something like id = forms!someForm.idControl