Creating a true dynamic search in Microsoft Access

Hi there ,
So I was working on an access database and I wanted to implement a search box that updates a subform dynamically as the user enters text. I searched online a lot and all i found was made using really ugly VBA code to set a search filter manually. I will show you how to do this using two VBA lines , a temp variable and a query. ENJOOYYY!!







Here is my form layout : 

 

Now , the steps

1st : Make a query that displays the search results

Nothing fancy here, i just make a query showing all results unfiltered , typically what you would have if the search box has nothing in it.

2nd : Handle the OnChange event of the search box

So i create an [event procedure] and in the OnChange event I do the following

Private Sub txtSearch_Change() 

TempVars!SearchText = txtSearch.Text 

DoCmd.Requery "sf_SearchResults" 

End Sub 

The Trick here : I depend on the tempvars which is an access structure that holds temporary variables.
The next thing is to requery the subform and this is what I do by calling the DoCmd.Requery function

3rd : Create a search criteria which depends on the temporary variable just set

1. Add a new column.


2. Open The Expression Editor : Right click and choose build


3. Finally you just enter an expression that will do the filtering for you , for example:

InStr([Eng Stud Name],[TempVars]![SearchText])

and in the criteria

<>0

Ta daaa !! here is an animated gif of the search in action :