Find (Filter) as You Type Controls (Combobox, Listbox, Form) (1 Viewer)

[MajP]

Here is an Find/Filter as You Type (FAYT) class. This allows you to find/filter lists of comboboxes, listboxes, and continuous forms. The class helps create the capability with a single line of code to instantiate the class.
You need to import the class module. Then instantiate a FAYT variable.

1. Auto expands
2. Handles apostrophes
3. Handles international characters as demonstrated by the test case.
4. Can be configured to search anywhere in string or from beginning only
5. Can search a multi column combobox.
6. Handles up and down arrow keys.

 

[cricketbird]

Wow - this is so helpful! I've been trying to implement something like this for ages and this worked so easily! Thank you!

 

[AlekseijDR]

Thank you very much! Just downloaded and works like a charm! It's also very fast, indeed!

Just one question: using FAYT combos configured to find string anywhere in the text, I noticed that if the string I type is the initial of some data in the combobox (e.g. I type Ali), the corresponding item in the combobox is highlighted (Alice Mutton); on the contrary, if the string I type is in the middle of it (e.g. I type Mut), nothing is highlighted in the combobox (of course filter works very well!)
Is there any way to highlight the first filtered item, in this latter case?

Thank you very much,
Carmine

 

[MajP]

I type is the initial of some data in the combobox (e.g. I type Ali), the corresponding item in the combobox is highlighted (Alice Mutton); on the contrary, if the string I type is in the middle of it (e.g. I type Mut), nothing is highlighted in the combobox (of course filter works very well!)
Is there any way to highlight the first filtered item, in this latter case?

I can look at it, but my first guess would be it may not be possible or at least not easy to do. That selection behavior is native to Access. In any combobox if your keystrokes match a selections starting values then a combobox autoselects that value. In this case you are filtering, but not matching. If you type Mut there is no value starting with Mut so nothing is autoselected. I tried selecting the first value, but that then negates all of the other code.

 

[AlekseijDR]

Thank you again for your kind reply! I imagined it, but I also thought you could use some magic!

 

[AlekseijDR]

Hi, sorry to bother you again, but I would have another question: in FindAsYouTypeForm, if, after typing some letters, I hit backspace, there's no new filtering on the resulting text.

Thank you!

 

[MajP]

There are some serious logic bugs that I did not think about. My plan was to handle additional filters that the user may have applied. This makes is way more complicated. So if you add filters by another means this will not work. So give this a try and see if it meets your needs. Replace the current procedure in the class

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 fldName As String
   Dim i As Integer
   TheText = Replace(TheText, "'", "''")
   If mHandleInternationalCharacters Then TheText = InternationalCharacters(TheText)
   If Me.FilterType = ffrm_FromBeginning Then
     strLike = " like '"
   Else
     strLike = " like '*"
   End If
   StrFilter = ""
   For i = 1 To mFieldsToSearch.Count
     fldName = mFieldsToSearch(i)
     If StrFilter = "" Then
           StrFilter = fldName & strLike & TheText & "*'"
      Else
             StrFilter = StrFilter & " OR " & fldName & strLike & TheText & "*'"
      End If
   Next i
  
  ' If mForm.Filter <> "" And mForm.FilterOn = True And StrFilter <> "" Then
  '   StrFilter = "(" & StrFilter & ") ' AND ( " & mForm.Filter & ") "
  ' End If
  'debug.print strFilter & "  In class"
 
  Me.Filter = StrFilter
  getFilter = StrFilter
End Function

 

[AlekseijDR]

It works perfectly, thanks!

 

[AlekseijDR]

Hi, me again! I am experiencing a problem with the NotInList event associated to a FAYT combo. With a "normal" combo I use to open a form, add new data and then requery the combo. With a FAYT combo, I can't manage to get the same result. Do you have any clue?

Thank you as always!

 

[MajP]

It is hard to tell without seeing your code and maybe seeing your db. However, the Class has its own Requery Method. You have to use the requery of the FAYT instance not the requery of the combobox

 

[adhoustonj]

Can you point me in the right direction of modifying the FindAsYouTypeForm class to filter two subforms from the main form?
On the main form load I was calling the class initialize sub but passing in the subform names, yet it would only filter the first subform passed in.

So I made a 2nd class module FindAsYouTypeForm2, and passed the 2nd subform into that one, but it is still only filtering the 1st.

 

[Gasman]

I would expect you need to call for each subform individually?

 

[adhoustonj]

Maybe I need to try passing the main form text box into the initialize call from each subform then. I tried a public sub on each subform that was called from the main form, but still only one would filter.

I need to make a sample db and post, need to remove a lot though.

 

[Gasman]

Maybe I need to try passing the main form text box into the initialize call from each subform then. I tried a public sub on each subform that was called from the main form, but still only one would filter.

I need to make a sample db and post, need to remove a lot though.

Might want to start a new thread in anothet relevant forum, as this is a monitored forum, and causes more work for admins, when it is not really needed?