MS Access VBA Filter

I am using MS Access 2016. I have an unbound main form with a bound subform.
On the Subform I have a True/False control (Whether a customer is active or inactive).
I want the default view to NOT include Inactive customers but have a tick box that will then include inactive customers.
I have used me.filter before bust I think I am having a problem with the True/False field.
Any help greatly appreciated.
Iain
iainmacleodAsked:
Who is Participating?

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

x
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.

Fabrice LambertConsultingCommented:
Hi,

You can base your subform on a query, and adjust the WHERE clause.
Dale FyeOwner, Developing Solutions LLCCommented:
The way I would do this is have the record source of the subform simply:

SELECT Field1, Field2, ... , FieldN FROM yourTable

But I would have the subforms filter property set to: [Active] = -1
with the FilterOnLoad set to Yes

A checkbox, in this instance might be confusing, and would not give you the ability to select Active, Inactive, or Both.  Instead of a checkbox, you might want to consider using a combo box, with values "Active;Inactive;Both", as this would give you the ability to filter on either active or inactive customers.  I would put this combo in the main form, right above the [Active] field in the subform.  Set the Default value of the combo to "Active".  The code in the AfterUpdate event of that combo would be:

Private Sub cbo_Active_AfterUpdate

    if me.cbo_Active = "Both" then
        me.subformcontrolname.form.Filter= ""
        me.subformcontrolname.form.FilterOn = false
    elseif me.cbo_Active = "Active" Then
        me.subformcontrolname.form.Filter= "[Active] = -1"
        me.subformcontrolname.form.FilterOn = true
    elseif me.cbo_Active = "Active" Then
        me.subformcontrolname.form.Filter= "[Active] = 0"
        me.subformcontrolname.form.FilterOn = true
    end if

End Sub

Open in new window

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
iainmacleodAuthor Commented:
Thank you Dale, you are a star. That is the perfect solution.
All the best
Iain
Dale FyeOwner, Developing Solutions LLCCommented:
glad to help.
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.