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

[AndyRob1973]

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

[June7]

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

[AndyRob1973]

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.

[AndyRob1973]

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

[June7]

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.

[AndyRob1973]

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

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

[June7]

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.

    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

[AndyRob1973]

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

[June7]

Comboboxes would not need loop. Treat them same as textbox.

Only multi-select listbox would require looping.

[AndyRob1973]

Hi June7,

I have tried using:

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

instead of:

            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?

[June7]

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.

[ssanfu]

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.

[AndyRob1973]

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.

[June7]

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.

    '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.

[ssanfu]

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

SELECT DISTINCT * FROM (SELECT tblOTR.Specialty FROM tblOTR UNION ALL SELECT 'ALL' FROM tblOTR )  AS A;

The combo box has a Row Source of

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)

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.