salad wrote:
I was wondering how you handle active/inactive elements in a combo box.
Let's say you have a combo box to select an employee. Joe Blow has
been selected for many record however Joe has left the company and has
been flagged inactive. If you have a filter on the rowsource like
Where Active = True
then Joe's name would not show up in the combo list. This would be fine
if the combo is associated with a field that is null/0. It would not be
fine if this is an older record created when Joe was active as his name
would not be in the list and the combo would be blank.
Do you do present the combo with a filter like
Where (Active = True) or (EmpID = [ID])
or do you present only active emps if the record is new/combo unfilled
and present all if the record is not new/combo filled? IOW, change the
rowsource depending on the existing value of the field to be selected?
Or do even bother and just show all emps regardless of their active status?
Here are some partially related ideas about filtering on a checkbox in
case you need it:
Suppose I have a Y/N field called Exclude in a table. A search form in
Access containing an unbound checkbox can create a query that excludes
records where Exclude is checked in a table using syntax like
WHERE Exclude >= -(1 + Forms!frmSearch!chkExclude.Value)
I.e.,
Forms!frmSearch!chkExclude.Value = -1 =>
WHERE Exclude >= 0 [Excludes Checked Values]
Forms!frmSearch!chkExclude.Value = 0 =WHERE Exclude >= -1
This depends on a value of -1 for True. It can be modified as follows:
WHERE Abs(Exclude) <= 1 - Abs(Forms!frmSearch!chkExclude.Value)
I.e.,
Forms!frmSearch!chkExclude.Value = 1 or -1 =>
WHERE Abs(Exclude) <= 0 [Excludes Checked Values]
Forms!frmSearch!chkExclude.Value = 0 =WHERE Abs(Exclude) <= 1
But the OP wanted records where Active is not checked to disappear
rather than checked records to disappear:
The True = -1 dependent version of this is:
WHERE Active <= Forms!frmSearch!chkExcludeNonActive.Value
I.e.,
Forms!frmSearch!chkExcludeNonActive.Value = -1 =>
WHERE Active <= -1 [Excludes records with Active unchecked]
Forms!frmSearch!chkExcludeNonActive.Value = 0 =WHERE Active <= 0
A modified version:
WHERE Abs(Active) >= Abs(Forms!frmSearch!chkExcludeNonActive.Value)
I.e.,
Forms!frmSearch!chkExcludeNonActive.Value = -1 or 1 =>
WHERE Abs(Active) >= 1 [Excludes records with Active unchecked]
Forms!frmSearch!chkExcludeNonActive.Value = 0 =WHERE Abs(Active) >= 0
I realize that an Access form's checkbox value cannot equal 1. I
include that possibility to illustrate the SQL required to filter
against data containing either -1 or 1 to indicate True when an Access
form is not used to get the filter value.
James A. Fortune
CD********@FortuneJames.com