OpenForm does not clear Filter

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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.
mpimAuthor 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
Gustav BrockCIOCommented:
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.


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.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
"" 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.
mpimAuthor Commented:
Thank you so much for this clarification... Rg Michael
Dale FyeOwner, Developing Solutions LLCCommented:
You might also want to check the FilterOnOpen property and set it to No.
mpimAuthor Commented:
Thanks Dale - willl do! Your help is very much appreciated!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.