By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,581 Members | 1,966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,581 IT Pros & Developers. It's quick & easy.

Filter as you type combobox in recordset

P: 1
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, 5 views)
1 Week Ago #1
Share this question for a faster answer!
Share on Google+

Post your reply

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