Results 1 to 7 of 7
  1. #1
    derfel is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    3

    Unhappy Filter Report by Multi-select Combo Box Criteria

    I have a form that contains three multi-select combo boxes, called Market, Readiness, and Role. I am trying to use the user-selected criteria from these boxes as parameters for my query, which is linked to a personnel report.

    I have been attempting (without success) to modify code to gather all the selections in each box and then open the updated report. The code below uses the combo box called "Market" and a report called "Succession".

    Private Sub Run_Report_Click()
    Dim StrWhere As String
    Dim varItem As Variant

    If Me.Market.ItemsSelected.Count > 0 Then

    For Each varItem In Me.Market.ItemsSelected
    StrWhere = StrWhere & Me.Market.ItemData(varItem) & ", "
    Next

    StrWhere = Left(StrWhere, Len(StrWhere) - 2)
    StrWhere = "[Market] In (" & StrWhere & ")"
    End If

    DoCmd.OpenReport "Succession", acViewPreview, , StrWhere

    End Sub

    Questions:
    1. This code is missing something, since when I click my event button to run it, a box pops up to ask for the parameter I already gave in the combo box.
    2. What is the syntax to add in the other combo box selections?

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Multi-select combos are NOT the way to go for criteria. Multi-select combos are different creatures than normal combos and I don't even know how you would have a multi-select combo without it being bound to a table (using a multi-value field).

    Use a listbox.

    If you aren't going to use a listbox, then you will have to check the table the multi-select combo is bound to in order to query those values as a multi-select combo does not have an ItemsSelected property that will work with multi-valued fields.

  3. #3
    derfel is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    3
    My first post was in error- I am using list boxes rather than combo boxes.

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    I'll post a bit of code I already did up for someone else. But just note that this person had the word _ALL unioned in to the Listbox so we set it up to ignore that listbox if _ALL was chosen.:

    Code:
        Dim strWhere As String
        Dim strITLead As String
        Dim strProgram As String
        Dim strProjDriv As String
        Dim strSponsoringBanks As String
     
        ' Build criteria string for GBT IT Lead
        ' if All is selected then we do not need the variable
        If Me.lstITLead.ItemData(0) <> " All" Then
            For Each varItem In Me.lstITLead.ItemsSelected
                strITLead = strITLead & Chr(34) & Me.lstITLead.ItemData(varItem) & Chr(34) & ","
            End If
        Next varItem
     
        strITLead = Left(strITLead, Len(strITLead) - 1)
        strWhere = "[GBT IT LEAD] IN(" & strITLead & ") AND "
    End If
     
    ' Build criteria string for Program
    ' if ALL is selected then no need to use any variable
    If Me.lstProgram.ItemData(0) = " All" Then
        For Each varItem In Me.lstProgram.ItemsSelected
            strProgram = strProgram & Chr(34) & Me.lstProgram.ItemData(varItem) & Chr(34) & ","
        End If
    Next varItem
     
    strProgram = Left(strProgram, Len(strProgram) - 1)
    strWhere = strWhere & "[Program] IN(" & strProgram & ") AND "
    End If
     
    ' Build criteria string for Project name
    If Me.lstProject.ItemData(0) = " All" Then
        For Each varItem In Me.lstProject.ItemsSelected
            strProject = strProject & Chr(34) & Me.lstProject.ItemData(varItem) & Chr(34) & ","
        End If
    Next varItem
     
    strProject = Left(strProject, Len(strProject) - 1)
    strWhere = strWhere & "[Project] IN(" & strProject & ") AND "
    End If
     
     
    ' Build criteria string for Project Driver
    If Me.lstProDriver.ItemData(0) = " All" Then
        For Each varItem In Me.lstProDriver.ItemsSelected
            strProjDriv = strProjDriv & Chr(34) & Me.lstProDriver.ItemData(varItem) & Chr(34) & ","
        End If
    Next varItem
     
    strProjDriv = Left(strProjDriv, Len(strProjDriv) - 1)
    strWhere = strWhere & "[Project Driver] IN(" & strProjDriv & ") AND "
    End If
     
    ' Build criteria string for SponsoringBanks
    If Me.lstSpoBk.ItemData(0) = " All" Then
        For Each varItem In Me.lstSpoBk.ItemsSelected
            strSponsoringBanks = strSponsoringBanks & Chr(34) & Me.lstSpoBk.ItemData(varItem) & Chr(34) & ","
        Next varItem
     
        strSponsoringBanks = Left(strSponsoringBanks, Len(strSponsoringBanks) - 1)
        strWhere = strWhere & "[Sponsoring Banks] IN(" & strSponsoringBanks & ")"
    End If
     
    'strip the AND off (we put one on every one of them so as to not have to test for it first)
    strWhere = "WHERE " & Left(strWhere, Len(strWhere) - 5)
     
    ' Build SQL statement
    strSQL = "SELECT [GBT Project Dashboard].* FROM [GBT Project Dashboard] " & strWhere

  5. #5
    derfel is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    3
    Brilliant. I followed your syntax on this and have my form up and running nicely. Thanks Bob! Enjoy your Thanksgiving.

  6. #6
    Dre is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    1
    Quote Originally Posted by derfel View Post
    Brilliant. I followed your syntax on this and have my form up and running nicely. Thanks Bob! Enjoy your Thanksgiving.
    Hi derfel, how did you get this to work? I need some assistance please.

  7. #7
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Quote Originally Posted by Dre View Post
    Hi derfel, how did you get this to work? I need some assistance please.
    Your post is very vague. derfel followed my code example and it worked for them. So that would be the response. Follow my code example and you should be good. But if you want a simplified version, check out this by pbaldy:

    http://www.baldyweb.com/multiselect.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-08-2011, 11:15 PM
  2. Multi-Select List Box as Criteria in Query
    By broadwat in forum Queries
    Replies: 6
    Last Post: 09-19-2011, 07:47 AM
  3. Multi Select Combo box for print function
    By jparker1954 in forum Reports
    Replies: 11
    Last Post: 09-17-2011, 03:08 PM
  4. Passing Multi Select string to Query criteria
    By oleBucky in forum Queries
    Replies: 4
    Last Post: 05-15-2011, 02:11 PM
  5. Replies: 1
    Last Post: 03-01-2009, 09:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums