Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2013
    Posts
    12

    Unanswered: VB code for 2 multi-select list boxes

    I created 2 multi-select list boxes(list1,list2) to be used as parameters for a query(query1). and I can't figure out the correct vb code to use once the okcmd is selected (I am new to vb code) Any help is greatly appreciated!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by accessuser45 View Post
    I can't figure out the correct vb code to use once the okcmd is selected
    The correct vb code for performing what?
    Have a nice day!

  3. #3
    Join Date
    Feb 2013
    Posts
    12
    I want to run query1 based on the multiple selections made in both listboxes
    Last edited by accessuser45; 02-11-13 at 14:40.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you use multi-selected listbox controls, you need to retrieve each selected values from each ListBox then use the retrieved values to dynamically build the SQL expression of the query.

    1. List1 contains a selection that must have a match in ColumnX of Table1 and List2 contains a selection that must have a match in ColumnY of Table1.

    2. Here is a VBA procedure that extracts the selected values from both ListBox control in 2 criteria (strList1 from List1 and strList2 from List2, then append a WHERE clause made from both criteria to a SELECT query stored in c_SQL. Finally, the SQL property of the query Query1 (which must exist) received the assembled SQL expression.
    Code:
    Private Sub okcmd_Click()
    
        Const c_SQL As String = "SELECT * FROM Table1"
        
        Dim qdf As DAO.QueryDef
        Dim ctl As Control
        Dim strList1 As String
        Dim strList2 As String
        Dim strSQL As String
        Dim varItem As Variant
        Dim i As Integer
    
        ' Assemble the criteria from List1.
        '
        Set ctl = Me.List1
        For Each varItem In ctl.ItemsSelected
            If Len(strList1) > 0 Then strList1 = strList1 & ","
            
            ' If the selected values are Text values:
            '
            strList1 = strList1 & "'" & ctl.ItemData(varItem) & "'"
            '
            ' For Numeric values, use:
            '
            ' strList1 = strList1 & ctl.ItemData(varItem)
            '
        Next varItem
        
        ' Assemble the criteria from List2.
        '
        Set ctl = Me.List2
        For Each varItem In ctl.ItemsSelected
            If Len(strList2) > 0 Then strList2 = strList2 & ","
            
            ' If the selected values are Text values:
            '
            strList2 = strList2 & "'" & ctl.ItemData(varItem) & "'"
            '
            ' For Numeric values, use:
            '
            ' strList2 = strList2 & ctl.ItemData(varItem)
            '
        Next varItem
        
        ' Complete and combine both assembled criteria.
        '
        If Len(strList1) > 0 Then strSQL = "ColumnX IN ( " & strList1 & " )"
        If Len(strList2) > 0 Then
            If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
            strSQL = strSQL & "ColumnY IN ( " & strList2 & " )"
        End If
        
        ' Finally merge with the constant containing the SELECT part of the query.
        '
        If Len(strSQL) > 0 Then strSQL = " WHERE " & strSQL
        strSQL = c_SQL & strSQL
        
        ' And modify the SQL expression of Query1 accordingly.
        '
        Set qdf = CurrentDb.QueryDefs("Query1")
        qdf.SQL = strSQL
        Set qdf = Nothing
        
        ' You may now do whatever you want with Query1: Open it, open a form or a Report
        ' using it as the RecordSource, etc.
        '
        
    End Sub
    Have a nice day!

  5. #5
    Join Date
    Feb 2013
    Posts
    12
    Thank you!!
    I added your code and I am getting a Run Time Error 3075 Syntax Error (missing operator). I absolutely cant figure out why. The table the Query is based off of is called Healthcare Projects. My listboxes are both unbound on one form. The Query is called Query 1 (until I can think of another name) I will eventually create a report based off of the query but one step at a time.

    Here is my code:

    Private Sub Command10_Click()
    Const c_SQL As String = "Select * From Healthcare projects"

    Dim qdf As DAO.QueryDef
    Dim ctl As Control
    Dim strnatureofworklist As String
    Dim strusetypelist As String
    Dim strSQL As String
    Dim varItem As Variant
    Dim i As Integer

    Set ctl = Me.natureofworklist
    For Each varItem In ctl.ItemsSelected
    If Len(strnatureofworklist) > 0 Then strnatureofworklist = strnatureofworklist & ","
    strnatureofworklist = strnatureofworklist & "'" & ctl.ItemData(varItem) & "'"
    Next varItem

    Set ctl = Me.usetypelist
    For Each varItem In ctl.ItemsSelected
    If Len(strusetypelist) > 0 Then strusetypelist = strusetypelist & ","
    strusetypelist = strusetypelist & "'" & ctl.ItemData(varItem) & "'"
    Next varItem

    If Len(strnatureofworklist) > 0 Then strSQL = "Natureofwork IN ( " & strnatureofworklist & " )"
    If Len(strusetypelist) > 0 Then
    If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
    strSQL = strSQL & "usetype IN ( " & strusetypelist & " )"
    End If

    If Len(strSQL) > 0 Then strSQL = " WHERE " & strSQL
    strSQL = c_SQL & strSQL

    Set qdf = CurrentDb.QueryDefs("query1")
    qdf.SQL = strSQL
    Set qdf = Nothing

    End Sub

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What does the SQL expression contains when it is assigned to the query? To know it, open the immediate window (CTRL+G) and try:
    Code:
    If Len(strSQL) > 0 Then strSQL = " WHERE " & strSQL
    strSQL = c_SQL & strSQL
    Debug.Print strSQL
    Stop 
    Set qdf = CurrentDb.QueryDefs("query1")
    Have a nice day!

  7. #7
    Join Date
    Feb 2013
    Posts
    12
    UGH! It now gives me all records no matter what criteria I enter.
    Here is an example of running my query 3 times with selected criteria. (I have the query run after the criteria is selected and the user clicks OK) What am I doing wrong?

    Select * From Healthcare projects WHERE Natureofwork IN ( 'Vertical Expansion' ) AND usetype IN ( 'Proton' )Select * From Healthcare projects WHERE Natureofwork IN ( 'Demolition' ) AND usetype IN ( 'Children's' )
    Select * From Healthcare projects WHERE Natureofwork IN ( 'Exterior Renovation' ) AND usetype IN ( 'ASC' )

    Select * From Healthcare projects WHERE Natureofwork IN ( 'Vertical Expansion' ) AND usetype IN ( 'Proton' )

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First, it should be:
    Code:
    Select * From [Healthcare projects] WHERE Natureofwork ...
    If you use spaces or other non alphanumeric characters in the name of a database object (which you should try to avoid), you must enclose the said name in square brackets when you use it in a query.

    Second, did you try to open the query in SQL view after changing the SQL expression and check what's in it? Does it match the SQL expression you built in the VBA procedure?
    Have a nice day!

  9. #9
    Join Date
    Feb 2013
    Posts
    12
    I'm sure that's the problem. The SQL expression does not match the expression in the code. How do I write the SQL Statement from the Query into the VBA procedure? and where do I put it? (Im very new to VBA)
    Thank you, your help has been invaluable!

    My code in the query is:
    SELECT [Healthcare Projects].[Project Number], [Healthcare Projects].[Client Company Name], [Healthcare Projects].NatureofWork, [Healthcare Projects].UseType, [Healthcare Projects].[Client City], [Healthcare Projects].[Client State], [Healthcare Projects].txt1, [Healthcare Projects].txt2, [Healthcare Projects].txt3, [Healthcare Projects].txt4, [Healthcare Projects].Picture
    FROM [Healthcare Projects];

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    From your last post, the declaration of the constant c_SQL should be:
    Code:
        Const c_SQL As String = "SELECT [Healthcare Projects].[Project Number], [Healthcare Projects].[Client Company Name], " & _
                                "[Healthcare Projects].NatureofWork, [Healthcare Projects].UseType, [Healthcare Projects].[Client City], " & _
                                "[Healthcare Projects].[Client State], [Healthcare Projects].txt1, [Healthcare Projects].txt2, " & _
                                "[Healthcare Projects].txt3, [Healthcare Projects].txt4, [Healthcare Projects].Picture " & _
                                "FROM [Healthcare Projects]"
    The section of the VBA procedure that changes the SQL expression of the query is:
    Code:
    Set qdf = CurrentDb.QueryDefs("query1")
    qdf.SQL = strSQL
    Set qdf = Nothing
    However, query1 was the name of the query is your first post. If the actual query has another name, replace query1 with that name.

    Notice that the SQL expression of the query will be changed every time the procedure is executed. It could be a good idea to keep a backup copy of the original query, just in case something goes wrong.
    Have a nice day!

  11. #11
    Join Date
    Feb 2013
    Posts
    12
    Arg!!! Ok, now I am getting a run time error 3075:
    Syntax Error (missing operatior) in query expression 'natureofwork IN (
    'Vertical Expansion' AND usetype IN ('Women's')'.

    I am so close but absolutely can't figure out what is wrong now! Any ideas?
    Thank you!

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If single quotes (') can be found in the data from the ListBox controls (e.g. Women's) a little substitution is required. Replace the lines:
    Code:
    strnatureofworklist = strnatureofworklist & "'" & ctl.ItemData(varItem) & "'"
    and:
    Code:
    strusetypelist = strusetypelist & "'" & ctl.ItemData(varItem) & "'"
    by:
    Code:
    strnatureofworklist = strnatureofworklist & "'" & replace(ctl.ItemData(varItem), "'", "''")  & "'"
    and:
    Code:
    strusetypelist = strusetypelist & "'" & Replace(ctl.ItemData(varItem), "'", "''")  & "'"
    Have a nice day!

  13. #13
    Join Date
    Feb 2013
    Posts
    12
    OMG! Now I am getting an error code 3831:
    The multi-valued field 'natureofwork' cannot be used in a WHERE or HAVING clause.

    I removed the 2 fields: natureofwork and usetype from my query and from the Const_c statement but still getting the error.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If [natureofwork] or any other columns are defined as the so-called "multi-valued field", there is little that can be done.

    This "data type" (if I may call it that way) is almost unusable in SQL and is one of the worst feature Microsoft ever invented. It breaks all rules on which a relational database system is build. See:
    Microsoft Access
    Search Form

    There is perhaps an "alternate" syntax to circumvent this kind of problem, unfortunately I don't know it, if it exists. See: Using multivalued fields in queries - Access - Office.com
    Have a nice day!

  15. #15
    Join Date
    Feb 2013
    Posts
    12
    I don't doubt you. Everything I have read said I should avoid multi-valued fields. In my database I already have VB code that works for 1 mult-valued field. The query runs fine with no errors. But being very new to VB I had no idea how to modify it to allow for 2 multi-valued fields.
    Is there anyway to modify this to include usetype which is set up exactly the same as natureofwork?


    Private Sub cmdOK_Click()
    Dim Q As QueryDef, db As Database
    Dim criteria As String
    Dim ctl As Control
    Dim itm As Variant

    Set ctl = Me!cbonatureofwork
    For Each itm In ctl.ItemsSelected
    If Len(criteria) = 0 Then
    criteria = Chr(34) & ctl.ItemData(itm) & Chr(34)
    Else
    criteria = criteria & "," & Chr(34) & ctl.ItemData(itm) & Chr(34)
    End If
    Next itm
    If Len(criteria) = 0 Then
    itm = MsgBox("You must select one or more items in the" & _
    " list box!", 0, "No selection made")
    Exit Sub
    End If
    Set db = CurrentDb()
    Set Q = db.QueryDefs("projectsbynatureofwork")
    Set db = CurrentDb()
    Set Q = db.QueryDefs("projectsbynatureofwork")
    Q.SQL = "Select [Healthcare Projects].* FROM[Healthcare Projects]Where([natureofwork].value) In (" & criteria & _
    ");"
    Q.Close
    DoCmd.OpenQuery "projectsbynatureofwork"


    End Sub

Posting Permissions

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