A.D.Tejpal
adtp at airtelmail.in
Tue Apr 15 22:12:06 CDT 2008
Arthur,
Two of my sample db's as mentioned below, might be of interest to you:
(a) Form_Search
(b) Form_SearchByYearMonthRange
These are available at Rogers Access Library (other developers library). Link - http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.
Both styles of filtering, i.e. (i) Manipulation of source query and (ii) Manipulation of form's Filter property in conjunction with its FilterOn setting, are covered in the samples.
Certain factors having a bearing on programmatic manipulation of filters on forms, are mentioned below, for ready reference.
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 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, an intrinsically true criteria string (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.
=====================================