Okay, I'll preface this by saying this project caused my first attempt at this. I originally got it to work fine by grouping on the report (opened report, docmd.output to PDF, close report - then closed rst and set =nothing); however, I quickly ran into the dreaded 3014 error (can't open any more tables). This occurred at around 250 or so PDFs. Since then, I have tried countless solutions, none getting what I need. I received advice on another forum to pass a public function to the query, and I have tried that but can't get it to work. I have tried this using the query design as well as SQL in VBA. I'll post code below...any help is greatly appreciated!
This is the module that housed the function:
This is the code calling the function in the query design criteria. Id did not do anything - no output, no errors
This is the above code except using SQL instead of the query design. Before I added the WHERE clause it would output all employees into one PDF and then proceed to dump all employees into the next file (it does recognize that it is moving through the rst based on the file names). I need it to make individual PDFs. After I added the WHERE clause it throws a "too few parameters" error.
Please help!
This is the module that housed the function:
Code:
Option Compare Database
Option Explicit
Public CurrentEmployee As String
Public Function PassEmployee() As String
PassEmployee = CurrentEmployee
End Function
Code:
Option Compare Database
Option Explicit
Public CurrentEmployee As String
Public Function PassEmployee() As String
PassEmployee = CurrentEmployee
End Function
This is in the form's code:
Option Compare Database
Option Explicit
Private Sub btnpdf_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim myPath As String
Dim temp As String
myPath = "C:\Users\xxxx\Desktop\TEST\"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT distinct [tblFullListbyHub]![EMPLOYEE NAME] FROM [qryReportTest]", dbOpenSnapshot)
If rs.RecordCount = 0 Then Exit Sub
While Not rs.EOF
CurrentEmployee = rs![EMPLOYEE NAME]
MyFileName = rs("EMPLOYEE NAME") & ".PDF"
DoCmd.OutputTo acOutputReport, "rptCLTEmployeeReport(2)", acFormatPDF, myPath & MyFileName
rs.MoveNext
Wend
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub
Code:
strSQL = "SELECT * FROM tblFullListbyHub INNER JOIN tblCLTAnnualPerformance ON " & _
"tblFullListbyHub.[Employee ID]=tblCLTAnnualPerformance.[Employee ID] WHERE " & _
"tbyFullListByHub.[EMPLOYEE NAME]= '" & CurrentEmployee & "'"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount = 0 Then Exit Sub
While Not rs.EOF
CurrentEmployee = rs!CurrentEmployee
MyFileName = rs("EMPLOYEE NAME") & ".PDF"
DoCmd.OutputTo acOutputReport, "rptCLTEmployeeReport(2)", acFormatPDF, myPath & MyFileName
rs.MoveNext
Wend
Please help!