467,218 Members | 1,013 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,218 developers. It's quick & easy.

Filter as you type combobox in recordset

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.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Combo4_Change()
  5. 'https://stackoverflow.com/questions/48133260/display-records-in-access-db-combobox-on-any-text-typed-by-user
  6. 'test number of characters entered - if greater then 0 then assign rowsource
  7.  
  8. If Len(Me.Combo4.Text) > 0 Then
  9.     'set the rowsource to match user search criteria
  10.      Me.Combo4.RowSource = "SELECT * FROM t_ref_Prefix WHERE Prefix LIKE '*" & Me.Combo4.Text & "*'"
  11.     'show the search in real-time
  12.      Me.Combo4.Dropdown
  13. Else
  14.     'set to no
  15.      Me.Combo4.RowSource = "SELECT t_ref_Prefix.auto, t_ref_Prefix.prefix, t_ref_Prefix.sort FROM _    
  16.        t_ref_Prefix ORDER BY t_ref_Prefix.sort, t_ref_Prefix.prefix"
  17. End If
  18. End Sub
Attached Files
File Type: zip Database3.zip (33.0 KB, 25 views)
Nov 30 '19 #1
  • viewed: 1770
Share:

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by sfauchille@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.