The Most Powerful form Filter and Requires almost No Code

[MajP]

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs.

The Most Powerful form Filter and Requires almost No Code

NOTE: See updated code below. I recommend using the updated class module that @arnelgp modified. It further simplifies building a search form and addresses some bugs...

www.access-programmers.co.uk

I figured this was worth reposting, because i see so many people struggling with wanting to filter a form. I have seen some painful attempts. I doubt without writing pages and pages of code you can replicate the following to filter a continuous form. The date filter alone would be 1000s of lines and multiple forms. Here is my form with every field having a complex multiple select option filter.

When choosing a text field the filters available

When selecting a date field the choice are limitless

I added a calculated year field

So how much code to build this form with all this functionality. According to Word 40 lines of code, but only 118 words.

Option Compare Database
Option Explicit
Private SelectedButton As Variant
Public Function FilterForm()
  On Error GoTo errlbl
  Set SelectedButton = ActiveControl
  Me.Controls(ActiveControl.Tag).SetFocus
  Me.Recordset.MoveFirst
  DoCmd.RunCommand acCmdFilterMenu
  Exit Function
errlbl:
  If Err.Number = 3021 Then
    MsgBox "No records Returned", vbInformation
    Me.Filter = ""
    Me.FilterOn = False
    SetButtons
  Else
   MsgBox Err.Number & " " & Err.Description
  End If
End Function

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
  SetButtons
End Sub

Private Sub Form_Load()
  DoCmd.Maximize
End Sub
Private Sub SetButtons()
  Dim cmd As Access.Control
  For Each cmd In Me.Controls
    If cmd.ControlType = acCommandButton Then
      If cmd.Tag <> "" Then
          If InStr(Me.Filter, cmd.Tag) > 0 Then
            cmd.Picture = "Filter"
          Else
            cmd.Picture = "Down"
          End If
       End If
    End If
  Next cmd
End Sub

 

[MajP]

To caveat. All I did was make a nice simpler wrapper to the very powerful filter features that come in Access. This just makes it more intuitive and more aesthetic.

 

[Minty]

Really neat, does it work in an Accde?
I haven't tried just wondered if you had, as they are disabled normally?

 

[MajP]

@Minty, I have not tried.

 

[MajP]

To demo some more utility, based on another thread about reporting selected records. I added a simple open report button based on the filter. Now you can select the records to print using the product name or any other field. That functionality would alone require a lot of code.

 

[MajP]

Really neat, does it work in an Accde?

Yes it does.

 

[gemma-the-husky]

@MajP

That really is slick. I didn't realise you could invoke the full filter. In another thread I added a simple "filter by selection", but yours is a whole new level. That does/should work in runtime, because although you won't get the option in the command bar at the top, you can still add the options programmatically. A user of mine yesterday found she couldn't sort a form column because she didn't get the right click menu, and that's in your filter menu, I see.

One point - I will try it out, but can you confirm how you are invoking the menu? do you add a button, or is it one of the events? do you have to add it to every control, and every form?

 

[Dreamweaver]

Very Nice work @MajP I might give it a try with a recordset of mine about 270K Entries

 

[MajP]

@gemma-the-husky and @MickJav,
This is one of those things that is hidden in plain sight and is so obvious that I never even thought of doing it and I guess others as well. A newbie user on the forum said they liked using datasheets because of all the sorting and filter features and wondered if they could do it in a continuous form. Like me they liked the aesthetics of a tabular form. They did not like having the extra step of clicking the field and then going up to the menu, especially if the form was maximized. I am so comfortable rolling my on filters and sorts that I never thought about simply using the available menu to filter by form, filter by selection, sort by selection etc.

So all this code does is set focus to the desired control and calls the filter menu
DoCmd.RunCommand acCmdFilterMenu

The only thing I did that was slick was use a single function to handle the click events which eliminated lots of individual event procedures and leveraged the tag property to avoid hardwiring a lot of code with control names.

It is so obvious, that I am amazed no one else thought of it, and took a newbie to ask if it was possible. I am using it all the time now. Then You can take the filter and pass it to a form, report, or query.

 

[arnelgp]

actully i made a Class out of it.
it correctly handles (bug i found on your code), Unfiltering using the Ribbon.

 

[Gasman]

@arnelgp
I get an error on opening?

Is this due to my only having 2007 again?

 

[gemma-the-husky]

So is the action coded to a standalone button on the form?

 

[AndyC88]

actully i made a Class out of it.
it correctly handles (bug i found on your code), Unfiltering using the Ribbon.

MajP, Arnelgp, thanks for your work - this looks really useful. How does this work with the modules? Is it a case of simply copying this into a new database or do you have to alter the code? Many thanks!

 

[gemma-the-husky]

it's not necessarily a "bug" to have an easy unfilter option.

 

[MajP]

I added a couple of additions to @arnelgp Class verions. The class version further simplifies using the filters. The problem that people may run into is that the images used in the combobox (down selector and filter) are stored in the Image Gallery as "shared" images. This will not work in older versions of Access. It was done this way for portability. If you are using older versions of access you will have to come up with a work around. Arnelgp left the default names of the images which was a little confusing, so I simply renamed them: FilterSelector and ClearFilter. Once in the image gallery you can load a picture simply by its name. So in the code I changed Command37 to FilterSelector...

@Gasman the image gallery issue is the problem you are having.

Another problem happens if you import just this form and class into a new database. All the images do not come over if they are not visible. So the FilterSelector will come over but not the ClearFIlter. So I added a form that you can import. This will cause the gallery to have both images. I could have just put the filter image on the main form hidden as well.

@AndyC88,
Well written code is a black box where you understand the inputs and the outputs and not necessarily the inner workings. Using these modules you do not change or add code you use the inputs. In the refined class module version you import the both class modules and also bring in the form with the two images. Simply create the form and add your comboxes with the down FilterSelector. Then all you do is the following code in your form.

'At top of form declare the class
Private CCF As classCommandFilters

Private Sub Form_Load()
    Set CCF = New classCommandFilters
   'add each command button to the class
   With CCF
   
        .add Me!YourCommandButtonName, "NameOfField"
    
        .add Me!Command60, "drawing"
        .add Me!Command61, "spool"
        .add Me!Command62, "MaxSize"
        .add Me!Command63, "heatnumber"
        .add Me!Command64, "length"
        .add Me!Command65, "paintcode"
        .add Me!Command66, "storagegrid"
        .add Me!Command67, "InventoryQty"
        .add Me!Command68, "InventoryDate"
        .add Me!Command69, "Remarks"
    End With
End Sub

 

[NauticalGent]

Don't know how I missed this earlier this year, but this is slicker than whale-poop! Much like the form you shared with me about 3 years ago, but all grown up in a Class of its own and some more bells and whistles. This is definitely going in the library!

Excellent work (as usual) MajP and Arnel!

 

[Pat Hartman]

Very clever solution. Just keep in mind that you do not want to use this when your linked tables are RDBMS unless they are very small. This solution is great for Jet/ACE tables . When the BE is SQL Server or other RDBMS, you never want to download an entire table to filter locally. You want the RDBMS to do the heavy lifting so other less clever methods are better.

 

[Sun_Force]

Very clever solution. Just keep in mind that you do not want to use this when your linked tables are RDBMS unless they are very small. This solution is great for Jet/ACE tables . When the BE is SQL Server or other RDBMS, you never want to download an entire table to filter locally. You want the RDBMS to do the heavy lifting so other less clever methods are better.

@Pat Hartman
I'm aware of your concern, and I'm sure you are right in not pulling the whole data set (due to LAN traffic).
Just as an experiment, I tested it with one of our university's databases. I used a linked table with 2,012,292 records. (sql server, Trusted connection)
The form took less than a blink of an eye to open and filtering was even faster than that.
Maybe having no text field in the table made it so fast.
Used table : Pr_Pk - OrderFK - PJ_FK - Delivery - UserFK

The form can also be opened with a limited set of data as it's record source if there's an issue concerning speed (to make it faster.)

 

[Pat Hartman]

The form opens as soon as Access receives enough records to populate it. Notice that the count doesn't show immediately. It will be 1 of x until the whole recordset is downloaded.

One person downloading two million records once isn't a problem. 100 people downloading 2 million records hundreds of times per day is a problem. You will hear from your DBA and he won't be wearing a smile. If all the fields were numeric, the records were probably pretty small so that has an impact also.

Don't take my word for it, ask him what he thinks

 

[tomasm]

I'm trying to understand how this code works. When using the code provided in the first post, why do the button pictures not update correctly when the toggle filter button is clicked in the ribbon (or next to the record selectors)? This problem is corrected in the Class version but I can't figure out how.