Link to home
Start Free TrialLog in
Avatar of Michael Franz
Michael Franz

asked on

Access Combo Box Filters for Active / Inactive based on Check Box

I have a Form (Sponsorships) with a Combo Box that is linked to a Table (Vendor)... The Vendor Table has "ACTIVE" and "INACTIVE" vendors. I want the Combo Box for the Vendors Names to only display "ACTIVE", but it someone "checks" the box on the form then "INACTIVE" vendors will also display.
Avatar of PatHartman
PatHartman
Flag of United States of America image

Add criteria to the RowSource query of the combo to look at the checkbox.

Select ...
From tblVendors
Where (Nz(Form!yourform!chkYourFlag, -1) =  True  And tblVendors.ActiveFlg = True) Or tblVendorsActiveFlg = False;

The query treats null as true so when the form loads, "true" is assumed.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Dale,
Doesn't this expression always return the active vendors regardless of what the form control says?
Pat, yes.  That is how I interpreted the OPs question.

"then 'INACTIVE' vendors will also display"

Bold is my emphasis.
You're probably correct.  I always use three states to avoid the ambiguity.  I also display the word "Inactive" whenever inactive items are shown in the combo.
I tend to do that as well, Pat.

All Records
Active
Inactive

and use the After Update event of the combo to simply set the Filter and FilterON properties to display the correct set of records.
Avatar of Michael Franz
Michael Franz

ASKER

Thank you very much for the help. I have it working!