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
Today, 04:11
Joined
May 21, 2018
Messages
4,015
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 coninuous 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: 25
  • DatasheetLikeFilter (2).zip
    44.5 KB · Views: 36
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:11
Joined
May 21, 2018
Messages
4,015
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, 09:11
Joined
Jul 26, 2013
Messages
7,735
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
Today, 04:11
Joined
May 21, 2018
Messages
4,015
@Minty, I have not tried.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:11
Joined
May 21, 2018
Messages
4,015
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: 44
  • select records.jpg
    select records.jpg
    38.6 KB · Views: 20

Users who are viewing this thread

Top Bottom