Friday, July 5, 2013

Creating a true dynamic search in Microsoft Access

Hi there ,
logoSo 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

image 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


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



  1. Can you please post a video tutorial? or upload your access file?

  2. Why don't you tell me the part you're stuck at ? .. or mail me , It's .
    Sorry for the delayed reply :)

  3. I had a couple questions. First is the 2nd step in what the "sf_SearchResults" statement points too. The second question is in the third step, on where the add a new column goes(ie, in the query, report, or table).
    Thanks for posting this it is what I have been looking for for weeks!


I'd like to read your comments