Solved Help with Search boxes on form! (1 Viewer)

slharman1

Member
Local time
Today, 02:09
Joined
Mar 8, 2021
Messages
467
I have a continuous form with multiple columns and unbound text fields in the header for each field for search boxes and they all work fine with the code below in the on change event. What I would really like is to have the form filter the records based on the text in all of the unbound text boxes.
So when I type in a number in the text box for the quote number field AND type in, let's say, apartial description in another of the search boxes the form gets filtered based on both (or all) of my search boxes.
Any help would be appreciated.
Thanks

Code:
Private Sub txtNumberFilter_Change()

Dim strFilter As String
    On Error GoTo ErrHandler
    If Me.txtNumberFilter.Text <> "" Then
        strFilter = "[QuoteNumber] Like '*" & Me.txtNumberFilter.Text & "*'"
        Me.Filter = strFilter
        Me.FilterOn = True
    Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
    With Me.txtNumberFilter
        .SetFocus
        .SelStart = Len(Me.txtNumberFilter.Text)
    End With
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation

End Sub

EDITED BY THE_DOC_MAN TO INSERT CODE TAGS. NO OTHER CHANGES TO TEXT OR CODE.
 
Last edited by a moderator:

Ranman256

Well-known member
Local time
Today, 03:09
Joined
Apr 9, 2015
Messages
4,339
make a continuous form that shows all records.
In the header, put unbound controls for the user to enter search criteria.
When user clicks the Find button, Test all controls for a possible filter then build the where clause:

Code:
sub btnFind_click()
sWhere = "1=1"
if not isnull(cboState) then sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(cboGender) then    sWhere = sWhere & " and [Gender]='" & cboGender & "'"

'then filer
if sWhere = "1=1" then
    me.filterOn = false
else
   me.filter = sWhere
   me.filterOn = true
endif
end sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:09
Joined
May 21, 2018
Messages
8,463
You may find this interesting

 

slharman1

Member
Local time
Today, 02:09
Joined
Mar 8, 2021
Messages
467
make a continuous form that shows all records.
In the header, put unbound controls for the user to enter search criteria.
When user clicks the Find button, Test all controls for a possible filter then build the where clause:

Code:
sub btnFind_click()
sWhere = "1=1"
if not isnull(cboState) then sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(cboGender) then    sWhere = sWhere & " and [Gender]='" & cboGender & "'"

'then filer
if sWhere = "1=1" then
    me.filterOn = false
else
   me.filter = sWhere
   me.filterOn = true
endif
end sub
Will this work dynamically? I do not want a find button.
Right now when I type a character in one of the boxes the form instantly filters based on the input, I would like the same thing to happen in any/all of the boxes, so when I type a character in the desc box and type a character in the job name box i would like the for to filter based on the selections in both boxes.
I hope this is making sense.
 

Isaac

Lifelong Learner
Local time
Today, 00:09
Joined
Mar 14, 2017
Messages
8,738
That might slow the interface down
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:09
Joined
May 21, 2018
Messages
8,463
If you want multiple textboxes that find as you type there are a couple of extra steps.
1. You have to do this on the change event.
2. During the change event what you see in the text box is not saved yet to the value of the textbox. If you type ABC only AB is saved.
3. You never want to work with the text property because you only can work with this when the control has focus. So the extra step is to force the save when the control has focus and before you try to save
ctrl.value = ctrl.text
4. The other PITA is that when you filter your selection is not longer at the end of the character you typed so you have to add code to reset it.

This is just to explain why in the example you end up with all this extra code for textboxes when doing it as you type. If you had a button these problems are simplified.

Here is three textboxes. Name, Country, State

Code:
Public Function GetFilter() As String
  'need variable for each partial filter
  Dim strName As String
  Dim strState As String
  Dim strCountry 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
 
  If Not (Me.txtName & "") = "" Then
    strName = "Full_Name like '*" & Me.txtName & "*'"
  End If
 
  If Not (Me.txtState & "") = "" Then
    strState = "State like '*" & Me.txtState & "*'"
  End If
 
  If Not (Me.txtCountry & "") = "" Then
    strCountry = "Country like '*" & Me.txtCountry & "*'"
  End If
 
  strFilter = CombineFilters(AndOr, strName, strState, strCountry)
 
  GetFilter = strFilter
  'need code for each combo. Just copy and past. Need to handle dates and strings

End Function

Private Function FilterForm()
  Dim strFilter As String
  Dim AC As Access.Control
 
  'MsgBox strFilter
  Set AC = Me.ActiveControl
  If AC.Name = "txtName" Or AC.Name = "txtState" Or AC.Name = "txtCountry" Then
    'MsgBox AC.Name
    AC.Value = AC.Text
  End If
 
  strFilter = GetFilter
  Me.txtFilter = strFilter
  If txtFilter <> "" Then
    Me.Filter = strFilter
    Me.FilterOn = True
  Else
    Me.Filter = ""
    Me.FilterOn = False
  End If
 
  If AC.Name = "txtName" Or AC.Name = "txtState" Or AC.Name = "txtCountry" Then
    'MsgBox AC.Name
    AC.SelStart = AC.SelLength
  End If
End Function
Private Sub cmdClear_Click()
 Me.txtName = Null
 Me.txtState = Null
 Me.txtCountry = Null
 FilterForm
End Sub

Private Sub frmAndOr_AfterUpdate()
  FilterForm
End Sub

Here is the code to combine multiple filters
Code:
Public Enum CombineFilterType
  ct_And = 0
  ct_OR = 1
End Enum

Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
  Dim FilterCombiner As String
  Dim i As Integer
  Dim strOut As String
 
  If And_Or = ct_And Then
    FilterCombiner = " AND "
  Else
    FilterCombiner = " OR "
  End If
 
  For i = 0 To UBound(Filters)
    If Filters(i) <> "" Then
      If strOut = "" Then
        strOut = Filters(i)
      Else
        strOut = strOut & FilterCombiner & Filters(i)
      End If
    End If
  Next i
  CombineFilters = strOut
End Function

With only 1 or 2 controls you can do this directly in the query. After that it becomes a PITA and this is far easier.

I do not really do it this way, because in this db you will see other functions to make this even far easier.

See form SimpleSearch with the three textboxes. Not the default.
 

Attachments

  • MultiFilterLight.accdb
    1.2 MB · Views: 609

slharman1

Member
Local time
Today, 02:09
Joined
Mar 8, 2021
Messages
467
If you want multiple textboxes that find as you type there are a couple of extra steps.
1. You have to do this on the change event.
2. During the change event what you see in the text box is not saved yet to the value of the textbox. If you type ABC only AB is saved.
3. You never want to work with the text property because you only can work with this when the control has focus. So the extra step is to force the save when the control has focus and before you try to save
ctrl.value = ctrl.text
4. The other PITA is that when you filter your selection is not longer at the end of the character you typed so you have to add code to reset it.

This is just to explain why in the example you end up with all this extra code for textboxes when doing it as you type. If you had a button these problems are simplified.

Here is three textboxes. Name, Country, State

Code:
Public Function GetFilter() As String
  'need variable for each partial filter
  Dim strName As String
  Dim strState As String
  Dim strCountry 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

  If Not (Me.txtName & "") = "" Then
    strName = "Full_Name like '*" & Me.txtName & "*'"
  End If

  If Not (Me.txtState & "") = "" Then
    strState = "State like '*" & Me.txtState & "*'"
  End If

  If Not (Me.txtCountry & "") = "" Then
    strCountry = "Country like '*" & Me.txtCountry & "*'"
  End If

  strFilter = CombineFilters(AndOr, strName, strState, strCountry)

  GetFilter = strFilter
  'need code for each combo. Just copy and past. Need to handle dates and strings

End Function

Private Function FilterForm()
  Dim strFilter As String
  Dim AC As Access.Control

  'MsgBox strFilter
  Set AC = Me.ActiveControl
  If AC.Name = "txtName" Or AC.Name = "txtState" Or AC.Name = "txtCountry" Then
    'MsgBox AC.Name
    AC.Value = AC.Text
  End If

  strFilter = GetFilter
  Me.txtFilter = strFilter
  If txtFilter <> "" Then
    Me.Filter = strFilter
    Me.FilterOn = True
  Else
    Me.Filter = ""
    Me.FilterOn = False
  End If

  If AC.Name = "txtName" Or AC.Name = "txtState" Or AC.Name = "txtCountry" Then
    'MsgBox AC.Name
    AC.SelStart = AC.SelLength
  End If
End Function
Private Sub cmdClear_Click()
Me.txtName = Null
Me.txtState = Null
Me.txtCountry = Null
FilterForm
End Sub

Private Sub frmAndOr_AfterUpdate()
  FilterForm
End Sub

Here is the code to combine multiple filters
Code:
Public Enum CombineFilterType
  ct_And = 0
  ct_OR = 1
End Enum

Public Function CombineFilters(And_Or As CombineFilterType, ParamArray Filters() As Variant) As String
  Dim FilterCombiner As String
  Dim i As Integer
  Dim strOut As String

  If And_Or = ct_And Then
    FilterCombiner = " AND "
  Else
    FilterCombiner = " OR "
  End If

  For i = 0 To UBound(Filters)
    If Filters(i) <> "" Then
      If strOut = "" Then
        strOut = Filters(i)
      Else
        strOut = strOut & FilterCombiner & Filters(i)
      End If
    End If
  Next i
  CombineFilters = strOut
End Function

With only 1 or 2 controls you can do this directly in the query. After that it becomes a PITA and this is far easier.

I do not really do it this way, because in this db you will see other functions to make this even far easier.

See form SimpleSearch with the three textboxes. Not the default.
Holy Cow MajP, I appreciate all of this but it will take me a while to 'decipher' it all.
I am sure I will hve more questions coming but it might be a while.
Thank again!!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:09
Joined
May 21, 2018
Messages
8,463
Holy Cow MajP, I appreciate all of this but it will take me a while to 'decipher' it all.
I am sure I will hve more questions coming but it might be a while.
Thank again!!
Good luck, but be advised you have added a layer of complexity by having multiple controls that filter as you type. That little difference from using a button to having it occur when you type does add a lot more code.

If I was doing a find as you type I would have a single text box, but then a option group next to it to choose with fields to filter. I would set it up to allow one or more. This actually requires less code and is more flexible.
 

slharman1

Member
Local time
Today, 02:09
Joined
Mar 8, 2021
Messages
467
Good luck, but be advised you have added a layer of complexity by having multiple controls that filter as you type. That little difference from using a button to having it occur when you type does add a lot more code.

If I was doing a find as you type I would have a single text box, but then a option group next to it to choose with fields to filter. I would set it up to allow one or more. This actually requires less code and is more flexible.
It is starting to sound like maybe i shouldn't try to accomplish this.
is that kind of what you are saying?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:09
Joined
May 21, 2018
Messages
8,463
No. You should be able to easily modify what i gave you. Just saying you picked a harder case to work with.
 

slharman1

Member
Local time
Today, 02:09
Joined
Mar 8, 2021
Messages
467
Good luck, but be advised you have added a layer of complexity by having multiple controls that filter as you type. That little difference from using a button to having it occur when you type does add a lot more code.

If I was doing a find as you type I would have a single text box, but then a option group next to it to choose with fields to filter. I would set it up to allow one or more. This actually requires less code and is more flexible.
MajP, I like the one named Simple filter
No. You should be able to easily modify what i gave you. Just saying you picked a harder case to work with.
Cool because I really like the way it works. I just want to get rid of the and/or buttons and have it always be “and”. I think it needs some kind of error checking though because I get a pop up error when I delete all text from one of the search boxes.
many ideas on that?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:09
Joined
May 21, 2018
Messages
8,463
I think it needs some kind of error checking though because I get a pop up error when I delete all text from one of the search boxes.
many ideas on that?
Need to reset the focus
Code:
If AC.Name = "txtName" Or AC.Name = "txtState" Or AC.Name = "txtCountry" Then
    'add below
    AC.SetFocus
    AC.SelStart = AC.SelLength
 

Users who are viewing this thread

Top Bottom