Output multiple PDFs From Report (Help!) (1 Viewer)

[VBA-ish]

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:

Option Compare Database
Option Explicit

Public CurrentEmployee As String

Public Function PassEmployee() As String
PassEmployee = CurrentEmployee
End Function

This is the code calling the function in the query design criteria. Id did not do anything - no output, no errors

 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

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.

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!

 

[Ranman256]

You don't have to open a report to make the PDF.
Just docmd.outputo

Produce 1 report using the report query to filter via a listbox.

 

[VBA-ish]

I know. Please re-read my question. I'm not opening the report in any of that code. Also, I want ALL of the employees to print individually with one event procedure. There are thousands of employees; it's not practical to select them individually with a listbox (unless there is some other trick using a listbox I'm not aware of).

 

[Minty]

Your Current employee for your query is being set outside the loop. You need to open that recordset each time within the loop.

 

[VBA-ish]

Your Current employee for your query is being set outside the loop. You need to open that recordset each time within the loop.

Thanks for the reply.

I've re-done this so many times and have been staring at it for so long it's starting to melt together. Are you saying I need to set the recordset inside the loop? Can you extrapolate a little more, please?

 

[Minty]

Briefly - your code below;

strSQL = "SELECT * FROM tblFullListbyHub INNER JOIN tblCLTAnnualPerformance ON " & _
       "tblFullListbyHub.[Employee ID]=tblCLTAnnualPerformance.[Employee ID] WHERE " & _
       "tbyFullListByHub.[EMPLOYEE NAME]= '" &[COLOR="Red"] CurrentEmployee [/COLOR]& "'"
    
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    If rs.RecordCount = 0 Then Exit Sub

    While Not rs.EOF
        [COLOR="RoyalBlue"]CurrentEmployee [/COLOR]= rs!CurrentEmployee
        MyFileName = rs("EMPLOYEE NAME") & ".PDF"

        DoCmd.OutputTo acOutputReport, "rptCLTEmployeeReport(2)", acFormatPDF, myPath & MyFileName

        rs.MoveNext
    Wend

Appears to select one employee's records (in RED) then loop around the employees but isn't changing the report for that one employees record. Hence you only get the one or no set of results. In basic terms you need to create your report for each employee one at a time so you need to, in this order do the following;

Create your list of employees we'll call it RsEmps with all the employees you need as a report so something like;

Dim RsEmps as Recordset

sSqlEmps = "SELECT distinct [tblFullListbyHub]![EMPLOYEE NAME] FROM [qryReportTest]"

Now loop through the employee records but include a Where Clause for the report.

DoCmd.OutputTo acOutputReport, "rptCLTEmployeeReport(2)", acFormatPDF, myPath & MyFileName, [COLOR="Red"]"[Employee Name] = '" & RsEmp("EMPLOYEE NAME") & "'"[/COLOR]

 

[VBA-ish]

I really do appreciate your help; however, I'm still having problems. I understand what you're saying, and I know what I want it to do, but I'm having trouble executing it.

This is what I have now:

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
Dim strSQL As String
Dim RsEmp As Recordset
 
strSQL = "select distinct [tblFullListbyHub]![EMPLOYEE NAME] from [qryReportTest]"
 
myPath = "C:\Users\xxxx\Desktop\TEST\"
Set db = CurrentDb()
    
    Set RsEmp = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    If RsEmp.RecordCount = 0 Then Exit Sub
 
     While Not rs.EOF
        MyFileName = RsEmp("EMPLOYEE NAME") & ".PDF"
         DoCmd.OutputTo acOutputReport, "rptCLTEmployeeReport(2)", acFormatPDF, myPath & MyFileName, _
        "[EMPLOYEE NAME] = '" & RsEmp("EMPLOYEE NAME") & "'"
        
        RsEmp.MoveNext
    Wend
 
 RsEmp.Close
Set RsEmp = Nothing
 db.Close
Set db = Nothing
  
 End Sub

I wasn't sure on the name of the SQL variable in this line so I assumed and changed it to "strSQL" (don't know if that's what you intended):

 sSqlEmps = "SELECT distinct [tblFullListbyHub]![EMPLOYEE NAME] FROM [qryReportTest]"

When this code runs, I get an error of "Too few parameters. Expected 1" on this line:

 Set RsEmp = db.OpenRecordset(strSQL, dbOpenSnapshot)

 

[Minty]

Okay I can't tell if your query is correct - open a new query bring in qryReportTest and create the query that gets all the unique (DISTINCT) employees - post teh SQL of that from the designer back here.
Or does qryReportTest expect a parameter?

 

[VBA-ish]

It does not expect a parameter. Each employee only exists once in the table. The other table has performance data linked to the employee (by a unique number). The report always produced unique employees, the trouble I was having was hitting the limit on open table references when trying to output each employee in the report into separate PDFs (initially, I was opening the report in the loop, which worked fine until about 250 PDFs). When I print the report into a single PDF (currently 800 or so employees) it works fine.

So, now that I've clarified my issue a little (sorry for not doing so earlier), I'm not exactly sure what it is that you need me to do.

 

[Minty]

Ok - without knowing your data this is a bit of a guess, but here goes.
You should still create the employee list from the results of your report query. This will prevent you trying to generate a blank report.
1. Does this SQL statement work

strSQL = "select distinct [tblFullListbyHub]![EMPLOYEE NAME] from [qryReportTest]"

I suspect it won't as I think it's referenced incorrectly It probably should be
"select distinct [EMPLOYEE NAME] from [qryReportTest]" assuming you don't have the field [EMPLOYEE NAME] in there twice.

 

[VBA-ish]

Yes, you're correct - I originally had it like that, but when I had the function in the criteria field of [employee name] in the query design, it started giving me an error for a reference to more than one [employee name] (which I'm not sure why, there's only one in the query). I added the table name to solve that error. I have since removed it as you suggested and now I receive an error stating it is the 'wrong data type for one of the arguments' on this line of code:

DoCmd.OutputTo acOutputReport, "rptCLTEmployeeReport(2)", acFormatPDF, myPath & MyFileName, _
"[EMPLOYEE NAME] = '" & RsEmp("EMPLOYEE NAME") & "'"

 

[Minty]

Okay - what if you hard code the filter part of opening the report;
"[Employee Name] = 'John Doe'"
Do you get the same error? if not add a debug.print RsEmp("EMPLOYEE NAME") to make sure you are getting the correct result