Turning Filter On and Off

[J Bhujanga]

I have a form on which the user is to have the option of filtering the records to only include those coded to a State Highway. This is determined by a boolean field in the recordset called "State_Hwy". There is a small table that contains the users previous settings for various preferences such as this, and there is a check box on the form for changing their preference. It happens to be the only filtering option we are offering so I have it in the form's Filter property, i.e. "[State_Hwy]=Yes".

When the check box is toggled it runs a little bit of code that switches the Filter on and Off,. The code is:
"If SH_Only Then Me.FilterOn = True Else Me.FilterOn = False" ("SH_Only" is the name of the Checkbox control)
It works perfectly.

But in the form's Load event I want to see what they used last time and default it to that for them. That's not quite the whole story though. They can review and reset their preferred settings from a form that just accesses their various preference settings, so the setting could actually be different from that which they actually used the last time they edited records.

So, there are four possible scenarios.
1. Last editing session No Filter, and have left preferences to not filter.
2. Last editing session No Filter, and have since set preference to filter.
3. Last editing session Filtered, and have left preferences to filter.


4. Last editing session Filtered, and have since set preference to not filter.

Hence I have this Load event code:

Filter_SH_Only = DLookup("[SH_Only_Mode]", "AP_Record_Filtering_Options")
If Filter_SH_Only Then
Me.FilterOn = True 'Turn filter on
Me.SH_Only = True 'Set state of checkbox to match filtered state
Else
Me.FilterOn = False 'Turn filter off
Me.SH_Only = False 'Set state of checkbox to match filtered state
End If



So, here's what happens:
The state of the check box always gets set correctly.
For cases 1-3 everything works exactly as expected, Filters stays Off, Filter changes from Off to On, Filter stays On.
But in the 4th case where they last filtered but they've since changed their preference not to filter, the form opens with the records filtered, even though the part of the "Else" clause that sets the checkbox status was properly executed.
How come the records stay filtered in this case (Scenario 4), but it successfully changes when going from off to on (Scenario 2)?

[Bulzie]

Hard to tell all what you have to pinpoint where something is going wrong. Put a breakpoint in your code and step through F8 each line to see if you can tell the issue.

[J Bhujanga]

I tried doing what you suggested and then it stepped through and worked the way it was supposed to without me changing anything and now it continues to work.
Thanks for the suggestion. This sort thing makes me a little crazy.

[Bulzie]

I have had that happen, sometimes it does not stay fixed. Maybe go into code on a form and do a compile and save. Then do a compact and repair.