Forms:Filtering Tables

[akn112]

Hi all, I have met yet another challenge. Attached i have a database with a form that has a table on the left and a text field on the right. What i want to do is to make the text field the filter for the field "names" in the table.

for example, it defaults as blank so the table shows everything. But if i type A into the text field, the table shows only names starting with A. If i type in Al, then the table will show only names starting with Al...etc... Is there any way to do this? T.T I've tried playing with the control source but can't quite seem to get it.

[geekgirlau]

Private Sub txtFilter_BeforeUpdate(Cancel As Integer)
Dim strSQL As String


strSQL = "SELECT * FROM Table1"

If Nz(Me.txtFilter, "") <> "" Then
' filter data
strSQL = strSQL & " WHERE [Name] Like """ & Me.txtFilter & "*"""
End If

Me.Table1_subform1.Form.RecordSource = strSQL
Me.Table1_subform1.Requery
End Sub


By the way, be very careful with the names of your fields and controls that you don't use reserved words in Access. I've changed the name of the filter text box to "txtFilter", as "Filter" is a property of the form. For the same reason, "Name" is the worst possible field name to use, as "Name" is a property of almost every object.