Access: Returning Filtered Data on Form

I like spending time in the Access newsgroups. Trying to help people out finding a solution for a problem they are struggling with. Sam asked a question about a search option in a form header and showing the results of the query to the detailsection of the form. After a couple of posts we are almost there. But sometimes it's better to show the image. So this post is for Sam explaining what we are talking about in the thread of the newsgroup. So here we go Sam.

Images (click to enlarge...)

I'll add several images for clarity here and try to explain what the goal should be. Remember Sam that you have to tune this to your own situation because I don't have your data...

I've based the example on a single simple table called Customers:

Now it's time to take the next step: create the query: "qGenerics". In your sample you can run the sql which you have create in your VBA. This will add the SQL statement to the query.

When the query is added it's time to create the form and it's functionality you are looking for see the next page. My form is called "form2"

You create the form by using a wizard or create one yourself. I used Access 2003 but the funtionality in 2007 is nearly the same. Use a wizard or create a blank form with the "qGenerics" as recordsource.

The next couple of steps are important they are based on the view you would like:

The images should be followed top down so after the new form image we take the next step: you have to set the recordset. If done by the wizard this is taken care of for you. When you don't use the wizard you'll have to chose it yourself.

The fieldlist will show up next to the design of the form. Add a formheader and a formfooter to the form. Drag the fields to the detailsection of the form. Rearrange them the way you like. I often use the tabular layout which is pretty familiar with most people.

As you can see i added a form header and a for footer. I've taken the labels and added them to the form header. When you set your form to "continuous" this will prevent the labels being showed with every record. After that i've narrowed the detail section to the smalles

possible size. The records will be shown as a list so we can save the space and the records will be show tightly under one another.

Now set the form to "continuous" in the form properties.

Add a search text box to the header of the form (regular textbox).

When the textbox is added set an event to the afterupdate event. In VBA add the following code: see next pincture.

The VBA code is fairly straight forward. I set the DB to the currentdb. set the strSQL to be a regular select statement. For conveniance i added an if statement to check something has been typed in the searchbox. If not then all the records will be displayed. If something is typed in the strSQL is adapted accordingly.

I remove the recordsource by setting it to "" and the resetting it by the newly filled "qGeneric". Because this action will paint the form i set the application echo to false, perform the action and set the application echo to true again. This prevents the painting of the form and will show the filtered set directly. Which reminded me that you can achieve the same result by just using a filter. I've added a double click event to the search text box. This will trigger the filter which i set based on the parameter entered in the searchbox. In this case it's the 'city' parameter.

Now let's see the results:

When the textbox is empty and the user doubleclicks on the searchbox all the records will be shown. If something is typed in the searchbox and enter is pressed the recordset will be adapted and shown accordingly. Remember you have to add some nice errorhandling to top things of after all we are dealing with users and you'll never know what a user is up to ;-)

Final result

before and after the search has been set..

Sample database can be found here: