The Most Powerful form Filter and Requires almost No Code

[isladogs]

@MajP
I also hadn't seen that other thread before. Clearly a lot of similarities in outcomes.
It will be interesting to compare the code in each version. That may well be very different. I'll study your examples carefully later.

Yesterday, I published an article showing how to freeze a specifiied number of columns in a continuous form. No APIs required.

Freeze Columns in Continuous Forms - 2

This is the second part of a 3-part article which discusses the freeze fields feature in datasheet forms and demonstrates how similar functionality can be added to continuous forms. Navigation is controlled using an ActiveX horizontal scroll bar

www.isladogs.co.uk

My next continuous form example will demonstrate how to move, resize, hide & restore columns in continuous forms - again with no API calls needed. That should be published in the next day or two.

 

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

I asked this question because there seems to be some unexpected behavior in Access but I couldn't find the cause. I located the source of this behavior and now have a solution.

To see the command button pictures update correctly when either of the Toggle Filter buttons are used, I would expect the only modification needed would be changing this line:

If InStr(Me.Filter, cmd.Tag) > 0 Then

to this:

If InStr(Me.Filter, cmd.Tag) > 0 And Me.FilterOn = True Then

The SetButtons sub needs to check if the form filter is turned on. This is because the Filter property of the form still contains matching tags when the filter is off. Oddly, the button pictures still don't update correctly after changing this line of code. The pictures change when the Toggle Filter buttons are clicked, but the picture is the opposite of what it should be.

Here is the source of this unexpected behavior:
When clicking the Toggle Filter button in the ribbon or clicking the Unfiltered/Filtered button in the record select bar, the On Apply Filter event runs before FilterOn is set to True/False. This causes the command button picture to always be the opposite of what it should be.

The solution is to move SetButtons to the On Current event. SetButtons will then run after the FilterOn property is updated, and the correct command button picture will be displayed. This is because the On Current event runs after FilterOn is set to True/False.

This unexpected timing of events only occurs with the Toggle Filter buttons. If filters are applied/removed with the ribbon Filter button (or via acCmdFilterMenu), FilterOn is set to True/False before the On Apply Filter event runs, as expected.

I haven't yet explored why the Class version doesn't have this problem.

Reference:

 

[arnelgp]

another demo this time the 2 Image buttons are in a table with Attachment fields.
these images are then copied to MsysResources table.

it is not the "most powerful" and it requires code.

 

[tomasm]

another demo this time the 2 Image buttons are in a table with Attachment fields.
these images are then copied to MsysResources table.

it is not the "most powerful" and it requires code.

I see now that your class version uses the Form_Current event to check if the filter is on and update button pictures accordingly. This must be what you meant in a previous post about fixing a bug in the original. I prefer the simplicity of MajP's original because I understand (mostly) how it all works. And with the small change I made it works well. As a learning exercise, I'm trying to only use code in my new database that I understand. I'll have to come back to this class version after I learn class modules.

 

[tomasm]

Another note to add:

If a field name contains the name of another field then the button picture will change unexpectedly. For example, if you have a field named Spool and another field named SpoolSize, whenever SpoolSize is filtered, the picture for both SpoolSize and Spool will change to show that the fields are filtered even though Spool is not filtered.

Adding brackets to the tag fixes this problem:

If InStr(Me.Filter, "[" & cmd.Tag & "]") > 0 And Me.FilterOn = True Then

In the Class version this line needs brackets added:

If InStr(frm.Filter, "[" & c.name & "]") > 0 Then

This should also prevent the wrong picture showing in cases where the filter criteria matches part or all of a field name.

 

[tomasm]

Back again with more notes to add.

I wanted this powerful form filter to be even more powerful for my forms so I made some changes to make the button pictures show that a field is sorted as well as being filtered.

Four more pictures are needed for a total of six. I'm using these images and these are the names I assigned to each:

I also added the Remove Sort and Toggle Filter buttons to the form because I hide the Access ribbon.

Below is the updated code (I'm still not using the class version, yet) with a few notes about the changes.

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:
  Select Case Err.Number
    Case 3021
      MsgBox "No records returned", vbInformation
      Me.Filter = ""
      Me.FilterOn = False
      SetButtons
    Case 2105 'this error occurs when clicking a button if the form is read only and the filter currently applied returned 0 records
      MsgBox "No records to filter"
      DoCmd.RunCommand acCmdUndo
      SetButtons
    Case Else
      MsgBox Err.Number & " " & Err.Description
  End Select
 
End Function


Private Sub Form_Current()
 
  SetButtons
 
  'enable the remove sort button if sorting is applied
  If Me.OrderBy <> "" Then
    btnRemoveSort.Enabled = True
  Else
    btnRemoveSort.Enabled = False
  End If
 
  'enable the toggle filter button if a filter is applied
  If Me.Filter <> "" Then
    btnToggleFilter.Enabled = True
  Else
    btnToggleFilter.Enabled = False
  End If
  
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 And Me.FilterOn = True Then
          
           'added these lines to change the picture if the field is sorted and filtered
           If InStr(Me.OrderBy, "[" & cmd.Tag & "]") > 0 And Me.OrderByOn = True Then
             If InStr(Me.OrderBy, "[" & cmd.Tag & "] DESC") > 0 Then
               cmd.Picture = "FilterSortedDesc"
             Else
               cmd.Picture = "FilterSortedAsc"
             End If
          
           Else
             cmd.Picture = "FilterApplied"
           End If
                  
         'added these lines to change the picture if the field is sorted
         ElseIf InStr(Me.OrderBy, "[" & cmd.Tag & "]") > 0 And Me.OrderByOn = True Then
           If InStr(Me.OrderBy, "[" & cmd.Tag & "] DESC") > 0 Then
             cmd.Picture = "SortedDesc"
           Else
             cmd.Picture = "SortedAsc"
           End If
        
         Else
           cmd.Picture = "FilterNone"
         End If
       End If
     End If
  Next cmd
      
End Sub

Private Sub btnRemoveSort_Click()
  If Me.OrderBy <> "" Then
    DoCmd.RunCommand acCmdRemoveAllSorts
  End If
End Sub

Private Sub btnToggleFilter_Click()
  If Me.Filter <> "" Then
    DoCmd.RunCommand acCmdToggleFilter
  End If
End Sub

 

[raziel3]

How many records can this handle? I find sometimes with filter/search forms the load and filter times starts to get slower the more records it has to handle especially if the underlying RecordSource is a query containing a subquery. It's even worse if your db is split and the backend is on a network.

What I do sometimes is to load the form with the current month or the last 1000 records added and then add filters to search all records.

 

[MajP]

How many records can this handle? I find sometimes with filter/search forms the load and filter times starts to get slower the more records it has to handle especially if the underlying RecordSource is a query containing a subquery. It's even worse if your db is split and the backend is on a network.

What I do sometimes is to load the form with the current month or the last 1000 records added and then add filters to search all records.

This is not really a filter/search form. It is a standard continuous form just wrapping the native Access functions in a more user friendly wrapper. So it is whatever limitations are on any Access form. This is a method that does not require developing your own search code and capabilities. If the user needs something more advanced then this is not being proposed as a solution.
In the first thread you will see it takes about 5-10 real lines of code to get the functionality. Everything else is just making the buttons change images.

 

[slharman1]

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

Pat, I hope this question finds you and yours doing well.
I have a similar question on a bit of code you created for us, it is in Module 1 and it is Public Sub FilterByControl. I would like to modify this to filter based on multiple txt boxes at the top of my form but I can't wrap my head around it.
Any idea where I should start or do i need to write a fresh sub for the routine - either way I am having a tough time figuring out where to start.
It goes like this on the EstimatesList Form:
tpye a partial model # and then a partial description AND/OR a product type (i have turned the Product type search control into an unbound combo that only gives text as a result with <All> being at the top of the list.
I't would be great if you could point me in the right direction.
Thanks

 

[slharman1]

Are the criteria connected with AND or OR? What if any are empty? Let's go with AND that connects optional criteria. So, you have 4 controls. Any combination can have values but if the control is empty, it is ignored.

Where (fld1 = Forms!myform!fld1 OR Forms!myform!fld1 Is Null)
AND (fld2 = Forms!myform!fld2 OR Forms!myform!fld2 Is Null)
AND (fld3 = Forms!myform!fld3 OR Forms!myform!fld3 Is Null)
AND (fld4 = Forms!myform!fld4 OR Forms!myform!fld4 Is Null)

Notice the pattern. Each expression inside a pair of parentheses is required to be true. The expression will be true if the table column matches the control OR the control is empty and so not of concern.

Thank you Pat, what if I need to use a portion of the field to see similar items in the data - such as:
typing ST in the search box would show all models numbers with ST in them like this
user types ST and the form is limited to:
ST 304
ST 305
ST 306 and so on.
Thanks

 

[slharman1]

Well, then use LIKE as the first part of each expression. Don't forget the wild cards.

Keep in mind that LIKE is a very expensive operation and the larger the table to be searched RBAR (Row By Agonizing Row) for EACH expression, the slower it will be.

You might want to consider adding an additional column to the table that allows you to group items. That way you can index it. and use = rather than LIKE.
Where somefield = "ST"

Thanks, Ill give it a try. I only have less than 3000 records

 

[slharman1]

Well, then use LIKE as the first part of each expression. Don't forget the wild cards.

Keep in mind that LIKE is a very expensive operation and the larger the table to be searched RBAR (Row By Agonizing Row) for EACH expression, the slower it will be.

You might want to consider adding an additional column to the table that allows you to group items. That way you can index it. and use = rather than LIKE.
Where somefield = "ST"

Use LIKE in place of the = sign correct?

 

[RWilliams500]

Howdy. Thank you arnelgp for the great filter code. I have three filter buttons on one of my forms. They each filter/sort a combobox control. Two of them work fine. One of them however is only partially working. It gives you the option to sort a-z and z-a, but the list of values to filter by does not populate. Everything seems to be spelled correctly, the right control for button and combobox are referenced. But that one just does not work. Would anyone have any advice on why?

 

[Gasman]

How is that anything to to with a filter?

 

[tomasm]

Howdy. Thank you arnelgp for the great filter code. I have three filter buttons on one of my forms. They each filter/sort a combobox control. Two of them work fine. One of them however is only partially working. It gives you the option to sort a-z and z-a, but the list of values to filter by does not populate. Everything seems to be spelled correctly, the right control for button and combobox are referenced. But that one just does not work. Would anyone have any advice on why?

I see that happen when the list of values is too long. I think you can increase the number allowed in Access options.

 

[isladogs]

By default, the list option is limited to 1000 records but that can be increased.

However, if the limit is very large, performance will suffer
For similar reasons, fields in linked ODBC tables are normally excluded

 

[RWilliams500]

By default, the list option is limited to 1000 records but that can be increased.

View attachment 114214
However, if the limit is very large, performance will suffer
For similar reasons, fields in linked ODBC tables are normally excluded

Thank you very much to you and tomasm for your replies. The table only has 99 records, though the field the control is tied to is a foreign key and that table has 654 records. But just in case, I increased the number of records to read to 10000, and clicked the ODBC fields option. This didn't help. The two filters on the form are reading from the same table and they work just fine. I included a copy of the database scrubbed of identifying info, with a few fake entries. The form in question is frmInjuries. The filter in question is the one that doesn't work obviously, it's the one that's under Employee. I feel like this is me missing something simple.

EDIT 5/23/24 0927

I messed up with the cleaned database, and also messed up my main one, due to forgetting one of the tables was a linked table. Fixed it and re-added.

 

[isladogs]

I've had a quick look at your database but am not clear what I'm meant to be looking at.
The screenshot appears to be for form frmInjuries but the buttons look different.
I have no idea where the code is that triggers the dropdowns when you click the buttons - there's no event procedures or embedded macros so I've no idea what's going on.

You may find it useful to review my recent article on this topic - it has 2 pages:

Multiselect Filter in a Continuous Form

Unlike datasheet forms, continuous forms do not have a multiselect filter option. This article shows how the feature can easily be added, either from the user interface or using VBA code.

www.isladogs.co.uk

 

[RWilliams500]

I used the code arnelgp posted in this thread. Did you get a warning about macros being blocked? I re-downloaded the database, and it said that and showed the button captions, Command80, Command76, and Command77. But I moved it to a folder that didn't have macros blocked, and it showed up like it does in the screenshot.

Should I have posted a new thread/question? I just assumed since the file was posted here that was the right place to do so.

 

[isladogs]

I've reopened the file and I now see the down arrows. The file was trusted previously and the code worked...but the images weren't displayed
However as I haven't looked at @arnelgp's code in this thread, its probably better to leave someone else to explain what's going on
As I've tagged @arnelgp, he should get alerted to the post and may be better placed to advise

However, you may find my approach to applying a multi-select filter in a continuous form works just as well and (possibly) with less code

I think a new thread would have been more suitable with a link back to the relevant posts in this thread.