Filter By Form (2002 SP-2)

[bfxtrfcmgr]

I'm experimenting with a continuous form containing some ten thousand records sorted on any combination of twelve different categories. I am using the "Filter By Form" tool to extract the information I need based on calculations in the form footer. Some of these filters are saved as queries (ie. date ranges). As the filtering can become rather complex, might there be a way to have the footer or header "reveal" what the current filter settings are? Switching back and forth (and remembering) between the Filter By Form screen and the continuous form is frustrating.

[HansV]

A very simple way would be to put a text box in the header or footer section with Control Source
<pre>=IIf([FilterOn],[Filter],"")
</pre>

It is a good way to learn how Access builds a where-condition, but it may look rather daunting.

[bfxtrfcmgr]

<img src=/S/drop.gif border=0 alt=drop width=23 height=23>. . .daunting?
Wow, what a great learning tool. <img src=/S/clever.gif border=0 alt=clever width=15 height=15>
Need a rather large txtbx to accomodate all the info, but it is very clever. Thanks!

[bfxtrfcmgr]

Hans:

In txtFilter (=IIf([FilterOn],[Filter],"") the attached results are returned for the particular filter applied. Obviously this takes up quite a bit of real estate (I can page up/down within the txtbx, but that doesn't give a good view). Might there be a way to concatenate the results? Hmm, that doesn't seem right (what would be the control source in a continuous form? I tried it, but just got the results of the first record in the record-set). Could I "Format" the results in txtFilter? The results are dynamic of course, so how would one address the formatting? My life isn't hanging on the edge over this, but it seems an interesting challenge with substancial benefit. Any and all direction/advise appreciated.

[HansV]

I don't understand what you mean by "concatenating the results" or by "formatting". How would you like to format it?

[bfxtrfcmgr]

In the attachment
txtShConCity1 = "helm" would display "helm"; not the entire statement, only the result (as with the remaining criteria). I'm not really concerned with the date parameters because I have two txtbx (Min/Max) that already show the date range of the continuous form. Sorry I didn't make that more clear.

[HansV]

But even without the date criteria you can have conditions on several fields, so just "helm" would not be very informative.

One thing you could do to shorten the string is to omit the name of the record source of the form:
<pre>=IIf([FilterOn],Replace([Filter],"qfltMasterAllFiltered.",""),"")
</pre>

Access also inserts lots of superfluous parentheses into the filter, but it would be a difficult task to prune the superfluous ones, while keeping those that are necessary. If you want more "formatting", it would be more efficient to write a VBA function that processes the filter string.

[bfxtrfcmgr]

Hans:

I follow. If I filtered on a single city entry (ShConCity1 or ShConCity2) I wouldn't be certain which city the filter applied to. Although some of this seems redundant (ie. the form results provide at least some clue) there is some confusion with the large record-set. Be that as it may, your solution for reducing the size of the string nets a result (in this case) that is nearly one-half the size of the original, and still easily discernable. Absent the knowledge in VBA to accomplish much more than this, I am happy with your current solution. Thanks so much for your help!

[MarkD]

On some forms (and reports) I display a "plain text" representation of the currently applied filter or query selection criteria. However, this is where the SQL for form/report is generated dynamically, based on user input (comboboxes, single- or multi-select listboxes, etc). A custom function generates plain text "filter" by looping thru combobox & listbox items selected, etc. Generally users cannot apply filter-by-form or filter-by-selection (these menu items are disabled or replaced with custom menus) - too many things can go haywire, and many end users wouldn't know how to use these built-in functions even if enabled. If allowing users to use built-in filter functionality, here is an example of some code that can be used to translate filter string to something more readable:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
' Test only:
MsgBox "Apply Filter event." & vbCrLf & "Apply Type: " & ApplyType, _
vbInformation, "APPLY FILTER EVENT"
SetFormFilterText ApplyType
End Sub

Private Sub Form_Current()
SetFormFilterText acApplyFilter
End Sub

Private Sub SetFormFilterText(ByRef intApplyType As Integer)

With Me
Select Case intApplyType
Case acShowAllRecords '0
.FormFilter_Text = "(None)"
.FormFilterPlain_Text = "(None)"

Case acApplyFilter, acCloseFilterWindow ' 1, 2
If .FilterOn = True Then
.FormFilter_Text = .Filter
.FormFilterPlain_Text = GetFormFilterText
Else
.FormFilter_Text = "(None)"
.FormFilterPlain_Text = "(None)"
End If
End Select
End With

End Sub

The GetFormFilterText function attempts to translate Filter string into English:

Private Function GetFormFilterText()

Dim strFilter As String

With Me
strFilter = .Filter
' Find AND's/OR's, add CR/LFs; Binary comparison - SQL And & Or are proper case:
strFilter = Replace(Expression:=strFilter, _
Find:=") AND (", _
Replace:=")" & vbCrLf & "And" & vbCrLf & "(", _
Compare:=vbBinaryCompare)

strFilter = Replace(Expression:=strFilter, _
Find:=") OR (", _
Replace:=")" & vbCrLf & "(OR)" & vbCrLf & "(", _
Compare:=vbBinaryCompare)

' Get rid of RecordSource:
strFilter = Replace(Expression:=strFilter, _
Find:=.RecordSource & ".", _
Replace:=vbNullString, _
Compare:=vbBinaryCompare)

' Get rid of open & close double parentheses:
strFilter = Replace(strFilter, "((", "(", , , vbBinaryCompare)
strFilter = Replace(strFilter, "))", ")", , , vbBinaryCompare)
' Space out the equal signs:
strFilter = Replace(strFilter, "=", " = ", , , vbBinaryCompare)
End With

GetFormFilterText = strFilter

End Function

To test this, add 2 unbound textboxes to form: FormFilter_Text, and FormFilterPlain_Text, and add the event procedures shown above. Set FormFilter_Text textbox to visible for test purposes only. I found it necessary to call sub from Current event, or else not always applied as expected, for example, when closing Filter By Form window. The Msgbox in ApplyFilter event is for test purposes only. This worked OK in test with forms in copy of Northwind.mdb. Code can be modified as necessary for best results if your filter criteria get very convoluted.

HTH

[bfxtrfcmgr]

Mark:

Thanks for all that code, but it's pretty much over my head (seems to be two procedures, uh, but. . .uh, On Current event for form? How do I get two procedures in one event?). As I mentioned to Hans "...absent the knowledge in VBA..." That aside, I would actually prefer to use combo-boxes to do the filtering from the form footer, but I don't get how to query the form when the parameters are set by the very form that is being queried. Dazed and confused.

[MarkD]

There's only one procedure that's called from the Current event: SetFormFilterText. This procedure in turn calls the GetFormFilterText function, if necessary, to format the filter string. The code in previous reply includes example of how to call the procedure from the Current and ApplyFilter events. All the procedures shown should be located in form module. You'd need to add the textboxes to the form to test this. There are many ways to apply filter to form based on comboboxes or other user input, some simple, some more convoluted (as with multi-select listboxes). Generally, there's an AfterUpdate event for combo or listbox, or a command button event, that calls a procedure that generates a filter string (equivalent to an SQL WHERE clause, without the "WHERE"). This string is then applied to the form's Filter property, and the form's FilterOn property set to True. The user is provided some visual cues to indicate filter applied, for example, the form's caption may reflect certain criteria, etc. If you are not familiar with building SQL criteria strings in VBA and so on, you may want to stick to the Filter-by-Form approach for time being.

HTH

[bfxtrfcmgr]

Mark:

Thanks for your patience. I'll "munch" on this for a while and see what kind of trouble I can get myself into. <img src=/S/munch.gif border=0 alt=munch width=19 height=17>

[bfxtrfcmgr]

Mark:

Sorry for being such a dolt, but you exist in an entirely different (advanced) world than I do; I

[charlotte]

Event procedures aren't the only kind of code that goes into a form. You can also add functions and subs just by pasting them into the form module. Then the routines are available to the event procedures and to other routines on the form, including allowing the use of the functions in the property sheet.

[bfxtrfcmgr]

Charlotte:

In the VBA window I tried selecting "Insert > Procedure" and then Type "Sub" and Scope "Public" but it would not accept the name "Private Sub SetFormFilterText(ByRef intApplyType As Integer)". Is that the wrong way to do it? I've never done this before.