Filter Automatically On a Text Box

[dmuroff]

Hi,

I would like to have a filter on a text box that will automatically update the filter everytime a character is entered in the box.
As well, I would like it to filter not on the whole field but any part of the field. For example..If the Product ID is "123456" and "34" is entered in the text box I would like that product to be in the filter.

I have this:

Private Sub txtcode_AfterUpdate()
Dim strFilter As String
strFilter = "1 = 1"
If Len(txtcode) > 0 Then
   strFilter = strFilter & " AND code = '" & txtcode & "'"
End If
Me.Filter = strFilter
Me.FilterOn = True
End sub

What should I do differently?
Thanks!

 

[PaulF]

instead of the AfterUpdate Event, use the KeyPress or KeyDown event.. (remember to set the Form's Key Preview property to YES)

PaulF

 

[dmuroff]

I've tried that one before.
Here's what I got:

Private Sub txtInternalCodeSearch_KeyPress(KeyAscii As Integer)
Dim strFilter As String
strFilter = "1 = 1"
If Len(txtInternalCodeSearch) > 0 Then
   strFilter = strFilter & " AND internalcode = '" & txtInternalCodeSearch & "'"
End If
Me.Filter = strFilter
Me.FilterOn = True
End Sub

It will filter automatically but if I enter two characters for example: '0' and then '1' it will put the '1' before the '0' in the text box. As well, when enter is pressed the text box loses focus and when I try to select the text box and delete the value it loses focus again.

Any suggestions?

 

[PaulF]

I find the best way to do this is using a form with an unbound textbox and have a subform that contains all of the data that I want to view, then like in this example where I enter a value in textbox Text3 and update a subform (sub1) based on the lastname field being equal to what I type in... I start with Like * so that they all show, and then add the values plus an * at the end.. so for your example entering 34 would return 12345 ....

Option Compare Database
Option Explicit
Dim strFilter As String
Dim strCharacters As String
Function fFilter(sKey As Integer)
If sKey = vbKeyBack Or sKey = vbKeyDelete Then
If Len(strCharacters) > 0 Then
strCharacters = Left(strCharacters, Len(strCharacters) - 1)
strFilter = "LastName Like '*" & strCharacters & "*'"
Me![sub1].Form.FilterOn = False
Me![sub1].Form.Filter = strFilter
Me![sub1].Form.FilterOn = True
End If
Else
Debug.Print "Len StrFilter " & Len(strFilter); " "; "Length StrChar " & Len(strCharacters)
strCharacters = strCharacters & Chr(sKey)
strFilter = "LastName Like '*" & strCharacters & "*'"
Debug.Print strFilter
Me![sub1].Form.FilterOn = False
Me![sub1].Form.Filter = strFilter
Me![sub1].Form.FilterOn = True
End If
End Function
Private Sub Form_Open(Cancel As Integer)
strFilter = "LastName Like '*'"
strCharacters = ""
Me![sub1].Form.Filter = strFilter
Me![sub1].Form.FilterOn = True
End Sub

Private Sub Text3_Enter()
If Text3 = "" Then Text3 = strCharacters
End Sub

Private Sub Text3_KeyUp(KeyCode As Integer, Shift As Integer)
Call fFilter(KeyCode)
End Sub


HTH
PaulF