Trying to loop through List Box entries, then save PDF report based on each result...

[brosenfelt]

I have a query used as the source for a report. The query criteria is a List Box (List0). Trying to write code to loop through all of the entries of the List Box and save a separate report for each. Not getting errors -- but also getting a blank report!

Private Sub Command3_Click()

Dim i As Integer
Dim lbox As ListBox
Dim myPath As String
Dim strReportName As String

Set lbox = Me![List0]
myPath = "K:\CLE05\Business Resource Center\Opportunity Tracking\"
strReportName = lbox.ItemData(i)

For i = 0 To lbox.ListCount - 1
   'Access each item with
   'Me.ListBoxName.ItemData(i)
   DoCmd.OutputTo acOutputReport, "Pipeline - All Rms - AUTO-REPORT", acFormatPDF, myPath & strReportName & ".pdf", False, , , acExportQualityPrint
   
   'Debug.Print lbox.ItemData(i)
Next i

End Sub

[jimatqsi]

I don't know why your report is blank but you will get the same result for every item in the list. Nothing changes as you go through your list. I think you want to move line 10 down between line 14 and 15.

Jim

[brosenfelt]

Got the answer from a different forum -- but here is the answer:

Dim i As Integer
Dim lbox As ListBox
Dim myPath As String
Dim strReportName As String
Dim varItm As Variant
Dim str As String

str = ""
Set lbox = Me!List0
myPath = "C:\Users\ROSENBR\Documents\BRC\Report\"

If lbox.ItemsSelected.Count = 0 Then
MsgBox ("No items are currently selected.")
Exit Sub
End If

For Each varItm In lbox.ItemsSelected
        strReportName = lbox.Column(0, varItm)
            Debug.Print "chcking Loop logic with " & lbox.Column(0, varItm)
            Debug.Print "2nd debug:" & strReportName
            Debug.Print "[RM_FULL_NAME]= " & """" & lbox.Column(0, varItm) & """"
    DoCmd.OpenReport "Pipeline - All Rms - AUTO-REPORT", acViewPreview, , "[RM_FULL_NAME]=" & """" & strReportName & """", acWindowNormal
    DoCmd.OutputTo acOutputReport, "Pipeline - All Rms - AUTO-REPORT", acFormatPDF, myPath & strReportName & ".pdf", False, , , acExportQualityPrint
    DoCmd.Close acReport, "Pipeline - All Rms - AUTO-REPORT", acSaveNo
Next varItm

[NeoPa]

I'm very curious to know why you needed to go elsewhere when the correct answer was posted here two days ago?

[brosenfelt]

NeoPa:
I'm very curious to know why you needed to go elsewhere when the correct answer was posted here two days ago?

Actually -- that wasn't the correct answer for the problem I was trying to solve.

[NeoPa]

I'm happy to read your reasoning on this.

I read through the code and it seemed to me to be exactly what your code was suffering from. In the absence of your posting what happened when you tried it, I'm still of that opinion.

[jimatqsi]

And I notice that the corrected code did in fact show the line moved into your loop as I suggested it needed to be. So what I pointed out was at least part of your problem. But I note that there was also the problem of the blank report which was mentioned but not really explained.

I'm glad you got your problem resolved.