Cannot Clear Subform Datasheet .filter string

Jim Palatine
Jim Palatine used Ask the Experts™
on
I need to use a datasheet subform's .filter property to generate filtered reports and other database actions.  However when I clear the filter using the attached code two things happen:
1) The actual subform is displayed with the filter cleared.
2) The subform's .filter property string still has the old value.

The same scenario occurs if I use the Ribbon icon to Toggle the filter and still the .filter string is not cleared.

 Snapshot showing filter is not cleared
I can clear the field in the Immediate Window just fine, just not with code.

Manipulating the datasource with programatically generated SQL is impractical.

Any ideas?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Try setting

.Filter = ""
.FilterOn = True

Open in new window

And the .Requery shouldn't be needed.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I believe that simply setting the .Filter property to False will display all of the data, but will keep the actual filter value assigned to that property.

I concur with Gustav that the Requery action should not be required, and because you are not manipulating the recordsource is actually redundant.  The code I generally use looks just like yours:

.Filter = ""
.FilterOn = False

Dale

Author

Commented:
The problem is that the .filter value is not cleared using .filter="".  That what I am showing in the immediate window which displays the debug.print output from the routine showing the .filter value remains.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
But what does it matter when you are not filtering?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Agree with Gustav.  If FilterOn = False, then the recordset should not be filtered, regardless of the value of the Filter property.

You might want to open that form (or subform) in design view and check the Filter property of the form, and set the FilterOnOpen value to No.

This might help, but as Gustav stated, what difference does it make.

Author

Commented:
Thanks Gustav and Dale for your responses.  Nice to know others are out there to help.

I see some confusion about my question.

First, the form filters with the code offered, however the .filter string is populated with the prior filter string.

 I didn't tell you that I need to use the .filter string to filter a report downstream.  Here is the code for the Load event (Open event behaves the same)  for the report "ScreenList".  This report is generated with the source form open.  Because the .filter string doesn't clear properly the report displays the wrong data.


Private Sub Report_Load()
Dim strFilter As String, strSort As String, strStrip As String

'set new filter based on current display
With Forms!Job.Form!JOBSELECTOR.Form

    strStrip = "[" & .NAME & "]."
    strFilter = Replace(.Filter, strStrip, "")
    strSort = Replace(.OrderBy, strStrip, "")

End With

Me.Filter = strFilter
Me.OrderBy = strSort
Me.OrderByOn = True
Me.FilterOn = True


End Sub
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I can not replicate this. If I have button on the form, clearing the filter:

Private Sub ClearFilter_Click()

    With Forms!frmParent!subChild.Form
        .Filter = ""
        .FilterOn = False
        
        Debug.Print "F", .Filter
    End With

End Sub

Open in new window

and then open and load the report:

Private Sub Report_Load()

    Dim strFilter As String

    'set new filter based on current display
    With Forms!frmParent!subChild.Form  ' Forms!Job.Form!JOBSELECTOR.Form
        strFilter = .Filter
    End With

    Debug.Print "R", strFilter

End Sub

Open in new window

Only an F and an R are output.

So, something else is going on.
Distinguished Expert 2017

Commented:
Since my BE's are almost always SQL Server, I don't use filter.   That said, try setting the filter to null rather than a ZLS

Me.Filter = null

Author

Commented:
Sorry for the radio silence.  Since I was unable to reliably clear the filter string from a datasheet subform, I instead built a temporary table from the recordset clone and used that for a report dataset and a couple other activities.  Everything works fine now.  Thanks for all the help.
Distinguished Expert 2017

Commented:
I hope you removed the temp table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial