Results 1 to 10 of 10
  1. #1
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161

    Question Filter on subform not being reset using Filter = "" command

    Using Access 2007.
    Have a subform with a filter set in the form - "British = TRUE"

    In the VBA code I change the filter to other things.
    e.g. Filter = "British = FALSE"
    FilterOn = True

    This works ok. However if I reset the filter using


    Filter = ""
    FilterOn = True
    the filter value is not changed. I have verified this using MsgBox Filter before and after the Filter = "" statement.

    Even more strange, if I use
    FilterOn = False
    Filter = "", then it is reset to blank.

    Can't see the logic here or what is preventing Filter = "" being applied if FilterOn = True

    Maybe there is a bug in this version of Access.

    Can anyone offer enlightenment on this - I have trawled the internet for clues, but none were forthcoming.

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    To be honest I always reset a filter by simply setting it to "" And setting it to Me.FilterOn = False.

    Perhaps the logic is that you can't change the filter value without it being off (FilterOn = False)?
    The idea maybe being that by forcing a change of Filter state the records are then re-queried?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161

    Thumbs up

    Minty,

    Yes you may be correct. I have done some more experiments and although I can change the Filter to something else if FilterOn = True,
    I can't reset it to blank if FilterOn = True. Perhaps it does force a requery and so it does not make sense to have Filter = "" and FilterOn = True.
    Surprised it does not throw up an error though trying to set Filter to "" when FilterOn = True.

    Thanks.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Does your underlying query or table have that filter applied? When an object is created, it inherits certain properties from the object it's based on, filter being one of them. So perhaps in the absence of a valid filter string it defaults to whatever is in the underlying data source. It's my habit to set filter on property to False as is recommended, not set it to an empty string and turn it on.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161

    Thumbs up

    On the subform, it is simply using the query in the recordset, with a filter applied. There is a default one set in the form design.
    I have noticed though that doing Filter = "" when FilterOn = True, does not revert the Filter to the default one, it leaves it to whatever it was set to previously.
    I do have some SQL on a Popup form that I construct using the Filter from the Subform. This is why it is important that if the filter is cleared (using FilterOn = False), I also set Filter = "", so both the subform and the Popup are selecting the same data.

    The issue has been resolved. Just really wanted to understand what was going on.
    So, it is important to apply the two statements in VBA in the correct order.
    ie. It has to be FilterOn = False followed by Filter = "". Filter = "" followed by FilterOn = False, does not work.

    Thanks for the comments.

  6. #6
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Before executing the FilterOn = True there should be a valid Filter condition set in the Filter Property like the "British=TRUE". FilterOn = False will remove the Filter and return the recordset to it's original state releasing the filter effect, but will not clear the filter condition from the Filter Property.

    Later on if the FilterOn=True is executed again the data will be filtered with the earlier Filter condition.

    Filter="" is not a valid Filter condition because it doesn't contain a valid Field Name or what data to look for to filter either and ignores the FilterOn=True when applied.

  7. #7
    Miles R is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    161
    Thanks for the reply.
    Yes that seems to be correct.

  8. #8
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Having seen that, I seem to remember somewhere that I saw someone use a filter of = "1=1" , which obviously is a valid filter but doesn't filter anything.
    It means if it's inadvertently saved with the form and filtering is turned on the form will still work.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The only part I question is
    Filter="" is not a valid Filter condition
    because what "condition" means in this context is not clear. When you set a filter property you're creating a string that is like a WHERE clause without the "where" part. If you set the filter property value to an empty string there is no error so it's not invalid from that standpoint. Also, if you set it to "" then apply .FilterOn = True the effect is as if there was no filter, and again, there is no error. IMO, people typically set the filter to "" as a safeguard against later applying the wrong filter or subsequently and unintentionally applying any kind of filtering in code. In other words, if you "turned off" a valid filter and later turned filtering on but forgot to specify a new filter, the old one is applied, which may not be noticed right away. Setting it to "" beforehand would result in this new application doing nothing. I don't know if that would be a no brainer as far as realizing nothing changed or not. If you were paying attention and had record counters on the form, it should be obvious.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    Since we enable or disable Filter through Code it is very unlikely that we write the FilterOn = TRUE statement alone.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-28-2017, 12:29 PM
  2. Replies: 3
    Last Post: 07-07-2016, 12:22 PM
  3. Filter out "Completed" items from subform
    By dargo72 in forum Forms
    Replies: 2
    Last Post: 11-28-2012, 10:41 AM
  4. Replies: 3
    Last Post: 11-27-2012, 07:20 AM
  5. Create "reset filter" button
    By vanlanjl in forum Access
    Replies: 0
    Last Post: 03-03-2009, 07:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums