Thread: A multi-option box for a search form

I've created a database to search through all the different types of PTO used. I am using a continuous form where the search functions are on the top, the displayed data in the middle, and a pivot chart on that data below. Currently you can search by Employee Number, Employee name, data range, and one selected type of PTO. However, I would like to select multiple types and have it show up in the search. Here is a look at the code I am using:

Option Compare Database
Option Explicit

Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
    Dim varItem As Variant                  'Selected items
    'Dim strDescrip As String                'Description of WhereCondition
    'Dim strDelim As String                  'Delimiter for this field type
    'strDelim = """"
    
    'Number field. Do not add the extra quotes.
    If Not IsNull(Me.txtFilterNum) Then
        strWhere = strWhere & "([empno] = " & Me.txtFilterNum & ") AND "
    End If
    
    'Text field. Use Like to find anywhere in the field.
    If Not IsNull(Me.txtFilterMainName) Then
        strWhere = strWhere & "([employee] Like ""*" & Me.txtFilterMainName & "*"") AND "
    End If
      
    'Date field. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtStartDate) Then
        strWhere = strWhere & "([tmdate] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If
    
    'Date field. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.txtEndDate) Then   'Less than the next day.
        strWhere = strWhere & "([tmdate] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
    End If

    'Loop through the ItemsSelected in the list box. (works only when selecting one)
    With Me.lbcode
        For Each varItem In .ItemsSelected
            If Not IsNull(varItem) Then
                'Build up the filter
                strWhere = strWhere & "([pay_type] Like ""*" & .ItemData(varItem) & "*"") And "
            End If
        Next varItem
    End With
    
    ' doesn't work yet
    'With Me.lbcode
    '    For Each varItem In .ItemsSelected
    '        If Not IsNull(varItem) Then
    '        strWhere = strWhere & strDelim & "([pay_type] Like ""*" & .ItemData(varItem) & "*"") & strDelim "" And "
    '        End If
    '    Next
    'End With

    
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
              
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
   For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acListBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next

        
    'Remove the form's filter.
    Me.FilterOn = False
    End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
    
    Cancel = True
    MsgBox "You cannot add records to the search form.", vbInformation, "Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
    
   ' Me.Filter = "(False)"
   ' Me.FilterOn = True
End Sub

I can get everything to work, however I can only select on type at a time. If I don't select any it displays all types.

Currently all data is within a master table called total time. And the items for the multi-list box are in a table calle pay code. Those tables are related based on PTO Type

If you know how to solve this problem, please help

CharterJP

CharterJP,

First off welcome to the website.

You need to edit your first post to be in accordance with the Forum Rules. ie. Wrap your code in Code Tags.

I have a couple of suggestions and questions.

What is PTO? (to me it means Power Take Off) (farming community)

I would suggest not using filters. I would alter the forms recordsource using SQL. It will give you the ability to create as complex of a filter as you could ever dream. And minimize how much data you actually have loaded.

I would make your complex search box a seperate form, use it and the selections to change the recordsource SQL statement for the main form.

Hope this helps,

Dan

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

CharterJP,

Here is a simpler example of what (I believe) you are trying to do:

Private Sub ApplyAdvancedFilter()
    Dim strSQL As String

On Error GoTo HandleError
    
    strSQL = "SELECT * FROM qryContactsExtended"
    
    If Not IsNull(Me.SearchBox) And Not IsNull(Me.cboFilters) Then
        strSQL = strSQL & " Where (ContactID Like '*" & Me.SearchBox & "*'" & _
                        " Or LastName Like '*" & Me.SearchBox & "*'" & _
                        " Or FirstName Like '*" & Me.SearchBox & "*'" & _
                        " Or [E-mailAddress] Like '*" & Me.SearchBox & "*'" & _
                        " Or Company Like '*" & Me.SearchBox & "*'" & _
                        " Or JobTitle Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingZip Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingCity Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingState Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingCountry Like '*" & Me.SearchBox & "*'" & _
                        " Or [File As] Like '*" & Me.SearchBox & "*'" & _
                        " Or [Contact Name] Like '*" & Me.SearchBox & "*'" & _
                        " Or BusinessPhone Like '*" & Me.SearchBox & "*'" & _
                        " Or HomePhone Like '*" & Me.SearchBox & "*'" & _
                        " Or MobilePhone Like '*" & Me.SearchBox & "*'" & _
                        " Or FaxNumber Like '*" & Me.SearchBox & "*'" & _
                        " Or Notes Like '*" & Me.SearchBox & "*'" & _
                        " Or AccountNumber Like '*" & Me.SearchBox & "*')" & _
                        " AND " & LookupListFormFilter(Me.cboFilters)
        Me.cmdShowAll.Enabled = True
    ElseIf Not IsNull(Me.SearchBox) Then
        strSQL = strSQL & " Where ContactID Like '*" & Me.SearchBox & "*'" & _
                        " Or LastName Like '*" & Me.SearchBox & "*'" & _
                        " Or FirstName Like '*" & Me.SearchBox & "*'" & _
                        " Or [E-mailAddress] Like '*" & Me.SearchBox & "*'" & _
                        " Or Company Like '*" & Me.SearchBox & "*'" & _
                        " Or JobTitle Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingZip Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingCity Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingState Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingCountry Like '*" & Me.SearchBox & "*'" & _
                        " Or [File As] Like '*" & Me.SearchBox & "*'" & _
                        " Or [Contact Name] Like '*" & Me.SearchBox & "*'" & _
                        " Or BusinessPhone Like '*" & Me.SearchBox & "*'" & _
                        " Or HomePhone Like '*" & Me.SearchBox & "*'" & _
                        " Or MobilePhone Like '*" & Me.SearchBox & "*'" & _
                        " Or FaxNumber Like '*" & Me.SearchBox & "*'" & _
                        " Or Notes Like '*" & Me.SearchBox & "*'" & _
                        " Or AccountNumber Like '*" & Me.SearchBox & "*'"
        Me.cmdShowAll.Enabled = True
    ElseIf Not IsNull(Me.cboFilters) Then
        strSQL = strSQL & " Where " & LookupListFormFilter(Me.cboFilters)
        Me.cmdShowAll.Enabled = True
    End If

ApplyFilter:
    Me.RecordSource = strSQL
    Me.Requery
    
ExitHere:
    Exit Sub
    
HandleError:
    MsgBox Err.Description
    Resume ExitHere
    
End Sub

This part here:

"strSQL = strSQL & " Where " & LookupListFormFilter(Me.cboFilters)"

Looks up a field in a table I have that stores predetermined filters.

For example:

If I typed "Dan" in the Field Search on my form (an unbound text box) and I selected Suppliers from my unbound combo box on the form (Me.cboFilters) It would only show me the list of anywhere Dan is located AND ("Supplier = -1" is what is in the filter table) only those contacts who are suppliers.

Hope this makes sense,

Dan