Combo Filter as you type individual record (1 Viewer)

dgreen

Member
Local time
Today, 13:07
Joined
Sep 30, 2018
Messages
397
Need help replicating a filter as you type on a combo box that is being used at the record level. Example: Instead of having an open text box for the prefix (e.g. Mr. Mrs. Ms. Dr.), I'm using a combo box that looks up from a reference table. I want to be able to type the letter "r" in the combo box and have it filter out Ms. and showing the remaining values. Once I make a selection store the selected value in the Name table.
Issue: When I add a new value in Combo4 the other rows above clear out if they don't match the value I just typed into the cell. Something likely with the RowSource in the below formula. Do I have something out of sequence or a flawed formula?
What I think I'm trying to do: 1) If Prefix value populated w/ value in t_Name THEN show the matching value in t_ref_Prefix 2) If Combo4 is Blank / Null THEN then open Combo4 and show all values in t_ref_Prefix so a value can be selected. 3) If user is typing text into Combo4 THEN filter on change using * on both sides of the typed value.

Code:
Option Compare Database
Option Explicit

Private Sub Combo4_Change()
'https://stackoverflow.com/questions/48133260/display-records-in-access-db-combobox-on-any-text-typed-by-user
'test number of characters entered - if greater then 0 then assign rowsource
    
If Len(Me.Combo4.Text) > 0 Then
    'set the rowsource to match user search criteria
     Me.Combo4.RowSource = "SELECT * FROM t_ref_Prefix WHERE Prefix LIKE '*" & Me.Combo4.Text & "*'"
    'show the search in real-time
     Me.Combo4.Dropdown
Else
    'set to no
     Me.Combo4.RowSource = "SELECT t_ref_Prefix.auto, t_ref_Prefix.prefix, t_ref_Prefix.sort FROM _    
       t_ref_Prefix ORDER BY t_ref_Prefix.sort, t_ref_Prefix.prefix"
End If
End Sub
 

Attachments

  • Database3.accdb
    608 KB · Views: 154

jdraw

Super Moderator
Staff member
Local time
Today, 14:07
Joined
Jan 23, 2006
Messages
15,364
I think your attempt to streamline the selection of a prefix is convoluted and likely unnecessary. Why not just let the user/data entry person select from the combo directly.
If the value that is needed is not already in the combo, then study this video for a simple solution.
Good luck.
 

dgreen

Member
Local time
Today, 13:07
Joined
Sep 30, 2018
Messages
397
I'm trying to learn with a very simple example. I plan on using this for more complex lookup tables with thousands of records.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:07
Joined
May 21, 2018
Messages
8,463
If I understand you correctly, you want two things. A find as you type combo box on a continuous form. If that is the case, there is 2 things to overcome. Find as you type, and the ability to do this on a continuous form. The latter is doable but problematic. The issue is that there is only one combobox, the rest are painted on the form. If you change the rowsource on 1 you change it on all. So there are tricks to work around this. I have code to do the Find As You Type and the trick to make this work on a continuous form. There are lots of threads on how to do a combobox on a continuous where you change the rowsource. But yes. If you change the rowsource of a combobox on a continuous form it will change all the rowsources. You will need a trick to do this.

The attached demo is a find as you type on a continuous form. The continuous form trick used a textbox over top of the combobox displaying the same information. The fayt uses a class module, but that is not required (just a lot easier and a lot more functionality).
 

Attachments

  • ContinousFAYT.accdb
    528 KB · Views: 138
  • FAYT_Prefix.accdb
    844 KB · Views: 156
Last edited:

Users who are viewing this thread

Top Bottom