Partager via

Design for filter favorites functionality in forms

  • Article

Access 2007 introduces a ton of new filtering tools that make it much easier to cut and slice data in different ways. I’m very happy about the new experience and think developers will spend far less time building filtering functionality because of the built in functionality. However, in all releases there are always one or two features round out the scenario that don’t fit into the milestones. One of the ideas we had planned to implement was the notion of filter favorites for any view. The basic idea is to allow users to save common or complex filters and sorts. Unfortunately, that feature was one of the last cuts we made.

Earlier this summer when I was home on paternity leave I had the chance to play around adding filter favorites to our templates. A key goal for the feature was to make it so simple that most users would be successful using and managing filters/sorts.

One of the goals of our templates is that they don’t have VBA code. We want them to run in sandbox mode without requiring users to enable code. The new functionality is all written in macros without VBA code. You can download a working sample from https://clintc.officeisp.net/Blogs/2006/44%20-%20Filter%20Favorites/Filter_Favorite_Sample.zip.

Let me describe how it works. There is a dropdown control in the upper right corner with a command for saving a filter.

The dropdown control provides users with the ability to clear filters, manage filters, and any saved filters. Selecting Clear Filter will remove the forms filter. Selecting any of the other filters will change the current filter of the document.

It is pretty easy to add new filters to the dropdown. Simply filter the list and click on the Add Filter button. This takes a copy of the current filter and sort and saved it in the Filters table. The user is asked to provide a name and description:

The Show details >> link shows the actual filter and sort SQL strings. I believe it is important to hide unnecessary complexity but provide intuitive ways for advanced users to find it. Most users simply don't need to see that kind of gunk.

If you want to add this functionality to your form it is pretty simple. Import the Filters table, Filters macro, and Filter Details form. Copy and paste the dropdown control and Save Filter button to your form.

As I was writing this blog post I realized you can't delete filters without going to the table. We will get that fixed before the templates are updated. You will also notice icons an all the form buttons--they make it all look much nicer.

If anyone wants to make filter favorites work in report browse with custom ribbons--send me the code and I will post it. Anyone have other suggestions how we can make it better?

Comments