VBA to Loop through Combo_box?

Endlessknight

New Member
Joined
Oct 28, 2016
Messages
36
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.


Code:
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Something strange there. A combo is for selecting one value from a list. If you're simply going to use the entire list, might as well just use the source that the combo list comes from and iterate through that. I suppose you could use the .Row property of a combo box rowsource but I'd guess that you have to know how many values are in the list to avoid trying to move beyond the last one. Simpler to use a query (if not the query for the combo) that provides these values, create a recordset of those values and loop through them, outputting your report. For that part, I'd use a sub and pass the report name to it and do the output rather than repeat the lines of code many times for the output part.
 
Upvote 0
Something strange there. A combo is for selecting one value from a list. If you're simply going to use the entire list, might as well just use the source that the combo list comes from and iterate through that. I suppose you could use the .Row property of a combo box rowsource but I'd guess that you have to know how many values are in the list to avoid trying to move beyond the last one. Simpler to use a query (if not the query for the combo) that provides these values, create a recordset of those values and loop through them, outputting your report. For that part, I'd use a sub and pass the report name to it and do the output rather than repeat the lines of code many times for the output part.


Hi Micron, thanks for the input.

Yeah I know it's strange and far from ideal way of doing things but I inherited the database from a coworker and don't want to change the existing db, only add on to it.

The way it's currently set up is that the value selected in the combo_box is used as the input criteria for a query and the report is then generated from that query. Not sure how to rework it as you suggested without rebuilding the report from the ground up.

Found this pseudo code while searching online and now trying to re-purpose it.

Code:
[COLOR=#242729][FONT=Arial]<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]Dim[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Long[/COLOR][COLOR=#303336]
  [/COLOR][COLOR=#101094]with[/COLOR][COLOR=#303336] myCombo
    [/COLOR][COLOR=#101094]For[/COLOR][COLOR=#303336] i [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]0 [/COLOR][COLOR=#101094]To[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ListCount
        Debug[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Print [/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ItemData[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]i[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Next[/COLOR][COLOR=#303336] i
[/COLOR][COLOR=#101094]end[/COLOR][COLOR=#101094]with[/COLOR]</code><code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">[COLOR=#101094]
[/COLOR]</code>
[/FONT][/COLOR]
 
Upvote 0
A variation of that should work. I tried to write it for you but I have no idea what the 2's and 3's are about. Not enough of your code was posted, just a function with parts I don't understand. If you can do it yourself, try substituting .row(i) if the combo only has one column and loop through. I can't recall if the row property is 0 or 1 based, so the counter might have to start with 1. The other way I was referring to would not require you to alter any report.
 
Upvote 0
A variation of that should work. I tried to write it for you but I have no idea what the 2's and 3's are about. Not enough of your code was posted, just a function with parts I don't understand. If you can do it yourself, try substituting .row(i) if the combo only has one column and loop through. I can't recall if the row property is 0 or 1 based, so the counter might have to start with 1. The other way I was referring to would not require you to alter any report.

Still having trouble with my syntax. How would your way work?
 
Upvote 0
This my code as it currently stands.

Code:
Function Expo_allPDF()


Dim MyPath As String
Dim i As Long
Dim ListControl As Control


Set ListControl = [Forms]![ChgsMstrCommands]![Combo_SelectRVURenderingForRpts]


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




End Function

The Dlookup function doesn't like a control as a criteria. Not sure how to fix or work around this issue. Going to try and add a new varible to convert the control to a string.

Any help would be great appreciated. Thanks.
 
Upvote 0
You can't include a control reference within the literal quotes for any criteria string. You have to concatenate:
"[Rendering] = " & Me.ListControl

If the control contains text values:
"[Rendering] = '" & Me.ListControl & "'"
 
Upvote 0
You can't include a control reference within the literal quotes for any criteria string. You have to concatenate:
"[Rendering] = " & Me.ListControl

If the control contains text values:
"[Rendering] = '" & Me.ListControl & "'"


Thanks!

I did get this to work.


Code:
Function Expo_allPDF()


Dim MyPath As String
Dim i As Long
Dim ListControl As Control
Dim StartTime As Double
Dim SecondsElapsed As Double


StartTime = Timer


Set ListControl = [Forms]![ChgsMstrCommands]![Combo_SelectRVURenderingForRpts]


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


  
MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation




End Function
 
Upvote 0
Glad you got it to work. Consider
- if you don't have Option Explict in every module, you're asking for trouble (I see no declaration for itm)
- don't see the need for ListControl = itm - you don't seem to use it
- you could refer to the object (ListControl) or itm, whichever is appropriate:

Code:
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
 
Upvote 0
Glad you got it to work. Consider
- if you don't have Option Explict in every module, you're asking for trouble (I see no declaration for itm)
- don't see the need for ListControl = itm - you don't seem to use it
- you could refer to the object (ListControl) or itm, whichever is appropriate:

Code:
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

Thanks for the input.

Should I declare itm as a string or variant?
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top