Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Apr 2012
    Posts
    9

    Unanswered: Multi Select List box into Query

    Hi,

    I currently have a simple multi select list box named "raisedby" on a form named "Create Graph"

    I am trying to get a query to use this list box as search parameters but I am struggling with the multi select part.

    I understand that you need to use code to acheive this but I do not really know how to do that.

    Any help would be much appreciated.

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    Hi,

    Try this code replacing the object/control names where appropriate. You need to ensure that you have Microsoft DAO 3.6 Object Library selected as a reference in VBA. To do this Tools -> Macro -> Visual Basic Editor -> Tools -> References.

    Code:
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("yourqueryname")
    For Each varItem In Me!cboyourcontrolname.ItemsSelected
    strCriteria = strCriteria & "," & Me!cboyourcontrolname.ItemData(varItem) & ""
    Next varItem
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything." _
    , vbExclamation, "Nothing to find!"
    
    Exit Sub
    End If
    
    strCriteria = right(strCriteria, Len(strCriteria) - 1)
    strSQL = "SELECT* FROM tblname " & _
    "WHERE tblname .fieldname IN(" & strCriteria & ");"
    qdf.SQL = strSQL
    
    DoCmd.OpenQuery "yourqueryname"
    Set db = Nothing
    Set qdf = Nothing
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Apr 2012
    Posts
    9
    Hi Dave,

    Thanks, That looks good however I am unsure as to where to put this code, could you give me any advice.

    Many Thanks

    Richard

  4. #4
    Join Date
    Apr 2012
    Posts
    9
    Ok, I have entered the code in the on click event of a button on the form. the code Entered is:

    Private Sub Command4_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Create Graph")
    For Each varItem In Me!Raisedby.ItemsSelected
    strCriteria = strCriteria & "," & Me!Raisedby.ItemData(varItem) & ""
    Next varItem
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything." _
    , vbExclamation, "Nothing to find!"

    Exit Sub
    End If
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    strSQL = "SELECT* FROM fault records " & _
    "WHERE fault records.raised by IN(" & strCriteria & ");"
    qdf.SQL = strSQL
    DoCmd.OpenQuery "create graph"
    Set db = Nothing
    Set qdf = Nothing
    End Sub


    However now when I click the button I get an error box which says:

    Run-time error '3075':

    Syntax error (missing operator) in query expression 'fault records.raised by IN(External,Internal)'


    When I click on debug, the following line of the code is highlighted:

    qdf.SQL = strSQL


    Any Help here would be greatly appreciated.

    Richard

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is the value in strSQL
    my guess is that strCriteria isn't set or set properly
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2012
    Posts
    9
    Hi, Could you please expand on that as I am relatively new to access and do not understand.
    Where do I set the strCriteria?

    Thanks

    Richard

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your code is setting strCriteria

    I'd suggest you put a messagebox after the 'where' clause is added to strSQL

    although to be hoinest looking at your code the problem is
    "WHERE fault records.raised by IN(" & strCriteria & ");"
    if you have a space between a table name or column name then you must use square brackets to tell the compiler to treat the following words as a single entity
    Code:
    "WHERE [fault records].raised by IN(" & strCriteria & ");"
    I'd strongly recommend that you do not use spaces between table or column names as this can cause grief later on.

    whether you use camel case or underscore is up to you, there is no clear definitive opinion although many here suggest use underscore

    underscore:-
    Code:
    "WHERE fault_records.raised by IN(" & strCriteria & ");"
    CamelCase
    Code:
    "WHERE FaultRecords.Raised by IN(" & strCriteria & ");"
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2012
    Posts
    9
    That seemed to be the problem, I put the square brackets in and it works now.

    Many Thanks for your help

  9. #9
    Join Date
    Apr 2012
    Posts
    9
    Hi again,

    Do your have any ideas how I would get this to work with 2 or more list boxes on the same form all activated from the same button click?

    Thanks

    Richard

  10. #10
    Join Date
    Jan 2012
    Posts
    97
    Assuming you want these to form part of the same criteria then simply repeat the following lines for each control...

    Code:
    For Each varItem In Me!cboyourcontrolname.ItemsSelected
    strCriteria = strCriteria & "," & Me!cboyourcontrolname.ItemData(varItem) & ""
    Next varItem
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  11. #11
    Join Date
    Apr 2012
    Posts
    9
    I dont think it will be the same criteria as the data is from different table fields. If I just repeat those lines of code as you suggested then query ignores my second list box altogether.

  12. #12
    Join Date
    Jan 2012
    Posts
    97
    Am not sure what you are trying to achieve. Are you wanting to collate selections from multiple listboxes into one string which is used as criteria for a query or are you trying to use selections from multiple listboxes as criteria for different fields in a query?

    The attached DB will collate information from multiple listboxes in a message box when you click on the 'Go...' command button on form1. You would simply need to remove the apostrophes from the code in order for it to be fully functional, i.e. pass the string to a query rather than display it in a message box.

    If you want each list box selection to be used for criteria in different fields you need to collate the selections using the code above but declare multiple string criteria variables i.e. strCriteria1, strCriteria2 etc and write these into your SQL statement.
    Attached Files Attached Files
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  13. #13
    Join Date
    Apr 2012
    Posts
    9
    Thanks for that, I have now changed my code to the following, but does not work, perhaps you can see where I am going wrong?

    Private Sub Command4_Click()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria1 As String
    Dim strCriteria2 As String
    Dim strSQL As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("multilist1")

    For Each varItem In Me!Raisedby.ItemsSelected
    strCriteria1 = strCriteria1 & ",'" & Me!Raisedby.ItemData(varItem) & "'"
    Next varItem


    If Len(strCriteria1) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"

    For Each varItem In Me!Model.ItemsSelected
    strCriteria2 = strCriteria2 & ",'" & Me!Model.ItemData(varItem) & "'"
    Next varItem


    If Len(strCriteria2) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"
    Exit Sub

    End If

    strCriteria1 = Right(strCriteria1, Len(strCriteria1) - 1)
    strSQL = "SELECT * FROM [fault records] " & _
    "WHERE [fault records].[raised by] IN(" & strCriteria1 & ");"

    End If

    strCriteria2 = Right(strCriteria2, Len(strCriteria2) - 1)
    strSQL = "SELECT * FROM [fault records] " & _
    "WHERE [fault records].[model] IN(" & strCriteria2 & ");"

    qdf.SQL = strSQL


    DoCmd.OpenQuery "query9"
    Set db = Nothing
    Set qdf = Nothing


    End Sub

  14. #14
    Join Date
    Jan 2012
    Posts
    97
    I'm assuming from this your looking to use selections from multiple controls as criteria for different fields in the same query. If this is the case then...

    Code:
    'Declarations
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria1 As String
    Dim strCriteria2 As String
    Dim strCriteria3 As String
    Dim strSQL As String
    
    Set db = CurrentDb()
    'Set qdf = db.QueryDefs("yourqueryname")
    
    'For each item in the first control get the selections & store as strCriteria1
    For Each varItem In Me!lstfirst.ItemsSelected
    strCriteria1 = strCriteria1 & "," & Me!lstfirst.ItemData(varItem) & ""
    Next varItem
    
    'For each item in the second control get the selections & store as strCriteria2
    For Each varItem In Me!lstsecond.ItemsSelected
    strCriteria2 = strCriteria2 & "," & Me!lstsecond.ItemData(varItem) & ""
    Next varItem
    
    'For each item in the third control get the selections & store as strCriteria3
    For Each varItem In Me!lstthird.ItemsSelected
    strCriteria3 = strCriteria3 & "," & Me!lstthird.ItemData(varItem) & ""
    Next varItem
    
    'Check that the user has selected something in as many controls as you wish
    If Len(strCriteria1) = 0 Or Len(strCriteria2) = 0 Or Len(strCriteria3) = 0 Then
    MsgBox "You did not select anything." _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If
    
    'Edit the strCriteria1/2/3 to ensure they will be passed to the query in the right format
    strCriteria1 = Right(strCriteria1, Len(strCriteria1) - 1)
    strCriteria2 = Right(strCriteria2, Len(strCriteria2) - 1)
    strCriteria3 = Right(strCriteria3, Len(strCriteria3) - 1)
    
    'Display each criteria in a message box, just to check you are sending the right string to the query, you can remove this once you know its working
    MsgBox (strCriteria1)
    MsgBox (strCriteria2)
    MsgBox (strCriteria3)
    
    'Incoporate the strCriteria1/2/3 into your SQL statement
    'strSQL = "SELECT* FROM tblname " & _
    '"WHERE tblname .fieldname IN(" & strCriteria & ");"
    
    'qdf.SQL = strSQL
    
    'DoCmd.OpenQuery "yourqueryname"
    Set db = Nothing
    'Set qdf = Nothing
    End Sub
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  15. #15
    Join Date
    May 2012
    Posts
    3

    strcriteria

    Hi, I am using basically the same vba code in my database. It runs a query based on criteria from a multi-select list box. When I click to invoke the query, the strcriteria treats the items selected as field names rtaher than items from the list. This is the excerpt from my code.

    strCriteria = right(strCriteria, Len(strCriteria) - 1)
    strSQL = "SELECT * FROM Oasis_Data_Clean_Fin " & _
    "WHERE Oasis_Data_Clean_Fin.Service in (" & strCriteria & ")"

    After I run the query, which ends up with no results, I go into teh query design mode and the criteria line shows this :

    In ([Arrive],[Ground])

    If I change this to In ("Arrive","Ground") the query runs as expected.

    How do I get he vba code to not enter the [brackets] around the criteria?

    Thanks...

Posting Permissions

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