Results 1 to 15 of 15
  1. #1
    AndyRob1973 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    7

    passing multiple values to a single query field from a form.

    Hello all,



    I'm using access 2010 and I have built a form with a combo box with a list of values to choose from. I have a query which picks up the selection rom the form and uses it as criteria.

    How can I select more than one value for in the form and pass these values to the query?

    Thank you in advance.

    AR

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,151
    A combobox allows only 1 item to be selected unless it is bound to a multi-value field. An unbound listbox can allow multiple selection. VBA code can construct criteria from listbox that would be used within IN() function in SQL statement. Unfortunately, IN() function cannot be dynamic in query object. Review http://allenbrowne.com/ser-50.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    AndyRob1973 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    7
    Quote Originally Posted by June7 View Post
    A combobox allows only 1 item to be selected unless it is bound to a multi-value field. An unbound listbox can allow multiple selection. VBA code can construct criteria from listbox that would be used within IN() function in SQL statement. Unfortunately, IN() function cannot be dynamic in query object. Review http://allenbrowne.com/ser-50.html

    Thank you June7,

    I will have a good look at that.

  4. #4
    AndyRob1973 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    7
    Thanks again June7,

    I've moved on slightly and have built a form with a multi select list which passes the selection and opens a query with the criteria selected. It seem to work but I would like to add a tick box to the form and when it is ticked, pass a Y to the criteria and if not then a N. The code so far is below. Could anyone help (or indeed improve the code below)? Do I need to send more info to you?

    Option Compare Database



    Private Sub cmdOpenQuery_Click()



    On Error GoTo Err_cmdOpenQuery_Click

    Dim MyDB As DAO.Database

    Dim qdef As DAO.QueryDef

    Dim i As Integer

    Dim strSQL As String

    Dim strWhere As String

    Dim strIN As String

    Dim flgSelectAll As Boolean

    Dim varItem As Variant



    Set MyDB = CurrentDb()



    strSQL = "SELECT * FROM tblOTR"



    'Build the IN string by looping through the listbox

    For i = 0 To lstSpecialties.ListCount - 1

    If lstSpecialties.Selected(i) Then

    If lstSpecialties.Column(0, i) = "All" Then

    flgSelectAll = True

    End If

    strIN = strIN & "'" & lstSpecialties.Column(0, i) & "',"

    End If

    Next i



    'Create the WHERE string, and strip off the last comma of the IN string

    strWhere = " WHERE [Specialty] in (" & Left(strIN, Len(strIN) - 1) & ") "



    'If "All" was selected in the listbox, don't add the WHERE condition

    If Not flgSelectAll Then

    strSQL = strSQL & strWhere

    End If



    MyDB.QueryDefs.Delete "qry_AdvSearch"

    Set qdef = MyDB.CreateQueryDef("qry_AdvSearch", strSQL)



    'Open the query, built using the IN clause to set the criteria

    DoCmd.OpenQuery "qry_AdvSearch", acViewNormal



    'Clear listbox selection after running query

    For Each varItem In Me.lstSpecialties.ItemsSelected

    Me.lstSpecialties.Selected(varItem) = False

    Next varItem





    Exit_cmdOpenQuery_Click:

    Exit Sub



    Err_cmdOpenQuery_Click:



    If Err.Number = 5 Then

    MsgBox "You must make a selection(s) from the list", , "Selection Required !"

    Resume Exit_cmdOpenQuery_Click

    Else

    'Write out the error and exit the sub

    MsgBox Err.Description

    Resume Exit_cmdOpenQuery_Click

    End If



    End Sub

    Private Sub cmdClose_Click()

    On Error GoTo Err_cmdClose_Click





    DoCmd.Close



    Exit_cmdClose_Click:

    Exit Sub



    Err_cmdClose_Click:

    MsgBox Err.Description

    Resume Exit_cmdClose_Click







    End Sub

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,151
    The Allen Browne link shows how to combine criteria from multiple controls.

    In future, please post code between CODE tags to retain indentation and readability.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    AndyRob1973 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    7
    Hello,

    Iíve used the Allen Browne link which I found useful and along with help from a friend, Iíve created something but would like some help if possible.

    I have a table (tblOTR) on which a form is based. I have a multi select list box (lstSpec) and a combobox (cmbStat) and a button which runs a query on the table based on the box selections. The available values in the boxes come from a distinct query but with ĎALLí unioned onto the query to allow the user to in effect not choose to filter by this field.
    It works when a selection is made in both boxes but I would like to incorporate the ĎALLí so that the user needs not select a specific value. I intend to add more combo and list boxes and a struggling to find a way to alter my code to allow for ĎALLí to be selected.
    Hope this makes sense! (I've been told to wrap code in tags which hopefully I've managed below)

    Thankyou

    Code:
    Private Sub OpenQuery_Click()
    On Error GoTo Err_cmdOpenQuery_Click
        Dim MyDB As DAO.Database
        Dim qdef As DAO.QueryDef
        Dim i As Integer
        Dim strSQL As String
        Dim strWhere As String
        Dim strSpec As String
        Dim strStat As String
        Dim flgSelectAll As Boolean
        Dim varItem As Variant
    
        Set MyDB = CurrentDb()
    
        strSQL = "SELECT * FROM tblOTR"
        
          
         'Build the specialty criteria string by looping through the listbox
        For i = 0 To lstSpec.ListCount - 1
            If lstSpec.Selected(i) Then
                If lstSpec.Column(0, i) = "All" Then
                    flgSelectAll = True
                End If
                strSpec = strSpec & "'" & lstSpec.Column(0, i) & "',"
            End If
        Next i
        
        'Build the status criteria from the combo box
       strStat = Me.cmbStat.Value
       
        
        
        'Create the WHERE string, and strip off the last comma of the IN string
        strWhere = " WHERE [Specialty] in " & _
                   "(" & Left(strSpec, Len(strSpec) - 1) & ") AND [Status] ='" & strStat & "'"
                   
        'If "All" was selected in the listbox, don't add the WHERE condition
        If Not flgSelectAll Then
            strSQL = strSQL & strWhere
        End If
        
        
        'MyDB.QueryDefs.Delete "qryOTR"
        MyDB.QueryDefs.Delete "qryOTR"
        Set qdef = MyDB.CreateQueryDef("qryOTR", strSQL)
        
        'Open the query, built using the IN clause to set the criteria
        DoCmd.OpenQuery "qryOTR", acViewNormal
    
        'Clear specialty listbox selection after running query
        For Each varItem In Me.lstSpec.ItemsSelected
            Me.lstSpec.Selected(varItem) = False
        Next varItem
    
        ' clear status combo selection after running query
        Me.cmbStat.Value = ""
        
        
    Exit_cmdOpenQuery_Click:
        Exit Sub
    
    Err_cmdOpenQuery_Click:
    
        If Err.Number = 5 Then
            MsgBox "You must make a selection(s) from the list" _
                   , , "Selection Required !"
            Resume Exit_cmdOpenQuery_Click
        Else
            'Write out the error and exit the sub
            MsgBox Err.Description
            Resume Exit_cmdOpenQuery_Click
        End If
    
    End Sub

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,151
    Correction, the Allen Browne link did not show how to combine criteria from multiple controls, unless you followed another link within that article http://allenbrowne.com/ser-62.html

    Use If Then conditional to determine whether or not to use a control in criteria.

    I prefer to test for Null in controls and therefore reset controls to Null, not empty string. Allen Browne's code uses Null.

    Exit loop if "ALL" is selected, make sure strSpec is empty string. The flgSelectAll won't be needed. Since strSpec will be an empty string, can concatenate to other variables without error.
    Code:
        For i = 0 To lstSpec.ListCount - 1
            If lstSpec.Selected(i) Then
                If lstSpec.Column(0, i) = "All" Then
                    strSpec = ""
                    Exit For
                End If
                strSpec = strSpec & "'" & lstSpec.Column(0, i) & "',"
            End If
        Next i
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    AndyRob1973 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    7
    Thankyou June7,

    So would you have a for loop for each list/combo box, and build the where clause from that?

    I'll have a go at what I thin you mean.

    Thanks

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,151
    Comboboxes would not need loop. Treat them same as textbox.

    Only multi-select listbox would require looping.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    AndyRob1973 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    7
    Hi June7,

    I have tried using:

    Code:
    If lstSpec.Column(0, i) = "All" Then
                    strSpec = ""
    instead of:

    Code:
                If lstSpec.Column(0, i) = "All" Then
                    flgSelectAll = True
    but i'm getting the error -

    Run-time error: '5'
    Invallid procedure call or argument.

    Don't suppose you could help?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,151
    Exactly which line triggered error?

    What is the listbox RowSource? Is the listbox multi-column? If the "ALL" is in second column, try:

    If lstSpec.Column(1, i) = "All" Then

    This does work for me. However, instead of looping all items in listbox, just loop through the selected items per Allen Browne example.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,713
    Quote Originally Posted by AndyRob1973 View Post

    Code:
    Private Sub OpenQuery_Click()
    On Error GoTo Err_cmdOpenQuery_Click
        Dim MyDB As DAO.Database
        Dim qdef As DAO.QueryDef
        Dim i As Integer
        Dim strSQL As String
        Dim strWhere As String
        Dim strSpec As String
    .
    .
    .
    .
    
        
    Exit_cmdOpenQuery_Click:
        Exit Sub
    
    Err_cmdOpenQuery_Click:
    
        If Err.Number = 5 Then
            MsgBox "You must make a selection(s) from the list" _
                   , , "Selection Required !"
            Resume Exit_cmdOpenQuery_Click
        Else
            'Write out the error and exit the sub
            MsgBox Err.Description
            Resume Exit_cmdOpenQuery_Click
        End If
    
    End Sub
    You might comment out the OnErrpr line until you debug the code. This way you will know what line is causing the error.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #13
    AndyRob1973 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    7
    Hello all,

    Thanks for al the replies. I seem to be getting myself into a bit of twist with it.

    I'm new to the forum thing so I don't know whether this goes against etiquette but wondered if someone could build on what I'm trying to do. Ive attached a simple version of my database. Basically I've got a form based on a table (tblOTR).


    So far, I can combine a multi select list box (lstspec) and a comb box (cmbstat) to query the table.

    The values in each box are pulled from distinct queries will the option to select the word 'ALL'.

    1) I would like to have the option to select any combination from both boxes, which works until you select all specific specialty with Status= ALL
    2) I would like to be able to add other list and combo boxes to add to the seach.

    Would anyone be prepared to look at my code and alter it for me - I think I would learn better this and build upon it.
    Attached Files Attached Files

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,151
    Consider this SQL for RowSource - nested query not needed.

    SELECT DISTINCT Specialty FROM tblOTR WHERE NOT Specialty IS NULL
    UNION SELECT "ALL" FROM tblOTR;

    Follow Allen Browne's examples.
    Code:
        'Build the status criteria from the combo box
        If Not IsNull(Me.cmbStat) And Me.cmbStat <> "ALL" Then strStat = "Status = '" & Me.cmbStat.Value & "' AND "
        
        'Build Specialty criteria from listbox
        With Me.lstSpec
            For Each varItem In .ItemsSelected
                If Not IsNull(varItem) Then
                    If .ItemData(varItem) = "ALL" Then
                        Exit For
                    End If
                    strSpec = strSpec & "'" & .ItemData(varItem) & "',"
                End If
            Next
        End With
        
        'Strip off the last comma of the IN string
        If strSpec <> "" Then strSpec = "[Specialty] IN(" & Left(strSpec, Len(strSpec) - 1) & ") AND "
    
        '***********************************************************************
        'Chop off the trailing " AND ", and use the string.
        '***********************************************************************
        strWhere = strSpec & strStat
        'See if the string has more than 5 characters (a trailng " AND ") to remove.
        lngLen = Len(strWhere) - 5
        If lngLen > 0 Then   'Yep: there is something there, so remove the " AND " at the end.
            strWhere = Left$(strWhere, lngLen)
            'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
            Debug.Print strWhere
        End If
    

    Why are you modifying QueryDefs instead of just applying to form or report filter?
    The QueryDefs part is not working. I have used this only twice before.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,713
    You'd think building a search form with 2 controls would be easy........

    Because of the way in which you populate the list box and the combo box, there are NULLs in each of the controls - which you can select as criteria.
    The list box has a Row Source of
    Code:
    SELECT DISTINCT * FROM (SELECT tblOTR.Specialty FROM tblOTR UNION ALL SELECT 'ALL' FROM tblOTR )  AS A;
    The combo box has a Row Source of
    Code:
    SELECT DISTINCT * FROM (SELECT tblOTR.Status FROM tblOTR UNION ALL SELECT 'ALL' FROM tblOTR )  AS A;
    Because there are records with NULLs in each of the fields, you can select a NULL in each of the controls.



    So what do you have to take into consideration?
    1) List box has 1 or more non NULL selections and Combo box has a non NULL selection
    2) List box has 1 or more non NULL selections and Combo box has a NULL selected
    3) List box has 1 or more non NULL selections and Combo box does not have a selection

    4) List box has a NULL selection and Combo box has a non NULL selection
    5) List box does not have a selection and Combo box has a non NULL selection

    6) List box has a NULL selected and Combo box has a NULL selected
    7) List box does not have a selection and Combo box does not have a selection

    So for #6, you need to convert the List box and Combo box NULLs to empty strings and treat them like #7.


    Now add 2, 3 or 4 more controls. Not hard to do, just tedious. You have to be sure you have thought of every combination/permutation of the controls.


    I rewrote your code (Note: the query is NOT deleted - the query SQL is changed)
    I changed to names of some variables to what I use (so I didn't get confused)
    Code:
    Private Sub OpenQuery_Click()
        On Error GoTo Err_cmdOpenQuery_Click
    
        Dim d As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim sSQL As String
    
        Dim strWhere As String
        Dim strSpec As String
        Dim strStat As String
        Dim flgSelectAll As Boolean
        Dim varItem As Variant
    
    
        Set d = CurrentDb()
    
        'default SQL
        sSQL = "SELECT * FROM tblOTR"
    
        ' loop through listbox "lstSpec"
        With Me.lstSpec
            For Each varItem In .ItemsSelected
                If .Selected(varItem) Then
                    If .ItemData(varItem) = "All" Or Len(.ItemData(varItem) & "") = 0 Then
                        flgSelectAll = True
                        Exit For
                    End If
                    strSpec = strSpec & "'" & .ItemData(varItem) & "',"
                End If
            Next
        End With
    
        'Build the status criteria from the combo box
        If Len(Trim(Me.cmbStat & "")) > 0 And Me.cmbStat <> "ALL" Then
            strStat = strStat & " [Status] = '" & Me.cmbStat & "'"
        End If
    
    
    '-------------------- Build the Criteria String -------------------
    
        'Create the WHERE string, and strip off the last comma of the IN string
        If Len(Trim(strSpec)) > 0 Then
            strWhere = " WHERE [Specialty] in " & "(" & Left(strSpec, Len(strSpec) - 1) & ") "
        End If
    
        If Len(strWhere) > 0 And Len(Trim(strStat)) > 0 Then          'values in strSpec and strStat
            strWhere = strWhere & " AND " & strStat
        ElseIf Len(strWhere) = 0 And Len(Trim(strStat)) > 0 Then    'value only in strStat
            strWhere = " WHERE " & strStat
        ElseIf flgSelectAll Then
            'All [Specialty] selected
        ElseIf Len(strWhere) = 0 And Len(Trim(strStat)) = 0 Then   ' no value in either strSpec or strStat
            MsgBox "You must make a selection(s) from the list", , "Selection Required !"
            Set qdf = Nothing
            Set d = Nothing
            Exit Sub
        End If
        '  Debug.Print strWhere
    '-------------------- End Build the Criteria String -------------------
    
    
        'If "All" was selected in the listbox, don't add the WHERE condition
        If Len(Trim(strWhere)) > 0 Then
            sSQL = sSQL & strWhere
        End If
    
        'modify the SQL for the query "qryOTR"
        Set qdf = d.QueryDefs("qryOTR")
        qdf.SQL = sSQL
    
        'Open the query, built using the IN clause to set the criteria
        DoCmd.OpenQuery "qryOTR", acViewNormal
    
        'Clear specialty listbox selection after running query
        For Each varItem In Me.lstSpec.ItemsSelected
            Me.lstSpec.Selected(varItem) = False
        Next varItem
    
        ' clear status combo selection after running query
        Me.cmbStat.Value = ""
    
    
    Exit_cmdOpenQuery_Click:
        'clean up
        On Error Resume Next
        Set qdf = Nothing
        Set d = Nothing
        Exit Sub
    
    Err_cmdOpenQuery_Click:
    
        If Err.Number = 5 Then
            MsgBox "You must make a selection(s) from the list", , "Selection Required !"
            Resume Exit_cmdOpenQuery_Click
        Else
            'Write out the error and exit the sub
            MsgBox Err.Description
            Resume Exit_cmdOpenQuery_Click
        End If
    
    End Sub

    I did some testing - but I might have missed an option combination.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 2
    Last Post: 06-12-2019, 05:57 PM
  2. Replies: 8
    Last Post: 06-14-2018, 10:14 AM
  3. Multiple values for a single database field
    By NJH0512 in forum Access
    Replies: 3
    Last Post: 01-30-2018, 07:31 PM
  4. Retrieving Multiple Values into a Single Field
    By Simbiose in forum Queries
    Replies: 6
    Last Post: 08-23-2016, 07:54 AM
  5. Passing multiple values to a second form
    By WithoutPause in forum Forms
    Replies: 39
    Last Post: 02-12-2014, 04:03 PM

Tags for this Thread

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 - Senior Forums