Need help with combo box loop/open report

[ERP1201]

I currently have a form that contains a combobox with all the employee names. The combo box calls to a table that contains all of the employee records and generates a report based on the selected employee

I have two buttons, export individual report which is done by selecting an employee from the combo box and once the button is clicked exports the report to PDF to the path written

The second button exports all reports as individual reports to the path written (since there are hundreds of employees it would take too much time to export each individually). The PDF is saved by lastname_firstn ame (column2 and column 1) of the combo box.

Now the second button labels the PDFs correctly as it loops through the combo box, but each time the report is opened the code does not register which employee is being selected and does not update the report with the correct employee records. All of the reports are saved as blanks.

I added the code below, can someone please help figure out a way for when the Report opens it recognizes which employee it is on in the combobox and updates before opening!

Thank you

Private Sub cmdExportAllToPDF_Click()


Dim intCount As Integer
Dim cmbCount As Integer


For intCount = 0 To cmbReportEmployee.ListCount - 1

    DoCmd.OpenReport "Letter Report", acViewPreview, , , acWindowNormal
    DoCmd.OutputTo acOutputReport, "Letter Report", "PDFFormat(*.pdf)", "C:\Users\username\Documents\Stock Letter Reports\" + Forms!ReportOpener!cmbReportEmployee.Column(1, intCount) & "_" & Forms!ReportOpener!cmbReportEmployee.Column(2, intCount) & ".pdf", False, "", , acExportQualityPrint
    DoCmd.Close acReport, "Letter Report", acSaveNo

    
Next



End Sub

[PhilOfWalton]

I suspect that your problem is that your OpenReport has no filter with it.

Try a Where clause or a Filter to open the report for the correct Employee. Based on your
Forms!ReportOpe ner!cmbReportEm ployee.Column(1 , intCount & Forms!ReportOpe ner!cmbReportEm ployee.Column(2 , intCount)...
Phil

[ERP1201]

PhilOfWalton,

Yes, that is what I am trying to figure out. Should I call to the table that contains the records and include the column and inCount of the combobox to match to the records?

[PhilOfWalton]

OK. First I suspect that what you are calling a Combo box is in fact a multi select list box (List0).
Columns are EmpSurname, EmpFirstName & EmpID

Try this code as a basis

Private Sub Command2_Click()

    Dim itm As Variant
    Dim Fltr As String

    For Each itm In Me.List0.ItemsSelected
        Fltr = "EmpID = " & List0.Column(2, itm)
        DoCmd.OpenReport "Letter Report", acViewPreview, , Fltr
        Debug.Print Reports!Letter Report.EmpSurName & "  " & Reports!Letter Report.EmpFirstName
        DoCmd.OutputTo acOutputReport, "Letter Report", "PDFFormat(*.pdf)", _
        "C:\Users\username\Documents\Stock Letter Reports\" & Reports!Letter Report.EmpSurName & "_" & Reports!LetterReport.EmpFirstName & ".pdf", False, "", , acExportQualityPrint
        DoCmd.Close acReport, "Letter Report"
    Next itm

End Sub

Phil

[ERP1201]

Ok thanks. I'll try this out. I just checked my form and it is a combo box not a multi select list box.

[PhilOfWalton]

I had the impression that you wanted to select say half a dozen employees out of all your employees and create a PDF for those selected employees.

If you're doing it from a Combo box, you can only do one at a time. Using a list box allows you to do a "Batch (could be only 1)

Phil

[ERP1201]

Oh sorry no. I was trying to loop through all the employees and create individual PDF for each, and you're saying that is not possible with a combo box?

[ERP1201]

Also, I forgot to mention my 'Letter Report' has two subreports which displays two tables of the employee records(which updates when the employee name is selected from the combo box)

[PhilOfWalton]

If you want to print a report for all Employees, what is the significance of the Combo Box?

This code should print out all Employees

Private Sub Command5_Click()

    Dim MyDb As Database
    Dim EmployeeSet As Recordset
    Dim Fltr As String
    
    Set MyDb = CurrentDb
    Set EmployeeSet = MyDb.OpenRecordset("SELECT Employee.* FROM Employee ORDER BY EmpLastName")
    
    With EmployeeSet
        Do Until .EOF
            Fltr = "EmployeeID = " & !EmployeeID
            DoCmd.OpenReport "Letter Report", acViewPreview, , Fltr
            Debug.Print Reports!Letter Report.EmpSurName & "  " & Reports!Letter Report.EmpFirstName
            DoCmd.OutputTo acOutputReport, "Letter Report", "PDFFormat(*.pdf)", _
            "C:\Users\username\Documents\Stock Letter Reports\" & Reports!Letter Report.EmpSurName & "_" & Reports!LetterReport.EmpFirstName _
            & ".pdf", False, "", , acExportQualityPrint
            DoCmd.Close acReport, "Letter Report"
            .MoveNext
        Loop
        .Close
        Set EmployeeSet = Nothing
    End With
    
End Sub

Phil

[ERP1201]

The significance of the combo box was to save/print each employee individual but to loop through rather than having to save/print each report manually.


I was working on the original code you provided that used a multi select list box. I created a list box with the same fields as the combo box.

However, the report is still not updating with the records based on the employee selected. It may have to do with the fact that "Letter Report" has two subreports which should be updated with the employees records based on selection.

[PhilOfWalton]

Try removing the subreports as a test

Phil

[ERP1201]

I deleted the subreports and got a run time error '2491'
The actions or methis is invalid because the form or report isn't bound to a table or query.



I tried your original way again with the list box and now I am getting an error with my Debug.Print line (I added the subreports back)

Private Sub cmdExportAllToPDF_Click()


Dim itm As Variant
Dim Fltr As String



For Each itm In Me.lstReportEmployee.ItemsSelected

    Fltr = "Employee ID" & lstReportEmployee.Column(0, itm)
    
    DoCmd.OpenReport "Letter Report", acViewPreview, , Fltr
    Debug.Print Reports![Letter Report].[Last Name] & " " & Report![Letter Report].[First Name]
    DoCmd.OutputTo acOutputReport, "Letter Report", "PDFFormat(*.pdf)", "C:\Users\username\Documents\Stock Letter Reports\" & Reports![Letter Report].[Last Name] & "_" & Reports![Letter Report].[First Name] & ".pdf", False, "", , acExportQualityPrint
    DoCmd.Close acReport, "Letter Report"


Next itm


End Sub

[PhilOfWalton]

What is the RecordSource of your main Report?

[ERP1201]

The main report doesn't have a record source. It is mainly a place holder/template for the two subreports.


The main report has 3 paragraphs of text for the formal letter followed by the two subreports which should display the employee records

[PhilOfWalton]

Try setting the RecordSource to a query based on Employees. I would have expected the subreports to be linked to the main report (to keep them syncronised)
Phil