Text field for filtering data (search for matching records with each keystroke) in MS Access

Question

Can someone help me understand why this code does not produce the expected results? I have a form with a TxtVendorSearch text box.

When the user begins to enter text in this text box, I would like him to start filtering the results as a form subdirectory.

I start with a completely filled out data sheet, and I'm not sure why the filter skipped all this, as soon as I start to introduce the right filter, which should leave the results.

Private Sub TxtVendorSearch_KeyPress(KeyAscii As Integer) Dim str1 As String str1 = "[VendorID] LIKE '*" & Me.TxtVendorSearch.Value & "*' " Me!subOrderDS1.Form.Filter = str1 Me!subOrderDS1.Form.FilterOn = True End Sub 

Answer 1

I had a similar problem, and I searched the Internet for the keyword "find as your type" AND "ms access"

I found this great article . hope it solves your problem.

NB This article also contains the source code to use.

Answer 2

Do not use the KeyPress event, you will not be able to easily restore the actual input data from the event handler itself (when the event is triggered, the key just pressed is not yet added to the text field text, meaning that you are always short with the last key press).

Instead, use the KeyDown and use the text < .Text instead of .Value .
.Value set only after the focus is removed from the window.

Thus, your code can simply be rewritten as (make sure your KeyDown event is set in text field events on the form):

 Private Sub TxtVendorSearch_KeyDown(KeyCode As Integer, Shift As Integer) ' If there is no filter criteria, disable filtering ' If Len(Trim(TxtVendorSearch.Text)) = 0 Then subOrderDS1.Form.FilterOn = False Exit Sub End If ' Do nothing if user hits RETURN or TAB ' If KeyAscii < 32 Then KeyCode = 0 Exit Sub End If Dim str1 As String str1 = "[VendorID] LIKE '*" & Trim$(TxtVendorSearch.Text) & "*'" subOrderDS1.Form.Filter = str1 subOrderDS1.Form.FilterOn = True End Sub 

I used Trim() to remove any start and end white space that the user could type.

Last: you do not need to use Me. or Me! from the form code itself.
It doesn't hurt if you do this, but it makes things a little less legible without adding anything to the code.