Text Box filter

[patrickswallace]

Hi,
I would like to create a filter for a form...I have a unbound text box
named "formfilter" and the goal is to filter the field "LastName"...I
would like the user to only type in the first couple letters and using
the On Lost Focus event filter the form for those individuals whose
name begins with those letters...I am not very experienced with VBA but
realize this procedure probably needs to be done with VBA.

 

[Allen Browne]

Use the AfterUpdate event procedure of your *unbound* text box named
formfilter, like this:

Private Sub formfilter_AfterUpdate
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'Save record
If IsNull(Me.formfilter) Then 'Show all records
Me.FilterOn = False
Else
strWhere = "[LastName] = """ & Me.formfilter & """"
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Or, you might prefer this one:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
This allows you to choose the field to filter on (e.g. LastName), and then
filters the form with each keystroke you type. You don't need to write any
code to set it up: just copy'n'paste, and set one property.

 

[Guest]

To filter on the initial characters of a name you'll need to change one line
of Allen's code slightly to:

strWhere = "[LastName] Like """ & Me.formfilter & "*"""

Ken Sheridan
Stafford, England