Unable to remove filter from multiple subforms.

Probably a really easy and fundamental answer to this one. I have a button that is meant to clear a filter on two subforms. I can clear the filter from one form, or the other form, but not both.

Whichever code I use, only the first one in the list is cleared, the second remains filtered.


Me.cboClientFilter.Value = ""
Forms!frmMainJobs!frmJobsComplete.Form.FilterOn = False
Forms!frmMainJobs!frmJobsInProgress.Form.FilterOn = False

OR

Me.cboClientFilter.Value = ""
Forms!frmMainJobs!frmJobsInProgress.Form.FilterOn = False
Forms!frmMainJobs!frmJobsComplete.Form.FilterOn = False




What am I doing wrong?
LVL 1
AbacusInfoTechAsked:
Who is Participating?
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
try this:

Me.cboClientFilter.Value = ""
Forms!frmMainJobs!frmJobsComplete.Form.Filter = ""
Forms!frmMainJobs!frmJobsComplete.Form.FilterOn = False
Forms!frmMainJobs!frmJobsInProgress.Form.Filter = ""
Forms!frmMainJobs!frmJobsInProgress.Form.FilterOn = False

mx
0
AbacusInfoTechAuthor Commented:
Thanks, but same problem. It's really weird.

I swapped your code around to:
Me.cboClientFilter.Value = ""
Forms!frmMainJobs!frmJobsInProgress.Form.Filter = ""
Forms!frmMainJobs!frmJobsInProgress.Form.FilterOn = False
Forms!frmMainJobs!frmJobsComplete.Form.Filter = ""
Forms!frmMainJobs!frmJobsComplete.Form.FilterOn = False

And again only the first subform clears.

As a test I tried using two separate buttons so button A clears form A and button B clears form B but I get the same behaviour. The first one I press will work but the second will not. It's as if Access will not allow me to clear the filter more than once in a given "session". Session is the wrong word I'm sure but you get my drift.

If it helps, I have this code in the afterupdate on my combo, this is the one that actually filters both subforms in one go (successfully)

Me.frmJobsInProgress.Form.Filter = "name='" & Me.cboClientFilter.Value & "'"
Me.frmJobsInProgress.Form.FilterOn = True
Me.frmJobsComplete.Form.Filter = "name='" & Me.cboClientFilter.Value & "'"
Me.frmJobsComplete.Form.FilterOn = True
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"It's as if Access will not allow me to clear the filter more than once in a given "session". "
Well, that should not be the case.  I've had some subtle issues when clearing filters.  

But I'm confused ... setting the filters you have:

Me.frmJobsInProgress.Form.Filter

but you are not referencing frmMainJobs ... as you are when clearing ... ?

mx

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AbacusInfoTechAuthor Commented:
I think I understand what you are asking,

frmJobsInProgress and frmJobsComplete are the names of the subforms I am filtering on my main from frmMainJobs...

Am I doing something wrong here...?

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well ... you say setting the filters works ... and you have this systax:

Me.frmJobsInProgress.Form.Filter = "name='" & Me.cboClientFilter.Value & "'"
Me.frmJobsInProgress.Form.FilterOn = True

But ... you don't have the Main form name like you do here where you are trying to clear:

Forms!frmMainJobs!frmJobsInProgress.Form.Filter = ""
Forms!frmMainJobs!frmJobsInProgress.Form.FilterOn = False
           ^^^^^^^^^^^^^

So actually, I'm not seeing how the setting of the filters is working ...?

Also confirming ... you have two different subforms on one main form, correct ?

And ... the combo is on the Main form ??

mx
0
AbacusInfoTechAuthor Commented:
I see what you are saying, and for the sake of consistency have changed the code on my combo to:

Private Sub cboClientFilter_AfterUpdate()
Forms!frmMainJobs!frmJobsInProgress.Form.Filter = "name='" & Me.cboClientFilter.Value & "'"
Forms!frmMainJobs!frmJobsInProgress.Form.FilterOn = True
Forms!frmMainJobs!frmJobsComplete.Form.Filter = "name='" & Me.cboClientFilter.Value & "'"
Forms!frmMainJobs!frmJobsComplete.Form.FilterOn = True
End Sub

The two subforms still filter 'as expected'.

The button code remains:

Me.cboClientFilter.Value = ""
Forms!frmMainJobs!frmJobsInProgress.Form.Filter = ""
Forms!frmMainJobs!frmJobsInProgress.Form.FilterOn = False
Forms!frmMainJobs!frmJobsComplete.Form.Filter = ""
Forms!frmMainJobs!frmJobsComplete.Form.FilterOn = False

(OR)

Me.cboClientFilter.Value = ""
Forms!frmMainJobs!frmJobsComplete.Form.Filter = ""
Forms!frmMainJobs!frmJobsComplete.Form.FilterOn = False
Forms!frmMainJobs!frmJobsInProgress.Form.Filter = ""
Forms!frmMainJobs!frmJobsInProgress.Form.FilterOn = False


And still the first subform in the code gets cleared.

The two subforms are indeeed on the one main form. The combo is on the main form.

I will dummy the data and post the database up here...



0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Can you:

1) Compact & Repair (*** to shrink the size),

2) Zip up the MDB (*** to further shrink the size)

3) Attach the file for upload here (using the 'Attach File function below) ... removing any sensitive data of course.

4**** And please give a clear explanation of exactly how to reproduce the problem or what you are trying to do.

NOTE:  If you have an A2007 ACCDB, please convert to A2003 MDB.
mx
0
AbacusInfoTechAuthor Commented:
Okay, this is the database. Hook up the linked tables, open the front end, go to 'Jobs'.

On this screen you'll see *ALL* jobs for all clients both in progress and complete.

You can filter for a given client and both subforms will update.

If you choose Willy Wonka you'll get filtered data in both subforms.

When you press 'clear filter' only one subform will revert to all data

You can swap the code around on the button as described earlier but then only the other subform will revert to unfiltered data.



eedb.zip
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
standby
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
weird I must say ...

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well ... I fixed it with a trick I've done before (see clear code). Should not be necessary ... but ... whatever it takes.

I also renamed the subform controls to be different than the actual form names.

mx
nnc-frontend-EE-MX01.zip
0

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
AbacusInfoTechAuthor Commented:
Thanks, I'll take a look now.
0
AbacusInfoTechAuthor Commented:
Thank you very much for your help, works just fine!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome. Thank you for using Experts Exchange.  Spread the word :-)

mx
0
onewheelhkCommented:
Would have made it easier if you had posted the answer(I have included your code below). I have a similar problem and this seems to be a known bug in Access and the solution is to 'change the recordsource' but I wasn't sure how to do it. Reading your code has shown me. Thanks
Private Sub btnClearClientFilter_Click()
    Dim x As String
    With Me
        .cboClientFilter.Value = ""
       
        x = .fsubJobsInProgress.Form.RecordSource
        .fsubJobsInProgress.Form.Filter = ""
        .fsubJobsInProgress.Form.FilterOn = False
        .fsubJobsInProgress.Form.RecordSource = x
        x = .fsubJobsComplete.Form.RecordSource
        .fsubJobsComplete.Form.Filter = ""
        .fsubJobsComplete.Form.FilterOn = False
        .fsubJobsComplete.Form.RecordSource = x
    End With

End Sub

Open in new window

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, I suppose, but at the time, the OP was the only one needing the fix.  You can always download the db and check it out ...

mx
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.