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.
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