Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Filter By Form (2002 SP-2)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Filter By Form (2002 SP-2)

    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.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Filter By Form (2002 SP-2)

    <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!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  4. #4
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Filter By Form (2002 SP-2)

    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.
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Filter By Form (2002 SP-2)

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

  6. #6
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Filter By Form (2002 SP-2)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Filter By Form (2002 SP-2)

    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.

  8. #8
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Filter By Form (2002 SP-2)

    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!
    Attached Images Attached Images
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter By Form (2002 SP-2)

    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

  10. #10
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Filter By Form (2002 SP-2)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter By Form (2002 SP-2)

    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

  12. #12
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Filter By Form (2002 SP-2)

    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>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Filter By Form (2002 SP-2)

    Mark:

    Sorry for being such a dolt, but you exist in an entirely different (advanced) world than I do; I
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filter By Form (2002 SP-2)

    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.
    Charlotte

  15. #15
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Filter By Form (2002 SP-2)

    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.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

Page 1 of 4 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •