Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter by form question (2002)

    I am trying to figure out a way to apply multiple filters to a subform with the options of removing each of them individually. Right now the subform I'm using is a continuous form, when a user clicks on an entry in the detail section Access records what field is clicked and uses it to know which field to filter by(See attachment).

    If you click in the address column then type your criteria in the text box and click filter it will work, THEN you click in another column and repeat. Right now all of this works fine, what I'm trying to do is add 2 or 3 command buttons lets call them "X", "XX", "XXX" whenever the user clicks on "X" I need the form to remove the first filter they applied (leaving all others), when they click "XX" it should remove the second filter they applied (leaving first, >=third filter untouched).

    I have no idea how to do something like this so any help is appreciated .

  2. #2
    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 question (2002)

    The only way to do this is to remove and rebuild the filter each time, excluding the condition you want to drop. Since you have only one textbox to enter a condition, you would have to attempt to parse the conditions out of the filter string itself, which I don't recommend. I would suggest you take the easy way out and simply add a button to show all records, which is what most of us do in this situation.
    Charlotte

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

    Re: Filter by form question (2002)

    Why don't you teach your users to use the built-in Filter by Form? It has all the functionality you're trying to create, and more. You can put a command button on the main form to display the Filter by Form.

  4. #4
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by form question (2002)

    Thanks for the replies, I figured this would be difficult to do. I was asked to create a filter form similair to one that my company already uses in a piece of software they purchaced a few years ago, so noone will have to re-learn how to perform their filters.

    There is already a way to show all records(remove filter). I'll see if I can come up with a way to use the built-in filter by form, but I have a feeling I'm still going to have to come up with something works like I asked in my first post [img]/forums/images/smilies/sad.gif[/img].

    Thanks again,
    Ryan

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

    Re: Filter by form question (2002)

    I have attached a modified version of your database, with extra command buttons that remove part of the filter.

    Note: all parts from the 3rd on are treated as one.

  6. #6
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by form question (2002)

    Thanks for the help Hans! Thanks for spending your time trying to help me(you helped me with my last post here too). Your modified database does exactly what I asked for, however I don't know if It can be used for everything I wanted to have in the final product. There are a few things I would like to be able to do with this.

    1. I was planning on adding a different backcolor to the columns that are currently being filtered
    2. Then when a user clicks back into a filtered column txtFilterText will update itself to reflect whatever filter is already assigned to it(so users can't filter the same column twice, instead it will update their previous filter for that column).
    3. Would like to have a button that removes the filter for the selected column(last column that user has clicked in)

    I think I would need to store each filter(with column,criteria,position maybe?) separately then use some kind of a function to determine which filter to remove/update and then rebuild the filter string accordingly afterward. Can I do these things with your changes as a base for them or would it be better for me to find another method?

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

    Re: Filter by form question (2002)

    You could put a filter text box above each column (it would have to be in the subform header instead of in the main form). These text boxes would act as storage as well as provide visual feedback. On the other hand, it would be different from what the users are familiar with; I don't know how much of an objection that is.

    Otherwise, you'd have to use an array or a collection to store the filter information. It should be possible to do what you want with that.

  8. #8
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by form question (2002)

    If I use one filter txt box for each of the 25 columns why must they be in the subform? I'm thinking this method would work for me if could use them in the main form and just keep all of them hidden except the one associated with the current selected column.

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

    Re: Filter by form question (2002)

    If you want to display only one text box at a time, it doesn't need to be in the subform. I was thinking of 25 visible text boxes, one above each column. Since your subform is wider than the main form, it scrolls horizontally. This would make it extremely difficult to have the text boxes on the main form.

  10. #10
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by form question (2002)

    I understand what you were saying now, right now I'm trying to code these text boxes to appear/disappear according to column selection. I'm still not sure which route I should be taking here. Could I maybe get an example of how to adapt your version of the filter to use arrays or a collections (I've never used either of them, and Access help is still kind of cryptic to me)?

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

    Re: Filter by form question (2002)

    I was looking at this a bit more, and I came to the conclusion that it's not really worth the effort. You'd be investing a lot of time and effort in building a limited, rather inflexible filter system - only Like "something*" criteria, combined only by AND, and you would have to adapt it each time the design of your table or form changed. Better stick with the relatively simple, but effective features you now have. For more advanced filtering, use the built-in Filter by Form or the Advanced Filter/Sort. You'd be reinventing the wheel if you tried to emulate those.

  12. #12
    New Lounger
    Join Date
    Oct 2004
    Location
    Cleveland, Ohio, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by form question (2002)

    Hans, thanks for checking back to this thread. By the time I read your last post I had already started over with this form and I'm glad to say It's nearly done, the only thing that I have left to do to it is make it so the user can only apply one filter per column (if the user tries to filter a column that is already filtered it should replace the current filter with the new one). I've got everything else to work on my own but have hit a mental block with this one (I have a feeling I'm going to need a lot of nested if/thens for this). Little help please?

    Thanks again,
    Ryan

    Edit: Nevermind.... I got it working, had to add the following to my "cmdApplyFilter_Click()":

    If aryFltr1(1) = StrCompFilterBy Then
    aryFltr1(1) = StrCompFilterBy
    aryFltr1(2) = Me.txtFilterText
    Call BuildFilterString
    GoTo Exit_ApplyFilter_Click
    End If
    If aryFltr2(1) = StrCompFilterBy Then
    aryFltr2(1) = StrCompFilterBy
    aryFltr2(2) = Me.txtFilterText
    Call BuildFilterString
    GoTo Exit_ApplyFilter_Click
    End If
    If aryFltr3(1) = StrCompFilterBy Then
    aryFltr3(1) = StrCompFilterBy
    aryFltr3(2) = Me.txtFilterText
    Call BuildFilterString
    GoTo Exit_ApplyFilter_Click
    End If
    If aryFltr4(1) = StrCompFilterBy Then
    aryFltr4(1) = StrCompFilterBy
    aryFltr4(2) = Me.txtFilterText
    Call BuildFilterString
    GoTo Exit_ApplyFilter_Click
    End If

Posting Permissions

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