RealTime Listbox Filter from textbox for Access

Question

I looked around and had a hard time finding anything that helped let alone a canned solution to use. I needed to filter a listbox in realtime so my users could search every row / column for a term.

The listbox I had was unbound and I was using buttons that drove logic / code to call various queries that would populate the data in the listbox. Every button called a query with a different number of columns, so the listbox changed dynamically to display this information accordingly.

Since I had such a hard time finding anything about this issue, I decided to share my solution with all of you wonderful folks who have helped me with my development issues.

Please see the solution below

If you think I need to fix anything in the post, let me know and I'll update it to better explain.

Answer 1

One small thing to point out, is that I store the UNFILTERED recordset that was originally stored in the listBox to a global variable on the form. I set the textbox to have an "onChange" event trigger and call the function below with it. I pass in the listbox, the user String from the textbox, and the global variable I made for the unfiltered recordset. This is needed to get the original data back when characters are deleted.

Also, this function does not handle numeric columns well. I realized this after some testing with a Query that had numeric datatype columns. In order to step around this issue, I set the query to return the number as a string using the CStr() function. For our newbies, I simply went into the named query, found my numeric column, and put the Cstr in the "field row". So for example, I have a numeric column called "Customer Impacted". I went into the query, and wrote this on the 'field row' for the [customer's impacted column]:

Customers_Affected: Cstr([customers impacted])

I want to caution that you may experience lag if you have huge recordsets. I'm only using about a size of 3000 and it runs very nicely. Enjoy.

Function realTimeFilter(ByVal List As Listbox, ByVal userString As String, ByVal staticRecordSet As DAO.Recordset)

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'\\\                                                                                \\\
'\\\        This Function allows the user to input any string to create a           \\\
'\\\        Filter on a given listbox in realtime.                                  \\\
'\\\                                                                                \\\
'\\\        The programmer gives this fucntion the listbox of values to filter,     \\\
'\\\        The user's input string, and the unfiltered recordset that should be    \\\
'\\\        held in a global variable on the form.                                  \\\
'\\\                                                                                \\\
'\\\        I personally create a global called baseRecord.  Everytime I update     \\\
'\\\        the records in the listbox with a new unfiltered set,                   \\\
'\\\        I clone a copy to baseRecord.  This allows                              \\\
'\\\        the user to delete strings from the filter and regain the old dataset   \\\
'\\\        without having to query the data to the box again.                      \\\
'\\\                                                                                \\\
'\\\        enjoy!                                                                  \\\
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\


'declare variables
Dim rs As DAO.Recordset
Dim str() As String
Dim filterStr As String
Dim i As Integer
Dim k As Integer

'adds unfiltered recordset back to listbox and also puts the data into our set for manipulation
Set List.Recordset = staticRecordSet.OpenRecordset
Set rs = List.Recordset


'split the terms
str = Split(userString, ",")

'examine the textbox string after it has been parsed. Determine which set of logic to use:
'first set is for single search criteria.  Second block is for multiple search criteria
If (UBound(str) = 0) Then

            'loop through the column fields
            For i = 0 To rs.Fields.Count - 1

                'if not on last column add an "OR" to the end of the filter string.  Else cap the string
                If ((i < rs.Fields.Count - 2) Or (i = rs.Fields.Count - 2)) Then
                    filterStr = filterStr & " " & rs.Fields(i).Name & " like '" & Trim(str(0)) & "*' OR "
                Else
                    filterStr = filterStr & " " & rs.Fields(i).Name & " like '" & Trim(str(0)) & "*'"
                End If
            Next i

            'set the filter
            rs.Filter = filterStr
    Else
        'start by enclosing the first logical string
        filterStr = "("

        'cycle through each word in the array of Strings
        For i = LBound(str) To UBound(str)

            'cycle through each column name in the recordset
            For k = 0 To rs.Fields.Count - 1

                'if not the final column add an "OR" at the end of the filter
                If ((k < rs.Fields.Count - 2) Or (k = rs.Fields.Count - 2)) Then
                    filterStr = filterStr & " " & rs.Fields(k).Name & " like '" & Trim(str(i)) & "*' OR "

                'if the final column AND string is not the last element add "AND (" to the end of the string to start the next
                'portion of logic in the string
                ElseIf ((i < UBound(str) And k = rs.Fields.Count - 1)) Then
                    filterStr = filterStr & " " & rs.Fields(k).Name & " like '" & Trim(str(i)) & "*') AND ("

                'if last column and last string in the array, cap the filter string
                Else
                    filterStr = filterStr & " " & rs.Fields(k).Name & " like '" & Trim(str(i)) & "*')"
                End If
            Next k

            'add filter
            rs.Filter = filterStr
        Next i
End If


'set recordset and refresh the listbox
Set List.Recordset = rs.OpenRecordset
List.Requery

'housekeeping
rs.Close
Set rs = Nothing
End Function