Filter through text box (1 Viewer)

Saltywaffles

New member
Joined
Sep 16, 2019
Messages
2
Hello,

Long time lurker, never had an account until yesterday when I finally could not find the answer that I have been looking for. So for my problem:

I created a table with all of my information, I have a Query based on that table, and I have 2 forms based on that Query. One form is going to show the whole list of all the people in the table and I want to create a search bar that users can type into and it will filter based on what the user is typing. A picture is attached with what I have so far.

In the "Search For" text box, as the user is typing, the "Search Results" box below it will automatically filter and show what has been typed so far. How do I do this? If you need me to upload the Database, let me know!
 

Attachments

June7

AWF VIP
Joined
Mar 9, 2014
Messages
2,687
Use a combobox with AutoExpand set to Yes. Combobox offers best of both textbox and listbox.

If you want to filter listbox as you type each letter in textbox, requires code and is a fairly frequent topic. http://allenbrowne.com/AppFindAsUType.html. Example is filtering a form but could be adapted for listbox.
 
Last edited:

Saltywaffles

New member
Joined
Sep 16, 2019
Messages
2
Thanks for your reply! Unfortunately, I need to have it do what I ask. I will read that link you shared and see what I can do. This database is going to be for end-users who are not the best at using forms such as this. They currently have a program that does what I am asking, and if I can keep it close to that, it will be an easy transition for them.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,705
Thanks for your reply! Unfortunately, I need to have it do what I ask. I will read that link you shared and see what I can do. This database is going to be for end-users who are not the best at using forms such as this. They currently have a program that does what I am asking, and if I can keep it close to that, it will be an easy transition for them.
Hi Saltywaffles. Welcome to AWF! Either links already provided should do what you ask. Please let us know if you get stuck implementing either one into your project. Cheers!
 

MajP

The Dude or uh, His Dudeness, or uh, El Duderino
Joined
May 21, 2018
Messages
2,393
I updated my Find as you type (FAYT) classes to turn any form into a FAYT with a single line of code. There are some features to allow you to define the type of search and to search and specified field or all text fields. This only works with text fields, just because I did not see a lot of utility searching numbers. However in certain cases you can convert numeric fields to text in the sql.

Code to turn a form into a FAYT form
Code:
Dim FAYT_Form As FindAsYouTypeForm

Private Sub Form_Load()
  Set FAYT_Form = New FindAsYouTypeForm
  FAYT_Form.Initialize Me, Me.txtFilter, "ProductName", ffrm_FromBeginning
End Sub
where you pass the form, textbox, field to filter, and type of filter.

Class
Code:
Option Explicit

'Class Module Name: FindAsYouTypeForm
'Purpose: Turn any Form into a "Find As You Type"  Form
'Created by: MajP


Private WithEvents mForm As Access.Form
Private WithEvents mSearchForm As Access.Form
Private WithEvents mTextBox As Access.TextBox
Private mFormFilterType As Long
Private mFieldToSearch As String
Public Enum FormFilterType
  ffrm_AnywhereInString = 0
  ffrm_FromBeginning = 1
End Enum

Private Sub mTextBox_Change()
  Call FilterForm
End Sub
Private Sub FilterForm()
  
  On Error GoTo errLabel
  
  Dim strFilter As String
  mTextBox.SetFocus
  If Not Trim(mTextBox.Text & " ") = "" Then
    'MsgBox getFilter(mTextBox.Text)
    mForm.Filter = getFilter(mTextBox.Text)
    mForm.FilterOn = True
    If mForm.Recordset.RecordCount = 0 Then
      MsgBox "No items matched filter " & vbCrLf & mForm.Filter, vbInformation, "No Items Found"
      mForm.FilterOn = False 'needed to set focus on textbox
      DoEvents
      mTextBox.SetFocus
      mTextBox.Value = Left(mTextBox.Text, Len(mTextBox.Text) - 1)
      FilterForm
    End If
  Else
    'Call unFilterForm
  End If
  mTextBox.SetFocus
  mTextBox.SelStart = Len(mTextBox.Text)
  Exit Sub
errLabel:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  ElseIf Err.Number = 2185 Then
    MsgBox "No item found.", vbInformation, "No Item Found."
    unFilterForm
    Exit Sub
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Private Sub unFilterForm()
  On Error GoTo errLabel
  mTextBox.SetFocus
  mForm.Filter = ""
  mForm.FilterOn = False
  mTextBox.Value = ""
  mTextBox.SetFocus
   Exit Sub
errLabel:
   MsgBox Err.Number & "  " & Err.Description
End Sub
Private Sub Class_Terminate()
    Set mForm = Nothing
End Sub
Public Sub Initialize(TheForm As Access.Form, TheTextBox As Access.TextBox, Optional FieldToSearch As String = "", Optional FilterType As FormFilterType = anywhereinstring)
  On Error GoTo errLabel
  Set mTextBox = TheTextBox
  Set mForm = TheForm
  mFieldToSearch = FieldToSearch
  Me.FilterType = FilterType
  mForm.OnCurrent = "[Event Procedure]"
  mTextBox.OnGotFocus = "[Event Procedure]"
  mTextBox.OnChange = "[Event Procedure]"
  Exit Sub
errLabel:
 MsgBox Err.Number & " " & Err.Description
End Sub

Private Function getFilter(ByVal TheText As String) As String
   'To make this work well convert all field in the listbox to string
   'Example:  strDateDue: cstr(dtmDueDate)
   Dim strFilter As String
   Dim strLike As String
   Dim rs As DAO.Recordset
   Dim fld As DAO.Field
   TheText = Replace(TheText, "'", "''")
   If Me.FilterType = ffrm_FromBeginning Then
     strLike = " like '"
   Else
     strLike = " like '*"
   End If
   Set rs = mForm.Recordset
   If mFieldToSearch = "" Then
      For Each fld In rs.Fields
        If fld.Type = dbMemo Or fld.Type = dbText Then
          If strFilter = "" Then
             strFilter = fld.Name & strLike & TheText & "*'"
          Else
             strFilter = strFilter & " OR " & fld.Name & strLike & TheText & "*'"
          End If
        End If
      Next fld
   Else
      strFilter = mFieldToSearch & strLike & TheText & "*'"
   End If
   getFilter = strFilter
End Function
Public Property Get FilterType() As FormFilterType
  FilterType = mFormFilterType
End Property
Public Property Let FilterType(ByVal TheFilterType As FormFilterType)
  mFormFilterType = TheFilterType
End Property
Public Property Get FieldToSearch() As String
  FieldToSearch = mFieldToSearch
End Property
Public Property Let FieldToSearch(ByVal theFieldToSearch As String)
  mFieldToSearch = theFieldToSearch
End Property
Public Sub SearchAllFields()
  mFieldToSearch = ""
End Sub
A filter form is pretty easy to do without a class, but this is reusable. The combos and lists are more involved to make them generic. The demo has listbox, combobox, and form.
 

Attachments

MajP

The Dude or uh, His Dudeness, or uh, El Duderino
Joined
May 21, 2018
Messages
2,393
For those looking at the class and seeing some strange gyrations on setting focus to the textbox, there is a very strange access behavior. If you have a textbox for typing the filter on the form outside of the detail section and your filter returns no records the textbox loses focus. You cannot set focus through code and therefore cannot get the text property. Therefore I have to first unfilter to set focus and read the text.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom