I have a report and I am trying to use a button to select which report I need by a unique ID and have it saved using fields within my table the following code works to save the report.
Private Sub Command119_Click()
Const sReportName As String = "LandDipClearance"
Const sFile As String = "S:\Defence\Restricted\7. Movements\Land DipClear Requests\PtD_Clearance.pdf"
DoCmd.OpenReport sReportName, acViewPreview, , "[Enter PtD ID] = [PtD_ID]", acHidden
DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sFile
DoCmd.Close acReport, sReportName, acSaveNo
End Sub
I have tried to add the build just using Unit to be included in the save file and I get "Run-time error '2501': The OutputTo action was cancelled." and when I debug the Docmd line is highlighted.
Private Sub Command121_Click()
Dim rs As DAO.Recordset
Dim sFolder As String
Dim sFile As String
Const sReportName = "LandDipClearance"
sFolder = Application.CurrentProject.Path & "S:\Defence\Restricted\7. Movements\Land DipClear Requests"
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT PtD_ID, Unit FROM LandDipClearancetbl", dbOpenSnapshot)
With rs
.MoveFirst
Do While Not .EOF
sFile = sFolder & "" & Nz(![Unit], "") & ".pdf"
DoCmd.OpenReport sReportName, acViewPreview, , "[Enter PtD ID No] = [PtD_ID]", acHidden
DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sFile
DoCmd.Close acReport, sReportName, acSaveNo
.MoveNext
Loop
End With
Application.FollowHyperlink sFolder
End Sub
When I delete the sFile in the DoCmd.OutputTo row it gives me the option to save in the right folder with the file name LandDipClearance.pdf, it does this twice and then error message and the Application.FollowHyperlink sFolder is highlighted.
The end result I am after is the report would be saved using PtD_ID, Unit and Date of Entry e.g. DS-REF-0001_25EngrGp_21/07/2021.pdf
Any advice or assistance in this would be greatly appreciated.
Regards
Peter