MS Access VBA trick that can be used to filter a continuous form.
MS Access Search As You Type is a feature that allows your current form to filter the results as you type. This feature will give your program a professional look and makes your searching easy and quick to find results.
Search As You Type
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 mentioned below where Form1 is the name of your form linked to the created query.
Like "*" & [Forms]![Form1]![txtSearch] & "*"
Option Compare Database
Private blnSpace As Boolean
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
5. On change event of the field txtSearch, type the following VBA code:
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:
Private Sub txtSearch_KeyPress(KeyAscii As Integer)
If KeyAscii = 32 Then
blnSpace = True
Else
blnSpace = False
End If
End Sub
Check out MS Access Search As You Type post that explains other way of search by selecting which field column is to search for.
In addition, you might be interested in MS Excel Search As You Type.