Generating Reports for Each Value in a List

[Jessica240]

Hello,



A form includes a combo box containing a list of about 20 employees. The user selects the employee and clicks a button to view and export that employee's report. The user must do this 20 times to get all reports exported which is time consuming.

What is the best way to export the reports as PDFs for each employee in the list? That would save time from having to select from the drop down, click the export button, and repeating 20 times.

Thanks in advance!

Jessica

[ranman256]

have a combo with all the employees in it, cboEmps.
you can have a combo box with the report to print, cboRpt.

click button to cycle thru all employees to print

The QUERY in the report looks at the combo box to filter the 1 person.

sub btnPrint_click()
Dim i As Integer
dim vEmp, vDir, vFile, vGrp

vDir = "C:\folder\"      'target folder

For i = 0 To cboEmps.ListCount - 1
   vEmp = cboEmps.ItemData(i)         'get the next item in list
   cboEmps = vEmp                    'set box to next person
 
   vFile = vDir & vEmp & Format(date,"yymmdd") & ".pdf"

   docmd.OutputTo  acOutputReport ,cboRpt ,acFormatPdf,vFile
Next
end sub

[ssanfu]

If you have room on the form, I would use a multi-select list box. Select all of the employees or 1 or 6 or 16, ...

Could have a button to select all of the employees or un-select all and a button to create the PDFs. Easy-peasy.

[Jessica240]

I'm not sure that a multi-select list box would work, but I've never used one before. The way the database is designed is there are tables that contain the data for all employees. Those tables are the record source for queries which are the record source for the reports.

The queries use the value selected in the combo box on the form as the criteria so that the report contains information for only that employee. Would the criteria work if the user selected multiple employees?

I will try ranman256's suggestion, but I am a very basic VBA user, so hopefully I can get it to work.

[ssanfu]

The queries use the value selected in the combo box on the form as the criteria so that the report contains information for only that employee. Would the criteria work if the user selected multiple employees?

A list box is (almost) the same as a combo box, just that the list box displays all/most of the the items. You could select the employees, click a button, and the code would loop through the selected items in the list box creating a report for each employee.

Post back if you run into problems...

[June7]

ranman, don't think a combobox can work for this.

A multi-select listbox would allow user to pick and choose multiple employees to generate reports for. Many examples of looping through listbox selected items. Here is one

    'Loop through the ItemsSelected in the list box.    
    With Me.lstEmployees
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'use listbox item to open filtered report
                DoCmd.OpenReport "report name", , acViewPreview , "EmployeeID=" & .ItemData(varItem)
                DoCmd.OutputTo acOutputReport , , acFormatPdf, "path\filename.pdf"
                DoCmd.CloseReport
            End If
        Next
    End With

If you just want to automatically generate reports for all employees without user discretion, then have code open a recordset of employees and loop through recordset.

[Jessica240]

Thanks June! I understand most of your code except what does "Me.1stEmployees" from the first line of your code represent? Is 1stEmployees supposed to be the name of the list box?

I do want it to automatically generate reports for all employees without user discretion. How do I add in the recordset part to your code?

[June7]

Yes, name of listbox. lstEmployees, not 1stEmployees. lst for list, not 1st using number 1.

However, is moot point if you don't want to use listbox.

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset ("SELECT EmployeeID FROM tblEmployees;", dbOpenDynaset)
If Not rs.BOF And Not rs.EOF Then
    While Not rs.EOF
        DoCmd.OpenReport "report name", , acViewPreview , "EmployeeID=" & rs!EmployeeID
        DoCmd.OutputTo acOutputReport , , acFormatPdf, "path\filename.pdf"
        DoCmd.CloseReport
        rs.MoveNext
    Wend
End If
rs.Close

[Jessica240]

June you are incredible! If there is ever any way I could repay you for all of your help (besides adding to your reputation) I would love the opportunity to do so!

[Jessica240]

June when I try running your code I get this error: "Compile error: Method or data member not found"

Attached is my database. Do you know what's causing that error?

[June7]

Before I go through effort of downloading and running db, post your exact code. Did you use your actual table, field, report names? How do you construct the destination file path\name?

[Jessica240]

Private Sub Command73_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT EmployeeID FROM tblEmployees;", adOpenDynaset)
If Not rs.BOF And Not rs.EOF Then
    While Not rs.EOF
        DoCmd.OpenReport "Report1", , acViewPreview, "EmployeeID=" & rs!EmployeeID
        DoCmd.OutputTo acOutputReport, , acFormatPDF, "C:users\jwright\desktop\Test.pdf"
        DoCmd.CloseReport
        rs.MoveNext
    Wend
End If
rs.Close
End Sub

I changed the table name and field name to match your code. So the table name is "tblEmployees" and the field name is "EmployeeID"

I simplified my database overall to help me learn VBA because I find it easier to practice on. Plus I thought it would be easier for you if the database had only a few objects. I hope I constructed the destination file path\name correctly. I wasn't exactly sure on that part.

[June7]

Sorry, typo on my part - memory failure. Fixed my earlier post. Instead of adOpenDynaset should be dbOpenDynaset. Or don't use it, not critical in this case to spedify Type argument parameter.

Suggested code presumes EmployeeID is number type.

Also missing \ after C: in the path.

To dynamically assign PDF filename, something like:

DoCmd.OutputTo acOutputReport, , acFormatPDF, "C:\users\jwright\somefoldername\" & rs!EmployeeID & "-" & Date()

[Jessica240]

I get the same "Method or data member not found" error. Here is my new code:

Private Sub Command73_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT EmployeeID FROM tblEmployees;", dbOpenDynaset)
If Not rs.BOF And Not rs.EOF Then
    While Not rs.EOF
        DoCmd.OpenReport "Report1", , acViewPreview, "EmployeeID=" & rs!EmployeeID
        DoCmd.OutputTo acOutputReport, , acFormatPDF, "C:\users\jwright\desktop\" & rs!EmployeeID & "-" & Date
        DoCmd.CloseReport
        rs.MoveNext
    Wend
End If
rs.Close
End Sub

I changed the data type of the employeeID fields to numeric instead of short text. I can't tell which part of the code is causing the error?

[Jessica240]

I figured it out! I will respond with my new code tomorrow when I get back to work but I wanted you to know!