Loop through listbox - create unique reports for each (1 Viewer)

[malxvc]

I would like to design a loop of some sort to iterate through a listbox, print a report as a PDF, and save with a unique name.

Currently, I am clicking a name in a listbox and then clicking a button to run the code below.

The listbox selection passes the value to the textbox "txtEmployeeName", which sets the criteria for the query that populates the report "Sales_Incentive_Report"

Private Sub btnPrint_PDF_Click()
 
Dim strDefaultPrinter, stDocName As String
 
strDefaultPrinter = Application.Printer.DeviceName
stDocName = "Sales_Incentive_Report"
 
Set Application.Printer = Application.Printers("PDF Printer")
 
   DoCmd.OpenReport "Sales_Incentive_Report", acViewDesign, , , acHidden
   Reports!Sales_Incentive_Report.Caption = txtEmployeeName & "_Sales_Incentive_Report"
   DoCmd.Close acReport, "_Sales_Incentive_Report", acSaveYes
 
   DoCmd.OpenReport stDocName, acViewNormal
   DoCmd.Close acReport, "Sales_Incentive_Report"
 
End Sub

Everything in the code above works fine, I'd just rather not click a name and then a button 50+ times to execute this...

Thanks in advance for your help.

 

[pr2-eugin]

Hello malxvc.. What is the name of the ListBox? Is the listbox multi select? Now, based on your description, you seem to use the OnClick property of the ListBox to set the value of the TextBox with the selected name is that correct?

 

[malxvc]

ListBox: lstEmployeeNames
- not multi select

I am not using the OnClick property of the ListBox to set the value of the TextBox, instead, the TextBox's Control Source property is =[lstEmployeeNames]

 

[pr2-eugin]

Okay, So the simple option would be to allow multi select, so the user can select the 50+ names to be reported on.. Then use a FOR loop to iterate through the list, to send the reports to the printer.. This will also eliminate the need of the Text box.. The code being something along the lines of..

Private Sub btnPrint_PDF_Click()
    Dim I As Variant, strDefaultPrinter, stDocName As String
    If (Me.lstEmployeeNames.ItemsSelected.Count = 0) Then
        Call MsgBox("Please SELECT atleast one NAME from the List.", vbInformation, "Missing Name")
        Exit Sub
    End If
    
    strDefaultPrinter = Application.Printer.DeviceName
    stDocName = "Sales_Incentive_Report"
     
    Set Application.Printer = Application.Printers("PDF Printer")
    
    For Each I In Me.lstEmployeeNames.ItemsSelected
       DoCmd.OpenReport "Sales_Incentive_Report", acViewDesign, , , acHidden
       Reports!Sales_Incentive_Report.Caption = txtEmployeeName & "_Sales_Incentive_Report"
       DoCmd.Close acReport, "_Sales_Incentive_Report", acSaveYes
     
       DoCmd.OpenReport stDocName, acViewNormal
       DoCmd.Close acReport, "Sales_Incentive_Report"
    Next
End Sub

 

[malxvc]

The textbox was necessary to set the caption of the report, since the caption determined what name the PDF would be saved as.

The use of the multi select listbox negates the textbox entry, so the reports will have no unique identifier automatically assigned, unless the code were to read:
.Caption = Me.lstEmployeeName.ItemData(i) '--- ?

Other than the caption issue, this coding also runs into the same problem I've been facing when trying to code this myself - the criteria in the query that generates the report is based off of the aforementioned textbox, so ignoring that field altogether returns an empty report.

I guess the question then becomes, how do you set the criteria of a query that generates a report based off of a loop?

 

[mrfimleys]

Hi

I have the same requirement. Loop through a list of parameters to pass to a report and write each report to a PDF.

Did you get an answer to you query?

Thanks,

Nick