A quick one - setting filter based on calculated control

terpsichore used Ask the Experts™
Dear experts -
I am trying to set a filter on a subform based on a control on that subform. The control in question is a checkbox, which is based on a formula (a complex one).

I am attempting to build a string and then setting the filter on the subform to that checkbox being true. I look at the subform BEFORE applying the filter and the checkbox is showing up as correctly checked.

I then apply the filter, using the code below, and no records appear.

    If Me!chkNeed2ndApp = True Then strfilter = strfilter & " and [Forms]![Project].sfPartsLines.Form.chkNeed2ndApp = TRUE"

Open in new window

(Me!chkNeed2ndApp is the control in the parent form that invokes the filtering sub)

Any idea what's wrong with my syntax/approach?

Many thanks.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Your code line below doesn't seem to be formed correctly. A Filter string should refer to a Field on the Form's Recordsource. Instead, you're referring to a control named cknNeed2ndApp. I believe instead:

If Me!chkNeed2ndApp = True Then strfilter = strfilter & " and TheFieldYouWantToFilter=TRUE"

So if I wanted to filter my form on a field named "Second_App" when the user checks the box named cknNeed2ndApp:

If Me!chkNeed2ndApp = True Then strfilter = strfilter & " AND Second_App=TRUE"


thank you - this led me to the solution.

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