Cycle Through Parameters in VBA

[kestefon]

I have a report that is linked to a form's List Box. Users can select Product A, Product B, Product C, etc. on the form, and the report will populate with data for the corresponding product.



I'd like to export each version of the report to Excel automatically, and I'm trying to figure out how to use VBA to enter the parameters. Is there a way to cycle through each value in the listbox?

Here's my code so far.

Sub ExportReport()


Dim ctr As String
Dim BillingTemp As String
Dim qryBilled As DAO.QueryDef




Dim dbs As DAO.Database


Set dbs = CurrentDb
Set qryBilled = dbs.QueryDefs("MyQuery")
'This query is linked to the form with the listbox.


qryBilled.Parameters("Forms!Billing Workbook!listProducts").Value = "Product A"






ctr = Format(Now(), "yyyymmddhhnnss")
ExportPath = CheckExportPath() 




BillingTemp = ExportPath & "\BillingTemp_" & ctr & ".xls"

'Fails here. Prompts user to manually enter the parameter.
    DoCmd.OutputTo acOutputReport, "BilledPlanned", "Excel97-Excel2003Workbook(*.xls)", BillingTemp, False, "", , acExportQualityPrint
    
End Sub

[June7]

This is a multi-select listbox?

Example code:

    Dim varItem As Variant
    'Loop through the ItemsSelected in the list box.    
    With Me.lstCategory
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'do something with the item
                Debug.Print .ItemData(varItem)
            End If
        Next
    End With

[kestefon]

Thanks, that works!

[kestefon]

Actually, wait. I spoke too soon. My form is single-select, and the report that it feeds into must have just a single selection. (i.e. I can't have multiple products in the same report).

Here's the query that feeds into the report. Essentially, I'd like to programmatically change "Forms![Billing Workbook]!listProducts" and export the report repeatedly until every product in the list box has been covered.

SELECT [RawData].DataType, [RawData].Media, [RawData].Product, [RawData].RevenueFROM RawData
WHERE ((([RawData].Product)=Forms![Billing Workbook]!listProducts));

Another way to think of it is: Instead of manually going to the form and selecting its entries from a listbox, I'd like to use VBA to select the items.