VBA to Loop through Combo_box?

[Endlessknight]

Greetings all,

I inherited a databases from a co-worker. It has a form with a combo_box and button to generate a report based on the item selected via the combo_box.

I wrote a function that, see below, that opens the report, looks up the location to save it in a table, saves it a then closes the report.

How can I get the function to loop through all the items in the combo_box without me having to manually select them?

Thanks in advance for any input, it's greatly appreciated.

Function Expo_allPDF()


Dim MyPath As String


MyPath = DLookup("Field2", "Table", "Field1 = '" & [Forms]![ChgsMstrCommands]![Combo_SelectRVURenderingForRpts] & "'")


DoCmd.OpenReport "Reportname", 2
DoCmd.OutputTo 3, "Reportname", acFormatPDF, MyPath, True
DoCmd.Close 3, "Reportname"


End Function

 

[Micron]

I know that item data for a list box must be a variant (99.7% sure of that). You're using a combo? I'd go with variant since logic would dictate the designers would follow a pattern. Plus, it would work with strings, numbers, empty strings or Nulls. You could try it both ways just for fun (at least I would) but I would use variant regardless for the reasons given.

 

[xenou]

For i = 0 To ListControl.ListCount - 1
itm = ListControl.ItemData(i)
MyPath = DLookup("Location", "Provider RVU & ATO Reports", "[Rendering] = '" & itm & "'")
DoCmd.OpenReport "ProviderRVUHandoutSingle", 2
DoCmd.OutputTo 3, "ProviderRVUHandoutSingle", acFormatPDF, MyPath, False
DoCmd.Close 3, "ProviderRVUHandoutSingle"
Next

Without the itm variable is simple enough:

For i = 0 To ListControl.ListCount - 1
  MyPath = DLookup("Location", "Provider RVU & ATO Reports", "[Rendering] = '" & ListControl.ItemData(i)  & "'")
  DoCmd.OpenReport "ProviderRVUHandoutSingle", 2
  DoCmd.OutputTo 3, "ProviderRVUHandoutSingle", acFormatPDF, MyPath, False
  DoCmd.Close 3, "ProviderRVUHandoutSingle"
Next