Problem with Search Boxes

Heide · Mar 22, 2025

I am working on a database currently, and have set up search boxes in a form. There may be three or four or five of these boxes, depending on the form and the underlying data. While the boxes are working, they reset the recordset when I go from one box to the next. In other words, the first box filters the data, then when I go to the second box, the data resets to all records. What I need is for the boxes to work successively: each additional criteria entered should narrow down the list. I will post my code here, and if you think it helpful, can do a screen grab of the boxes in the form. Thank you for any help. I have spent way too much time trying to figure this out, and while I have resolved some minor issues, the main issue remains.

First I create a text box, named "txt_search_box", then I add the code to the OnKeyUp event. Subsequent boxes on the same form are named "txt_search_box1" and so on.

Private Sub txt_search_box_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
' Search Box
'
'******************************************************************
' Declare variables
Dim filter_data As String
' Apply filter if text is entered in search box
If Len(txt_search_box.Text) > 0 Then
' Set the text entered in search box equal to the variable defined above
filter_data = txt_search_box.Text
' Build the filter string
' Need to add to filter string for each field you wish to search
Me.Form.Filter = " [fiscalyear] LIKE '*" & filter_data & "*'" _
' Apply filter
Me.Form.FilterOn = True
' Prevent text in search box from being removed after refresh
txt_search_box.SelStart = Len(txt_search_box.Text)
Else
' Clear filter string
Me.Form.Filter = ""
' Remove filter
Me.Form.FilterOn = False
' Set focus
txt_search_box.SetFocus

End If

End Sub

isladogs · Mar 22, 2025

You will need to concatenate (combine) the filter criteria
See if my example apps help you with the idea

Multiple Group & Filter

This article discusses different ways of filtering and grouping data in Access. It includes two example apps showing the use of multiple combo boxes & toggle buttons to filter and group data.

www.isladogs.co.uk

Heide · Mar 22, 2025

In the original code, it did concatenate several fields, but they were all in one box, so didn't work well (same basic problem...when it found results, they listed, then when more criteria were entered, the records reset).

I think I understand the concept of what you are suggesting, and it makes sense, but I don't know enough about coding to put it together. Any help with that would be hugely appreciated!

BTW, I love the quote...and it's true.

theDBguy · Mar 23, 2025

It may be difficult to explain and may be easier to show it using a demo but what I've done before was used a bunch of TempVars, one for each search term. In the end, the filter ends up being something like: TempVars!Search1 AND TempVars!Search2 AND so on...

MajP · Mar 23, 2025

Filter forms is something that is done all the time in many different ways. So a long time a ago I wrote some helper code that does most of this for me. I can quickly build a filter form that uses comb boxes, multi select listboxes, dates, booleans, and many variants.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

But the gist is in my combineFilter function that lets me pass in the parts from each control and combine it.
For each control build a variable to hold its filter.
Now I have an extremely powerful function called getFilterfromControl. This for example will build the filter for many types of control without additional code. It is explained in the thread.
But you can build your own partial filter. If there is nothing in the control the partial filter should be an empty string.

Code:

Public Function GetFilter() As String
  'need variable for each partial filter
  Dim strName As String
  Dim strDate As String
  Dim strState As String
  Dim strCountry As String
  Dim strID As String
  Dim strBetween As String
 
  'Need final filter
  Dim StrFilter As String
  Dim AndOr As CombineFilterType
 
  'Optional Code for 'and or'. If not remove code for this.
  Select Case Me.frmAndOr
    Case 1
      AndOr = ct_And
    Case 2
      AndOr = ct_OR
  End Select
 
  strName = GetFilterFromControl(Me.cmboName)
  strState = GetFilterFromControl(Me.listState)
  strCountry = GetFilterFromControl(Me.cmboCountry)
  strDate = GetFilterFromControl(Me.cmboCreated)
  strID = GetFilterFromTextBox(Me.txtID, sdt_Numeric, "ID", flt_Equal)
  If IsDate(Me.cmboDateEnd) And IsDate(Me.cmboDateStart) Then
    strBetween = GetBetweenFilter(Me.cmboDateStart, Me.cmboDateEnd, "Created_Date")
  End If
 
  StrFilter = CombineFilters(AndOr, strName, strState, strCountry, strDate, strID, strBetween)
 
  GetFilter = StrFilter
  'need code for each combo. Just copy and past. Need to handle dates and strings
 
End Function

arnelgp · Mar 23, 2025

you can also do this:

Code:

Dim m_filter1 As String
Dim m_filter2 As String
Dim m_filter3 As String


Private Sub txt_search_box_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
' Search Box
'
'******************************************************************
' Apply filter if text is entered in search box
If Len(txt_search_box.Text) > 0 Then
' Build the filter string
' Need to add to filter string for each field you wish to search
m_filter1 = "[fiscalyear] LIKE '*" & txt_search_box.Text & "*'"
Else
' Clear filter string
m_filter1 = ""
End If

Call FilterForm

End Sub


Private Sub txt_search_box2_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
' Search Box2
'
'******************************************************************
' Apply filter if text is entered in search box
If Len(txt_search_box2.Text) > 0 Then
m_filter2 = "[Field2] LIKE '*" & txt_search_box2.Text & "*'"
Else
' Clear filter string
m_filter2 = ""
End If

Call FilterForm

End Sub


Private Sub txt_search_box3_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
' Search Box3
'
'******************************************************************
' Apply filter if text is entered in search box
If Len(txt_search_box3.Text) > 0 Then
m_filter3 = "[Field3] LIKE '*" & txt_search_box3.Text & "*'"
Else
' Clear filter string
m_filter2 = ""
End If

Call FilterForm

End Sub

Private Sub FilterForm()
Dim ctl As TextBox
Dim sFilter As String
Set ctl = Screen.ActiveControl
If Len(m_filter1) Then
    sFilter = sFilter & " AND " & m_filter1
End If
If Len(m_filter2) Then
    sFilter = sFilter & " AND " & m_filter2
End If
If Len(m_filter3) Then
    sFilter = sFilter & " AND " & m_filter3
End If
If Len(sFilter) Then
    sFilter = Mid$(sFilter, 6)
    Me.Filter = sFilter
    Me.FilterOn = True
Else
    Me.FilterOn = False
End If
With ctl
    .SelStart = Len(ctl.Text)
End If
End Sub

MarkK · Mar 23, 2025

Code:

Dim m_filter(2) As String

Private Sub txt_search_box_KeyUp(KeyCode As Integer, Shift As Integer)
    SetFilter 0, "[fiscalyear]", text_search_box
End Sub

Private Sub txt_search_box2_KeyUp(KeyCode As Integer, Shift As Integer)
    SetFilter 1, "[Field2]", txt_search_box2
End Sub

Private Sub txt_search_box3_KeyUp(KeyCode As Integer, Shift As Integer)
    SetFilter 2, "[Field3]", txt_search_box3
End Sub

Private Sub SetFilter(idx As Integer, Field As String, tb As TextBox)
    m_filter(idx) = ""
    If Len(tb.Text) Then m_filter(idx) = Field & " LIKE '*" & tb.Text & "*'"
    Call FilterForm
End Sub

Private Sub FilterForm()
    Dim sFilter As String
    Dim i As Integer
    
    For i = 0 To 2
        If Len(m_filter(i)) Then sFilter = sFilter & " AND " & m_filter(i)
    Next
    
    If Len(sFilter) Then Me.Filter = Mid$(sFilter, 6)
    Me.FilterOn = Len(sFilter)
    
    Dim ctl As TextBox
    Set ctl = Screen.ActiveControl
    ctl.SelStart = Len(ctl.Text)
End Sub

Pat Hartman · Mar 23, 2025

I would use the technique shown by @arnelgp. Every box calls the filter code and the filter code builds the entire filter.

The only difference for me is that I don't actually use form filters since my BE's are almost exclusively SQL Server. Form filters prevent SQL Server from doing the heavy lifting since they always download ALL rows from the server for the form's Recordsource table/query and then filter locally. My forms are always based on a query that includes a where clause which does the filtering so the server does the fetching and only brings down the selected records and never the entire unfiltered recordset. How many records can a user work with at one time anyway?

If your BE is Jet/ACE and will remain that way, I won't confuse you with my code. But if your BE is SQL Server, let me know and I'll post it.

Heide · Mar 23, 2025

theDBguy said:
It may be difficult to explain and may be easier to show it using a demo but what I've done before was used a bunch of TempVars, one for each search term. In the end, the filter ends up being something like: TempVars!Search1 AND TempVars!Search2 AND so on...

Thank you for the help! I'm trying to implement your suggestions, and running into an error message when I try to test the search box. It seems to think that the object name (txt_search_box) exists more than once, which it doesn't. In your example, do you not name the search box objects?

Heide · Mar 23, 2025

Pat Hartman said:
I would use the technique shown by @arnelgp. Every box calls the filter code and the filter code builds the entire filter.

The only difference for me is that I don't actually use form filters since my BE's are almost exclusively SQL Server. Form filters prevent SQL Server from doing the heavy lifting since they always download ALL rows from the server for the form's Recordsource table/query and then filter locally. My forms are always based on a query that includes a where clause which does the filtering so the server does the fetching and only brings down the selected records and never the entire unfiltered recordset. How many records can a user work with at one time anyway?

If your BE is Jet/ACE and will remain that way, I won't confuse you with my code. But if your BE is SQL Server, let me know and I'll post it.

I think it's SQL server...standard MS Access install. I can't find the post you mention above from arnelgp...would a link to it be possible? Thanks!

theDBguy · Mar 23, 2025

Heide said:
Thank you for the help! I'm trying to implement your suggestions, and running into an error message when I try to test the search box. It seems to think that the object name (txt_search_box) exists more than once, which it doesn't. In your example, do you not name the search box objects? View attachment 119065

I do name them. For example: txtSearchFName, txtSearchLastName, etc.

Then in the AfterUpdate of each box, something like:

Code:

TempVars!SearchFirstName = "[FirstName] Like '*" & Me.txtSearchFirstName & "*'"
or
TempVars!SearchLastName = "[LastName] Like '*" & Me.txtSearchLastName & "*'"

And as I said, the actual filtering happens with something like:

Code:

Me.Filter = TempVars!SearchFirstName & " AND " TempVars!SearchLastName

If the searchbox is blank/empty, each TempVar will contain " 1=1 " (I just didn't include that part in the sample code above).

Heide · Mar 23, 2025

Thank you. I must admit to feeling a little (a lot) lost here. I am not a programmer, but need to do things in Access that require programming, so please have patience if my questions seem really basic. At this point, would I be better off to remove all the VBA for the search boxes, and start over following your suggestions?

theDBguy · Mar 23, 2025

Heide said:
Thank you. I must admit to feeling a little (a lot) lost here. I am not a programmer, but need to do things in Access that require programming, so please have patience if my questions seem really basic. At this point, would I be better off to remove all the VBA for the search boxes, and start over following your suggestions?

You might consider posting a sample copy of your db with test data, so we can show you how to apply the posted VBA suggestions.

Pat Hartman · Mar 23, 2025

Heide said:
I think it's SQL server...standard MS Access install. I can't find the post you mention above from arnelgp...would a link to it be possible? Thanks!

#6 but if your BE is SQ!L Server, then you can use the technique of creating a filter but instead create a where clause for your query. Then update the Recordsource with the changed query.

Josef P. · Mar 23, 2025

[OT]

Pat Hartman said:
#6 but if your BE is SQ!L Server, then you can use the technique of creating a filter but instead create a where clause for your query. Then update the Recordsource with the changed query.

However, you can also set the Form.Filter property, as this forwards the criteria to the SQL Server in the same way and only the requested data is transferred.
The statement that all data records are retrieved from the server and only then filtered in Access cannot be confirmed in the SQL Server by observing the XEvent Profiler.
You can of course use Access functions for filtering and thus prevent pre-filtering by the SQL Server, but this would then also have the same effect for a query with the same criteria.

Heide · Mar 23, 2025

Josef P. said:
[OT]

However, you can also set the Form.Filter property, as this forwards the criteria to the SQL Server in the same way and only the requested data is transferred.
The statement that all data records are retrieved from the server and only then filtered in Access cannot be confirmed in the SQL Server by observing the XEvent Profiler.
You can of course use Access functions for filtering and thus prevent pre-filtering by the SQL Server, but this would then also have the same effect for a query with the same criteria.

I think I get it...I'll go to code posted in #6, and start over with the search boxes. One more question, the code I'm using is attached to the OnKeyUp property. Would it be better in OnGotFocus property? Thank you sincerely for all your help.

Heide · Mar 25, 2025

arnelgp said:

you can also do this:

Code:

Dim m_filter1 As String
Dim m_filter2 As String
Dim m_filter3 As String


Private Sub txt_search_box_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
' Search Box
'
'******************************************************************
' Apply filter if text is entered in search box
If Len(txt_search_box.Text) > 0 Then
' Build the filter string
' Need to add to filter string for each field you wish to search
m_filter1 = "[fiscalyear] LIKE '*" & txt_search_box.Text & "*'"
Else
' Clear filter string
m_filter1 = ""
End If

Call FilterForm

End Sub


Private Sub txt_search_box2_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
' Search Box2
'
'******************************************************************
' Apply filter if text is entered in search box
If Len(txt_search_box2.Text) > 0 Then
m_filter2 = "[Field2] LIKE '*" & txt_search_box2.Text & "*'"
Else
' Clear filter string
m_filter2 = ""
End If

Call FilterForm

End Sub


Private Sub txt_search_box3_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
' Search Box3
'
'******************************************************************
' Apply filter if text is entered in search box
If Len(txt_search_box3.Text) > 0 Then
m_filter3 = "[Field3] LIKE '*" & txt_search_box3.Text & "*'"
Else
' Clear filter string
m_filter2 = ""
End If

Call FilterForm

End Sub

Private Sub FilterForm()
Dim ctl As TextBox
Dim sFilter As String
Set ctl = Screen.ActiveControl
If Len(m_filter1) Then
    sFilter = sFilter & " AND " & m_filter1
End If
If Len(m_filter2) Then
    sFilter = sFilter & " AND " & m_filter2
End If
If Len(m_filter3) Then
    sFilter = sFilter & " AND " & m_filter3
End If
If Len(sFilter) Then
    sFilter = Mid$(sFilter, 6)
    Me.Filter = sFilter
    Me.FilterOn = True
Else
    Me.FilterOn = False
End If
With ctl
    .SelStart = Len(ctl.Text)
End If
End Sub

Hi, Pat. I'm using the code suggested here and, after a few false starts, I'm not getting any errors. However, I'm also not getting any results. The data has 624 records, and no matter which criteria I enter, still shows 624 records. I'll post the exact code I'm using below. I've only done the first two fields, so far (if they don't work, the rest won't either, I assume).

Dim m_filter1 As String
Dim m_filter2 As String
Dim m_filter3 As String

Private Sub txt_search_box_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
' Search Box
'
'******************************************************************
' Apply filter if text is entered in search box
If Len(txt_search_box.Text) > 0 Then
' Build the filter string
' Need to add to filter string for each field you wish to search
m_filter1 = "[fiscalyear] LIKE '*" & txt_search_box.Text & "*'"
Else
' Clear filter string
m_filter1 = ""
End If

Call FilterForm

End Sub
Private Sub FilterForm()
Dim ctl As TextBox
Dim sFilter As String
Set ctl = Screen.ActiveControl
If Len(m_filter1) Then
sFilter = sFilter & " AND " & m_filter1
End If
If Len(m_filter2) Then
sFilter = sFilter & " AND " & m_filter2
End If
If Len(m_filter3) Then
sFilter = sFilter & " AND " & m_filter3
End If
If Len(sFilter) Then
sFilter = Mid$(sFilter, 6)
Me.Filter = sFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End If
With ctl
.SelStart = Len(ctl.Text)
End With
End Sub

Private Sub txt_search_box1_KeyUp(KeyCode As Integer, Shift As Integer)
'******************************************************************
'
' Search Box2
'
'******************************************************************
' Apply filter if text is entered in search box
If Len(txt_search_box1.Text) > 0 Then
m_filter2 = "[months] LIKE '*" & txt_search_box1.Text & "*'"
Else
' Clear filter string
m_filter2 = ""
End If

Call FilterForm

End Sub

Gasman · Mar 25, 2025

Walk your code with breakpoints and F8.
Filter string will not start with an AND

Heide · Mar 25, 2025

Gasman said:
Walk your code with breakpoints and F8.

I don't know what you mean. Not one of those terms is familiar to me...sorry.

Gasman · Mar 25, 2025

Heide said:
I don't know what you mean. Not one of those terms is familiar to me...sorry.

Look at the debuuging link in my signature.

Problem with Search Boxes

New member

MVP / VIP

New member

I’m here to help

You've got your good things, and you've got mine.

..forever waiting... waiting for jellybean!

bit cruncher

Super Moderator

New member

New member

I’m here to help

New member

I’m here to help

Super Moderator

Well-known member

New member

New member

Enthusiastic Amateur

New member

Enthusiastic Amateur

Similar threads

Users who are viewing this thread