Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2013
    Posts
    14

    Unanswered: Concurrent Filters on a Form

    Dear all,

    I have a form with different fields, and a few hundred records.

    My objective is to be able to use different filters at the same time:
    (1) 2 filters on 2 fields that can take only 2 values ("F" or not "F" for the first field, TRUE or FALSE for the second).
    I already have 2 sets of 2 checkboxes that control filters based on these fields.
    Code:
    Public Sub CheckNCRStatusF_Click()
    If CheckNCRStatusF = True And CheckNCRStatusNonF = True Then
    DoCmd.ShowAllRecords
    Else
    If CheckNCRStatusF = True And CheckNCRStatusNonF = False Then
    DoCmd.ApplyFilter , "[NCR Status] = 'F'"
    Else
    If CheckNCRStatusF = False And CheckNCRStatusNonF = True Then
    DoCmd.ApplyFilter , "[NCR Status] <> 'F'"
    Else
    DoCmd.ApplyFilter , "[NCR Status] = '9899' "
    '9899 is an impossible value'
    End If
    End If
    End If
    End Sub
    (2) several filters on other fields where upon clicking a button the form requests the use to input (InputBox) a string that will be searched in this one field.
    Until now, I used something like :
    Code:
    DoCmd.ApplyFilter , "InStr(1, [Report Issuer], searchtext, 1) <> 0"
    What are the issues?
    (a) I can't use InStr with a text string defined elsewhere instead of a mere local variable (here, searchtext)
    I would like to have something like:
    Code:
    dim searchtext as As String
    searchtext = "John"
    DoCmd.ApplyFilter , "InStr(1, [Report Issuer], searchtext, 1) <> 0"
    (b) I can't use different filters at the same time. This is the biggest problem I have.
    I would like to be able to click on a first button to filter all records for which the [Status] = "F", and then to click another button leading to the input box to search the [Report Issuer] field.
    I don't want the second filter to replace the first one but the two filters to be applied simultaneously.


    Thanks for reading

    Tarik

  2. #2
    Join Date
    Aug 2006
    Posts
    126
    I personally have not used filters very much. I prefer to apply my selection criteria to a query, which can be very complicated if need be, and then use the results of that filter to populate a form or report. Queries are easy to find and easy to read and I don't then forget my logic from a year ago.
    Just a thought.
    Pete
    PGT

  3. #3
    Join Date
    Feb 2013
    Posts
    14
    I personally have not used filters very much. I prefer to apply my selection criteria to a query, which can be very complicated if need be, and then use the results of that filter to populate a form or report. Queries are easy to find and easy to read and I don't then forget my logic from a year ago.
    Thanks,

    While queries can take just a few seconds to modify for an Access-savvy user, the purpose of my present form is to make filtering intuitive and user-friendly for the final users.
    Last edited by Tarik C; 03-03-13 at 21:42.

  4. #4
    Join Date
    Aug 2006
    Posts
    126
    I didn't mean for YOU to modify the filters but to apply the result of your check boxes to various field criteria of the query. i.e. the "criteria" of the [NCRStatus] field would be [form].[userinput].[checkNCRstatusNonF]. I'm making up some of this because I don't know the name of your forms or the controls (or checkboxes).
    PGT

  5. #5
    Join Date
    Feb 2013
    Posts
    14
    I didn't mean for YOU to modify the filters but to apply the result of your check boxes to various field criteria of the query. i.e. the "criteria" of the [NCRStatus] field would be [form].[userinput].[checkNCRstatusNonF]. I'm making up some of this because I don't know the name of your forms or the controls (or checkboxes).
    Okay sorry I now understand what you meant.

    That would indeed be a way of making this work. While I've already run SQL queries through VBA, I don't know how to apply filter values to the source query itself.
    Any idea on how to do that?

    There should also be a a way of clicking another button ("ALL") in order to delete all the criteria and see all records.

    I would prefer to modify the source query rather than deleting/re-creating it as I have added extra columns to this query. It would save some hassle to keep the query and amending it...
    Last edited by Tarik C; 03-03-13 at 22:33.

  6. #6
    Join Date
    Feb 2013
    Posts
    14
    For info, I found a thread here, and it seems like I could apply this to my problem.

    I'll update you later.

    Thanks for the hint!

  7. #7
    Join Date
    Feb 2013
    Posts
    14
    Here is an update, and I think that thanks to Pete Townsend and the link I posted previously, I'm hopeful!

    Before I can even lay different filters, I have to make at least one working.
    Here is what I've done so far, based on 2 check boxes ("Status F", "Status non-F") on my main Form.

    (0) I created 2 check boxes on my form. An d I linked the AfterUpdate() event to the following actions:
    Code:
    Public Sub checkboxclosedNCR_AfterUpdate()
    
    If checkboxclosedNCR = True And checkboxopenNCR = True Then
    txtNCRStatus = "'F' Or <>'F'"
    Else
    If checkboxclosedNCR = True And checkboxopenNCR = False Then
    txtNCRStatus = "'F'"
    Else
    If checkboxclosedNCR = False And checkboxopenNCR = True Then
    txtNCRStatus = "<>'F'"
    Else
    txtNCRStatus = "'F' And <>'F'"
    
    End If
    End If
    End If
    End Sub
    The Event triggered by the other checkbox is exactly the same.

    Note: Open <--> "F" , Closed <--> not "F"

    (1) I created public variables in my module.
    Code:
    Option Compare Database
    Public txtNCRStatus As String
    Public checkboxclosedNCR As Boolean
    Public checkboxopenNCR As Boolean
    (2) I created a public function in my module. This is just an identity, to put the public variables values into a function.

    Code:
    Public Function NCRStatus() As String
    
        NCRStatus = txtNCRStatus
        
    End Function
    (3) I put the function value into the Form's source Query, hoping that it will filter the Query and subsequently the Form accordingly.
    See: function_into_source_query.JPG (attachment)

    RESULTS
    The source Query doesn't update according to the function I placed in the criteria row.

    What do I do wrong?

    Tarik
    Attached Thumbnails Attached Thumbnails function_into_source_query.JPG  

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    It may be language, but you cannot have concurrent fitlers active in a form. you can only EVER have a single filter active on a form at anyone time. that filter can be a compound filter (made up of multiple elemnents)
    you can have multiple filters set up ready to go as variaable and then apply the correct variable as required
    a compound filter can refer the same column.
    effedctivley a fliter is a WHERE clause that doesnt' use the word WHERE, so providing your expression can bre resolved using boolean logic it will work


    ferinstance, say you have a numeric and a string column
    Code:
    strFilter = "(MyNumericColumn = 10 and MyStringColumn like 'Tariq%') OR (MyNumericColumn = 99 and MyStringColumn = 'Tariq') OR 
    (MyNumericColumn = 40) OR 
    (MyNumericColumn = 15 and MyStringColumn <> '%Tariq%')"
    effectivley that says
    return all records with the following characteristics
    MyNumericColumn = 10 and MyStringColumn contains the word 'Tariq')
    AND MyNumericColumn = 99 and MyStringColumn equals 'Tariq'
    AND MyNumericColumn = 40
    AND MyNumericColumn = 15 and MyStringColumn doesn't contain 'Tariq'

    NOTE the use of brackets liits the AND clause, then use of the word OR means a different set of conditions apply to the following block (so that means you can have rules which at first glance could be mutually exclusive
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2013
    Posts
    14
    Thanks Healdem for your reminder on the definition of a filter.

    While English isn't my mother tongue, the problem here is more a problem of definitions.

    What I'm trying to do is to apply ONE filter to a form (and yeah the title of the thread wasn't accurate).
    I want this filter to be made of ONE OR SEVERAL criteria as input by the user (using the checkbox and Instr() search buttons, as described in the post#1).

    So going back to the possible solutions, I thought the solution provided by Pete was great, and I'm trying to make it work now, but I bumped into the issues described in post#7.

    I thought I got it but the solution doesn't work as it should be.
    I really need some help on this one.


    PS: actually your post just gave me a hint : I could try to create a public string variable (your strfilter) and update it dynamically based on the check boxes and buttons on my form. The AND and OR will help me to do so.
    This solution is different from Pete's Query approach.
    What do you think?
    I'm off to try that!
    Last edited by Tarik C; 03-04-13 at 05:07.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    source query doesn't update
    at what point do you think you are updating the source query?
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Feb 2013
    Posts
    14
    at what point do you think you are updating the source query?
    Healdem,

    I am not sure about what you tried to say.
    RESULTS
    The source Query doesn't update according to the function I placed in the criteria row.
    I meant that: having placed the function NCRStatus() in the relevant Query criteria (in Design View), I didn't observe a Query result change according to the content of NCRStatus().

    When I check in the Immediate Window, I have
    Code:
    ? NCRStatus()
    'F'
    ...which is what I wanted.
    However, the Query Datasheet View doesn't show the corresponding records. It shows no records, or records that at least do not match the criteria contained in this NCRStatus().

    If I go back to the Query Design and if I type manually 'F' in the first criteria row, I do get the results I wanted.


    Let me know whether you can understand my post.

    Thanks again,

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so is this a query problem or a filter problem
    if it is a query problem is it in selecting records or updating records

    what is the result of running the query


    the problem that I see is that you are settign the value of NCRStatus = txtNCRStatus which is set by code elsewhere.

    I'm not convinced you can do that. when it comes to running the query the function will return a value that is set in that function or code called from within that function


    so if you want NCRStatus to have a value then you need to explicitly call some code that sets that value within the scope of that function. Im confused looking at your code which seems to hbe dependant on a form being open, again not soemthign that you can neccesarily rely on.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Aug 2006
    Posts
    126
    Following up on Missinglinq's comment I ran across the following info while I was looking from something else.
    Microsoft Access Tips: Creating a Dialog Form
    Pete
    PGT

  14. #14
    Join Date
    Feb 2013
    Posts
    14
    Pete, Healdem,

    Thanks a lot for your help guys!

    I've finally achieved what I wanted to do.
    I followed the hint that Healdem gave me in post#8 and built a dynamic NCRstrfilter string variable. I defined NCRstrfilter in my modules.
    I also found some inspiration in Pete's link

    Every time the Form user clicks on a checkbox or on a search button (I added a button in front of each field, so that the buttons just replace the Fields' names -> space savings on the Form), an InputBox opens and ask for the field's search string : the field-specific search string goes into <Field's name>StrFilter.

    I then call a user-defined function in a module in order to collect all the criteria in a single string (namely NCRstrfilter ):

    Code:
    Public Function ApplyAllFilters()
    ' Just in order not to have empty criteria
    If Field1StrFilter = "" Then
    Field1StrFilter = " TRUE "
    End If
    If Field2StrFilter = "" Then
    Field2StrFilter = " TRUE "
    End If
    If Field3StrFilter = "" Then
    Field3StrFilter = " TRUE "
    End If
    ...
    ...
    ...
    ...
    
    NCRstrfilter = Field1StrFilter & " AND " _
                    & Field2StrFilter & " AND " _
                    & Field3StrFilter 
    
    End Function

    and back to the Form's Sub I filter the form based on this NCRstrfilter string filter after every user's input, for example:

    Code:
    Public Sub searchField1_Click()
    Field1StrFilter = InputBox("What item are you looking for? ")
    Field1StrFilter = "[Item name] like '*" & Field1StrFilter & "*'"
    
    Call MyModule.ApplyAllFilters
    Me.Filter = NCRstrfilter
    Me.FilterOn = True
    End Sub
    Note: I also stopped using Instr() as I think it doesn't accept string variables as arguments. I used Like instead.


    I'm really happy to have finished that, as I was stuck on that problem for days!

    Cheers,

    T

Tags for this Thread

Posting Permissions

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