[AccessD] Setting form filter string

Doug,

    My observations based upon Access 2003 desktop on Win XP are placed below:

    Programmatic assignment of zero length string as filter property does clear the previous filter string if any - as seen at run time. It has the simultaneous effect that the FilterOn property too gets set to False. (Mere change of filter string, so long as the new one is not of zero length, does not interfere with existing status of FilterOn property).

    Carry-over of filter string to next session takes place if the form is saved and closed while a normal filter string is still in force and has not been cleared by explicit assignment of zero length string. Once this carry over takes place, the string gets embedded in the property sheet (as can be verified in design view). This represents a sticky state in the sense that although assignment of zero length string at run time would still prevail (as per previous para), the sticky string does not go away and resurfaces on next opening of form. Of course if another non-zero length string is assigned during a given session, the latest one becomes the carry-over string.

    In actual practice, so long as you take care to explicitly set the FilterOn property to True or False as required, the sticky behavior of filter string should not cause a problem. Some other details are placed below, for ready reference.

Best wishes,
A.D. Tejpal
------------

Form's Filter & Sort Order At Run-Time
============================
1 - Factors to be considered for ensuring that the form opens with a clean slate (free of any filter or sort order).

    1.1 - Once the Filter and OrderBy properties of a form have been set to some significant strings at run time, subsequent setting to zero length strings (Me.Filter = "" and Me.OrderBy = "") in the close event is not found effective in preventing a carry over to the next opening of form. (Though debug.Print statement placed within the close event, shows zero length strings for these two properties, their last significant values used prior to firing of close event are found to stick on and resurface when the form is opened next time).

    1.2 - On a clean form (free of filter & sort), any values assigned at runtime to Filter and OrderBy properties, can be cleared by subsequent assignment of zero length strings provided such assignment is carried within same session of runtime and before reaching the Close event stage. In absence of any such action, the values assigned to Filter and OrderBy properties get carried over to the next opening of form.

    1.3 - Assignment of zero length strings to Filter and OrderBy properties results in forcing the FilterOn and OrderByOn properties to False for the current session of runtime, till further interference.

    1.4 - If a form opens with carry-over values of Filter & OrderBy properties from the previous session, assignment of zero length strings to these properties, even if done before reaching the Close event, is effective only for current run session and does not get rid of old carry over values for next opening of the form. If however, fresh non-zero length strings are assigned (before reaching the Close event), these replace the earlier ones and become new carry-over values.

    1.5 - FilterOn and OrderByOn properties are not available in design view and their default status is False. For any new opening of a form (whether free of Filter/OrderBy settings or not), the status of FilterOn is invariably False. On the other hand, if OrderByOn property had been set to True in previous run of the form and was not re-set to False before reaching the Close event (setting it to False in Close event alone, does not suffice), it is found to retain its True status in next opening of form.

    1.6 - Conclusion - For ensuring clean status of form (free of filter & sort) when it opens, the following statement in its Load event should suffice.
    Private Sub Form_Load()
        Me.OrderByOn = False
    End Sub
    
    As an abundant precaution (and if the developer does not feel comfortable in their absence), the following additional statements (though redundant) can also be included in the Load event.

        Me.OrderBy = ""
        Me.Filter = ""
        Me.FilterOn = False

2 - Some general aspects relevant to filtering on forms at run time are summarized below.

    2.1- Any filter inherent within the record-source continues to remain in force. Whatever is done via form's filter property, is merely supplementary to that (as if joined by " And " operator).

    2.2 - Unlike a report, form's FilterOn property is not available for setting in design view. It has to be explicitly set to True at run time (on opening the form, default status of this property is False, even though Allow filters property is set to Yes).

    2.3 - Order of placement of Me.FilterOn statement with respect to Me.Filter statement does not matter.

    2.4 - Whenever the statement Me.Filter = "" is used, FilterOn property of the form gets automatically set to False. This implies that if at any stage in the code, Me.Filter is set to zero length string,  subsequent assignment of a fresh string to the filter property won't be effective unless there is also a fresh statement Me.FilterOn = True (although Me.FilterOn was never set to False explicitly).

    2.5 - Once a criteria gets assigned to form's filter property, it tends to stick. Subsequent run time assignment of zero length string to this property is not able to get rid of it permanently. It can be replaced by another string (e.g. "2 = 2" or "True").

    2.6 - If it is to be doubly ensured that form's filter set during runtime, does not cause any interference on next  opening of the form (after it has been closed after the current session), the following statement can be included in form's load event. 
        Me.FilterOn = False
(Strictly speaking, this statement is redundant, as on opening of a form, the default status of  FilterOn property is False)

    2.7 - After applying the filter, there is no need to use Me.Requery explicitly. Application of any fresh filter condition, forces requery as well.

    Note - If  DoCmd.OpenForm statement, used for opening the  form, contains a criteria string in its where argument, the form opens with its FilterOn property set to True and its Filter property set to the contents of where argument (in Docmd statement). Effect of this filter is supplementary to any filter inherent within the record source, as mentioned at (2.1) above.
=====================================

  ----- Original Message ----- 
  From: Doug Steele 
  To: accessd at databaseadvisors.com 
  Sent: Wednesday, April 08, 2009 08:01
  Subject: [AccessD] Setting form filter string


  Hello All:

  I discovered today that I couldn't assign the empty string to a form filter
  in Access 2003:

  Me.Filter = ""

  left the filter string unchanged.  I couldn't assign a Null to it, either.
  Has anyone else run into this?  Help doesn't say anything.  The workaround
  is to set Me.FilterOn to False instead, but it still seems weird.

  Doug Steele