You might need a semi-colon at the end of the string like this:
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [ID],[last_name ] FROM [Qry1];"
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
error number:3061
error source: SavetoPDF_Click
error description: Too few parameters. Expected 3.
Qry1 is the name of the query that will be used in the report
Payslip_Report is the name of my report
I got my code from https://social.msdn.microsoft.com/Forums/en-US/53f85f8b-6800-4c5a-a8c8-c974ca6b4d53/print-each-record-to-separate-pdf-file-using-certain-field-for-filename?forum=accessdev and it seem to work but mine isn't. Appreciate all the help i will get.
This is my code:
Option Compare Database
Private Sub SavetoPDF_Click()
Dim rs As DAO.Recordset
Dim rpt As Access.Report
Dim sFolder As String
Dim sFile As String
Const sReportName = "Payslip_Report"
On Error GoTo Error_Handler
'The folder in which to save the PDFs
sFolder = Application.CurrentProject.Path & "C:\Users\user\Desktop\payslip\"
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT ID,last_name FROM Qry1", dbOpenSnapshot)
With rs
If .RecordCount <> 0 Then 'Make sure we have record to generate PDF with
'Open the Report
DoCmd.OpenReport sReportName, acViewPreview, , , acHidden
'Define a report object so we can manipulate it below
Set rpt = Reports(sReportName).Report
.MoveFirst
Do While Not .EOF
'Build the PDF filename we are going to use to save the PDF with
sFile = Nz(![last_name], "") & Nz(![first_name], "") & Nz(![last_name], "") & ".pdf"
sFile = sFolder & sFile
'filter the report to the specific record or criteria we want
rpt.Filter = "[ID]=" & ![ID]
rpt.FilterOn = True
DoEvents 'This is critical!!!!
'Print it out as a PDF
'DoCmd.OpenReport "Payslip_Report", acViewPreview, etc etc
DoCmd.OutputTo acOutputReport, sReportName, acFormatPDF, sFile, , , , acExportQualityPrint
'If you wanted to create an e-mail and include an individual report, you would do so now
.MoveNext
Loop
'Close the report now that we're done with this criteria
DoCmd.Close acReport, sReportName
End If
End With
'Open the folder housing the PDF files (Optional)
Application.FollowHyperlink sFolder
Error_Handler_Exit:
On Error Resume Next
If Not rpt Is Nothing Then Set rpt = Nothing
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub
Error_Handler:
If Err.Number <> 2501 Then 'Let's ignore user cancellation of this action!
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: Command0_Click" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
End If
Resume Error_Handler_Exit
End Sub
You might need a semi-colon at the end of the string like this:
Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [ID],[last_name ] FROM [Qry1];"