OpenForm does not clear Filter

Michael Paravicini
Michael Paravicini used Ask the Experts™
on
I use a Sub to open all forms (for validation purpose). One of the parameters passed is lWhereCond. Now I have the situation where lWhereCond is set to "" and yet when I open the form with

    DoCmd.OpenForm lFrameName, lacFormView, lFiltername, lWhereCond, lDataMode, lWindowMode, lOpenArgs

where both lFiltername and lWhereCond are set to "" the filter is still set to "Resid=3" in the Load event of the form (this was the previous filter used).  I am confused why the filter is not reset to nothing and why it still applies the old filter? I am using a form with a subform. Thank you so much for any explanation. Rg Michael
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
In the OnOpen event of the form, check if property Filter is set to something. If it is, set property FilterOn to False.

Alos, check the form's property ApplyFilterOnOpen (or similar) is set to False.

Author

Commented:
Thank you Gustav but unfortunately this does not work as me.filter is set with the value of the calling parent frame and not set to "" as requested in the OpenForm  where condition. No clue why... any further help would really be appreciated... Cheers Michael
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
The parameters don't set the Filter property - they determine how the recordset of the form should be filtered initially.

If you wish to pass values to the form - to be used for setting a filter by code in the form - use OpenArgs.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Hi,

if you set the filter value in the form properties itself and also "load filter at start" then setting the "Where" condition to "" will not change this setting. Only if you explicitly set a valid condition it will overwrite the saved filter. Also, if no filter was set, the "Where" condition will set the "Filter" property and the "FilterOn" property automatically. But it doesn't reset it if you do not specify a "Where" condition or pass an empty string value.
So if you want to reset a filter a form uses you need to open the form without the "acDialog" parameter (to make sure the code in the calling procedure doesn't stop) and then you can access the loaded form with the "Forms" collection and change the filter setting to "" and "FilterOn" to false. On this way you can also be sure to reset a filter which was maybe set in the Form_Open or Form_Load event of the form you want to open.

Cheers,

Christian
Distinguished Expert 2017

Commented:
"" is not the same as Null.

Use either a filter or a where clause but not both.  Personally, because I use mostly ODBC BE's, I never use filters.

Author

Commented:
Thank you so much for this clarification... Rg Michael
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
You might also want to check the FilterOnOpen property and set it to No.

Author

Commented:
Thanks Dale - willl do! Your help is very much appreciated!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial