[AccessD] Reset OrderBy

A.D.TEJPAL adtp at airtelbroadband.in
Wed Feb 21 11:23:21 CST 2007


    Unlike FilterOn property, OrderByOn property followed by save action tends to get carried over to next opening of the form.

    Values assigned to Form's Filter & OrderBy properties tend to get carried over to next opening of form. Once stuck, these strings can not be replaced by zero length strings by mere assignment statements (See detailed note below).

    As such, form's close event is not always completely effective for ensuring that the form when opened next, does so with a clean slate. Instead, a single line of code in form's Load event, as given below,  should suffice:

    Private Sub Form_Load()
        Me.OrderByOn = False
    End Sub

    Certain points relevant to form's Filter / Sort action are covered in the note placed below.

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. If such zero length assignment is followed by save statement, OrderByOn property also becomes False for next opening of form (FilterOn property is always false for fresh opening).
    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 assignment of zero length string to this property is not able to get rid of it (even though it can be made in-effective by setting the FilterOn property to false). If it is expressly desired to clear the filter in force, criteria string that always evaluates to true (e.g. "2 = 2") has to be assigned to form's filter property.
    2.6 - If it is desired that form's filter, set during runtime, should carry over to next opening of the form (after it has been closed after the current session), the following statement should be included in form's load event.
        Me.FilterOn = True
    2.7 - On the other hand, 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.8 - 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: Susan Harkins 

  I'm using a simple procedure to set a form's OrderBy property. When I close the form, I want to delete any setting, so that the property isn't saved with the form. How can I do that? I tried 

  Private Sub Form_Close()
    'Reset Order By property
    Me.OrderBy = ""
  End Sub

  But it doesn't work. Checked Help but didn't find anything helpful. I don't really want to set the sort order to "" -- I just want to wipe it clean. My guess is the property doesn't recognize "", although it doesn't error. 

  Susan H. 


More information about the AccessD mailing list