locked
Selecting multiple values in a combo box RRS feed

  • Question

  • I have a form with a combo box. This combo box value is a criteria in a query. I want to be able to select more than one value in the combo box. Please advise
    Tuesday, August 3, 2010 10:52 AM

Answers

  • I tried using list box instead setting the multiselect property to both simple and extended. The query does not work. This is the SQL of the query that I am using:

    SELECT [Error Log].[Project Type], Sum([Error Log].[Total # of Errors]) AS [SumOfTotal # of Errors]
    FROM [Error Log]
    GROUP BY [Error Log].[Project Type]
    HAVING ((([Error Log].[Project Type])=[Forms]![Input Form1]![Project Type]));

    Please note that Project Type is the list box in the form that I am trying to pass the criteria to the above query.

    Okay, as I mentioned, you're going to need code to read all the values from the multi-select listbox.  Code needs to go somewhere, so I'm going to assume you have a button on the form called cmdRunQuery.  In the OnClick event of the button, you'd need code something like this:

    Private Sub cmdRunQuery_Click()
    On Error GoTo Err_cmdRunQuery_Click
       
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String, strWhere As String
    Dim i As Integer

    Set db = CurrentDb

    '*** create the query based on the information on the form
    strSQL = "SELECT [Project Type], Sum([Total # of Errors]) AS [SumOfTotal # of Errors]" & _
                  " FROM [Error Log]" & _
                  " GROUP BY [Project Type]"
    strHaving = "HAVING [Project Type] IN( "
    For i = 0 To lstProjectType.ListCount - 1
        If lstProjectType.Selected(i) Then
            strHaving = strHaving & "'" & lstProjectType.Column(0, i) & "', "
        End If
    Next i
    strWhere = Left(strHaving , Len(strHaving ) - 2) & ");"
    strSQL = strSQL & strHaving
    MsgBox strSQL
    '*** delete the previous query
    db.QueryDefs.Delete "qryMyQuery"
    Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

    '*** open the query
    DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

    Exit_cmdRunQuery_Click:
        Exit Sub

    Err_cmdRunQuery_Click:
        If Err.Number = 3265 Then   '*** if the error is the query is missing
            Resume Next             '*** then skip the delete line and resume on the next line
        Else
            MsgBox Err.Description      '*** write out the error and exit the sub
            Resume Exit_cmdRunQuery_Click
        End If
    End Sub

    --------------------

    The Msgbox line will just show you what SQL statement is being created.  When you've got it working right, you can delete it or comment it out.  Also, you'll want to replace "qryMyQuery" with your actual stored query.

    On my website is a small sample database called CreateQueries2.mdb, which illustrates this.  See Form 6.


    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    • Marked as answer by Bessie Zhao Tuesday, August 10, 2010 10:13 AM
    Wednesday, August 4, 2010 1:27 PM

All replies

  • Try to use ListBox and MultiSelect property = 1 (Simple) or 2 (Extended).
    Tuesday, August 3, 2010 11:34 AM
  • Neeraj88 wrote:

    I have a form with a combo box. This combo box value is a criteria in a
    query. I want to be able to select more than one value in the combo box.

    You cannot select multiple values in a combo box. Use a list box instead.
    You will find the Multi Select property in Properties, tab Other.


    Peter Doering [MVP Access]

    Tuesday, August 3, 2010 12:45 PM
  • As others have observed, you can't use a combo box for this, you must use a listbox.  Unfortunately, when you set the Multi-Select property of a listbox to Simple or Extended, the listbox is no longer has a Value.  In other words, you can't use the value of the listbox in a query.

    The only way to use a multi-select listbox in a query is to use code to build a value list from the Selected list items for the WHERE clause of the query, which is a bit more complicated. 

    If you're interested in pursuing it, we'd have to see the SQL of your query as well as the table and field names.


    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    Tuesday, August 3, 2010 2:42 PM
  • I tried using list box instead setting the multiselect property to both simple and extended. The query does not work. This is the SQL of the query that I am using:

    SELECT [Error Log].[Project Type], Sum([Error Log].[Total # of Errors]) AS [SumOfTotal # of Errors]
    FROM [Error Log]
    GROUP BY [Error Log].[Project Type]
    HAVING ((([Error Log].[Project Type])=[Forms]![Input Form1]![Project Type]));

    Please note that Project Type is the list box in the form that I am trying to pass the criteria to the above query.

     

    Wednesday, August 4, 2010 12:26 PM
  • I tried using list box instead setting the multiselect property to both simple and extended. The query does not work. This is the SQL of the query that I am using:

    SELECT [Error Log].[Project Type], Sum([Error Log].[Total # of Errors]) AS [SumOfTotal # of Errors]
    FROM [Error Log]
    GROUP BY [Error Log].[Project Type]
    HAVING ((([Error Log].[Project Type])=[Forms]![Input Form1]![Project Type]));

    Please note that Project Type is the list box in the form that I am trying to pass the criteria to the above query.

    Okay, as I mentioned, you're going to need code to read all the values from the multi-select listbox.  Code needs to go somewhere, so I'm going to assume you have a button on the form called cmdRunQuery.  In the OnClick event of the button, you'd need code something like this:

    Private Sub cmdRunQuery_Click()
    On Error GoTo Err_cmdRunQuery_Click
       
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String, strWhere As String
    Dim i As Integer

    Set db = CurrentDb

    '*** create the query based on the information on the form
    strSQL = "SELECT [Project Type], Sum([Total # of Errors]) AS [SumOfTotal # of Errors]" & _
                  " FROM [Error Log]" & _
                  " GROUP BY [Project Type]"
    strHaving = "HAVING [Project Type] IN( "
    For i = 0 To lstProjectType.ListCount - 1
        If lstProjectType.Selected(i) Then
            strHaving = strHaving & "'" & lstProjectType.Column(0, i) & "', "
        End If
    Next i
    strWhere = Left(strHaving , Len(strHaving ) - 2) & ");"
    strSQL = strSQL & strHaving
    MsgBox strSQL
    '*** delete the previous query
    db.QueryDefs.Delete "qryMyQuery"
    Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

    '*** open the query
    DoCmd.OpenQuery "qryMyQuery", acNormal, acEdit

    Exit_cmdRunQuery_Click:
        Exit Sub

    Err_cmdRunQuery_Click:
        If Err.Number = 3265 Then   '*** if the error is the query is missing
            Resume Next             '*** then skip the delete line and resume on the next line
        Else
            MsgBox Err.Description      '*** write out the error and exit the sub
            Resume Exit_cmdRunQuery_Click
        End If
    End Sub

    --------------------

    The Msgbox line will just show you what SQL statement is being created.  When you've got it working right, you can delete it or comment it out.  Also, you'll want to replace "qryMyQuery" with your actual stored query.

    On my website is a small sample database called CreateQueries2.mdb, which illustrates this.  See Form 6.


    -- Roger Carlson
    MS Access MVP 2006-2010
    www.rogersaccesslibrary.com
    • Marked as answer by Bessie Zhao Tuesday, August 10, 2010 10:13 AM
    Wednesday, August 4, 2010 1:27 PM
  • This worked like a charm, thanks! I did notice one error in the code, however.

    strSQL = strSQL & strHaving  should be

    strSQL = strSQL & strWhere

    Thursday, June 28, 2018 2:08 PM
  • You might also be interested in another approach which does allow you to reference a control in a form as a parameter.  The parameter in this case is not the list box itself, but a hidden text box into which the selected values are written as a value list by looping through the list box's ItemsSelected collection.

    Normally when restricting a query by a value list the IN operator would be used, but this does not accept a parameter as its argument, only a literal string expression.  However, Microsoft have published two functions, InParam and GetToken, (the former calls the latter) which simulate the IN operator.  The InParam function is called in the query, passing the parameter and the name of the relevant column as its two arguments.

    You'll find this illustrated in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the section 'retrieving data from the database' incudes an unbound form in which multiple contacts can be selected.  The value list is then built with code in the Click event procedure of a button on the form.  This then opens a report based on the query.

    Ken Sheridan, Stafford, England

    Thursday, June 28, 2018 4:43 PM