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








3 comments:

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

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

    ReplyDelete
  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!

    ReplyDelete

I'd like to read your comments

Popular