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 : 

  MainFormLayout

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.
image

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

<>0



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

Capture








Popular