Search As You Type

Here is a VBA trick that can be used to filter a continuous form. The steps are:

1.       Create a Query with all of the required fields.

2.       Under the criteria of the field which will be used for looking up the data, insert this expression: 

Like "*" & [Forms]![Form1]![txtSearch] & "*"

Where Form1 is the name of your form linked to the created query.

3.       In a continuous form of “form1”, create a textbox and name it “txtSearch” at the Form Header.

4.       At the top of VBA screen, ensure the existence of the following code

VBA

Option Compare Database

Private blnSpace As Boolean

5.       On change event of the field txtSearch, type the following VBA code:

VBA

Private Sub txtSearch_Change()
If blnSpace = False Then
 Me.Requery
 Refresh
 txtSearch.SetFocus
 txtSearch.SelStart = Len(Me.txtSearch.Text)  
End If
End Sub

6.       On key Press event of the field txtSearch, type the  following VBA Code:

VBA

Private Sub txtSearch_KeyPress(KeyAscii As Integer)
If KeyAscii = 32 Then
  blnSpace = True
Else
  blnSpace = False
End If
End Sub