Page 1 of 2 12 LastLast
Results 1 to 10 of 12

VBA to Loop through Combo_box?

This is a discussion on VBA to Loop through Combo_box? within the Microsoft Access forums, part of the Question Forums category; Greetings all, I inherited a databases from a co-worker. It has a form with a combo_box and button to generate ...

  1. #1
    New Member
    Join Date
    Oct 2016
    Posts
    11

    Default VBA to Loop through Combo_box?

    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

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,065

    Default Re: VBA to Loop through Combo_box?

    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.
    Tips for posting problems:
    1) "doesn't work" is of no help. Post error message numbers and text, if known.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  3. #3
    New Member
    Join Date
    Oct 2016
    Posts
    11

    Default Re: VBA to Loop through Combo_box?

    Quote Originally Posted by Micron View Post
    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:
    Dim i AsLong
      with myCombo
        For i =0 To.ListCount
            Debug.Print .ItemData(i)
        Next i
    endwith
    
    

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,065

    Default Re: VBA to Loop through Combo_box?

    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.

  5. #5
    New Member
    Join Date
    Oct 2016
    Posts
    11

    Default Re: VBA to Loop through Combo_box?

    Quote Originally Posted by Micron View Post
    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?

  6. #6
    New Member
    Join Date
    Oct 2016
    Posts
    11

    Default Re: VBA to Loop through Combo_box?

    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.

  7. #7
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,065

    Default Re: VBA to Loop through Combo_box?

    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 & "'"

  8. #8
    New Member
    Join Date
    Oct 2016
    Posts
    11

    Default Re: VBA to Loop through Combo_box?

    Quote Originally Posted by Micron View Post
    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

  9. #9
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,065

    Default Re: VBA to Loop through Combo_box?

    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
    Tips for posting problems:
    1) "doesn't work" is of no help. Post error message numbers and text, if known.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  10. #10
    New Member
    Join Date
    Oct 2016
    Posts
    11

    Default Re: VBA to Loop through Combo_box?

    Quote Originally Posted by Micron View Post
    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?

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com