The Most Powerful form Filter and Requires almost No Code (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:30
Joined
May 21, 2018
Messages
4,551
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.


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.
Products.jpg


When choosing a text field the filters available
Text.jpg


When selecting a date field the choice are limitless

DateFilter.jpg


I added a calculated year field

Year.jpg


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

Code:
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
 

Attachments

  • Text.jpg
    Text.jpg
    317.2 KB · Views: 36
  • DatasheetLikeFilter (2).zip
    44.5 KB · Views: 56
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:30
Joined
May 21, 2018
Messages
4,551
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

AWF VIP
Local time
Today, 03:30
Joined
Jul 26, 2013
Messages
8,064
Really neat, does it work in an Accde?
I haven't tried just wondered if you had, as they are disabled normally?
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:30
Joined
May 21, 2018
Messages
4,551
@Minty, I have not tried.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:30
Joined
May 21, 2018
Messages
4,551
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.

select records.jpg
 

Attachments

  • DatasheetLikeFilter 2.zip
    55.8 KB · Views: 63
  • select records.jpg
    select records.jpg
    38.6 KB · Views: 28

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 03:30
Joined
Sep 12, 2006
Messages
14,261
@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?
 

MickJav

Well-known member
Local time
Today, 03:30
Joined
Nov 28, 2005
Messages
2,431
Very Nice work @MajP I might give it a try with a recordset of mine about 270K Entries
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:30
Joined
May 21, 2018
Messages
4,551
@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

error reading drive A:
Local time
Today, 11:30
Joined
May 7, 2009
Messages
11,606
actully i made a Class out of it.
it correctly handles (bug i found on your code), Unfiltering using the Ribbon.
 

Attachments

  • Continuous_Filter_button.zip
    599.4 KB · Views: 23

Gasman

Enthusiastic Amateur
Local time
Today, 03:30
Joined
Sep 21, 2011
Messages
7,806
@arnelgp
I get an error on opening?

Is this due to my only having 2007 again?

1611151748548.png
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 03:30
Joined
Sep 12, 2006
Messages
14,261
So is the action coded to a standalone button on the form?
 

AndyC88

New member
Local time
Today, 03:30
Joined
Dec 4, 2020
Messages
20
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

Super Moderator
Staff member
Local time
Today, 03:30
Joined
Sep 12, 2006
Messages
14,261
it's not necessarily a "bug" to have an easy unfilter option.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:30
Joined
May 21, 2018
Messages
4,551
I added a couple of fixes 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 which was very confusing, so I simply renamed them: FilterSelector and ClearFilter. (However I guessed I should have just called it Filter). 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.

newPics.jpg


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.

ImportForm.jpg


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

Code:
'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
 

Attachments

  • DataSheetLikeFilterClass.accdb
    3.4 MB · Views: 15

Users who are viewing this thread

Top Bottom