I have found several threads on this topic, and have found some code to get started with in this thread:
http://access-programmers.co.uk/forums/showthread.php?t=206372&highlight=outputto&page=2
But after trying several tweaks, I just can't quite get it to work. I am fairly new to Access and can handle vba for doing things on forms but recordsets and this type of code is a little over my head, so please if you have any suggestions, I will probably need it spelled out.
Here is what I'm trying to do: I have a small database to track errors found on accounts. I would like a pdf report to be saved for each processor [ProcessorName] on a drive, which would be done once a week. In another database, I have it set up to select the Processor in a combobox and export the report to the drive. This works fine, but that requires each processor to be selected individually. I would like it to save each processor's report in one click, if possible.
Here is what I have:
Query: qryUCCErrors - shows errors found by Processor
Form: frmReports - has txtStartDate and txtEndDate. The user enters the date range they would like to filter the report by, and the query references those controls as criteria for [QCDate].
Report: rptUCCErrors - Grouped on ProcessorName
Here is what I have for code:
It is currently stuck on the DoCmd.OutputTo line with a runtime error 2501: The OutputTo action was canceled. Also, I am totally guessing on the top section and the parameters. I don't know anything about this and am just using what I found in other threads.
Is anything standing out as incorrect? Don't mind the beginning of the drive, it is a network drive with the long path, but I took it out as it has the company name. Any help would be greatly appreciated!
http://access-programmers.co.uk/forums/showthread.php?t=206372&highlight=outputto&page=2
But after trying several tweaks, I just can't quite get it to work. I am fairly new to Access and can handle vba for doing things on forms but recordsets and this type of code is a little over my head, so please if you have any suggestions, I will probably need it spelled out.
Here is what I'm trying to do: I have a small database to track errors found on accounts. I would like a pdf report to be saved for each processor [ProcessorName] on a drive, which would be done once a week. In another database, I have it set up to select the Processor in a combobox and export the report to the drive. This works fine, but that requires each processor to be selected individually. I would like it to save each processor's report in one click, if possible.
Here is what I have:
Query: qryUCCErrors - shows errors found by Processor
Form: frmReports - has txtStartDate and txtEndDate. The user enters the date range they would like to filter the report by, and the query references those controls as criteria for [QCDate].
Report: rptUCCErrors - Grouped on ProcessorName
Here is what I have for code:
It is currently stuck on the DoCmd.OutputTo line with a runtime error 2501: The OutputTo action was canceled. Also, I am totally guessing on the top section and the parameters. I don't know anything about this and am just using what I found in other threads.
Code:
Private Sub Command6_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("qryUCCErrors")
qdf.Parameters(0).Value = Forms!frmReports!txtStartDate
qdf.Parameters(1).Value = Forms!frmReports!txtEndDate
Set rst = qdf.OpenRecordset
Do While Not rst.EOF
strRptFilter = "[ProcessorName] = " & Chr(34) & rst.Fields("ProcessorName") & Chr(34)
DoCmd.OutputTo acOutputReport, "rptUCCErrors", acFormatPDF, "I:\Direct and Indirect Admin\Collateral QC Reports\" & "\" & rst![ProcessorName] & Format(Date, "mmddyyyy") & ".pdf"
DoEvents
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set qdf = Nothing
Set db = Nothing
End Sub
Is anything standing out as incorrect? Don't mind the beginning of the drive, it is a network drive with the long path, but I took it out as it has the company name. Any help would be greatly appreciated!