Handling Combo box Active/Inactive rows?

[salad]

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?

[Allen Browne]

The simplest solution is to sort the inactive records to the bottom. That
way the old names still show correctly (even if the bound column is hidden),
but as you type a name, it always chooses the active ones.

This kind of thing:
SELECT ClientID, Surname & ", " & FirstName AS FullName
FROM tblClient
ORDER BY Inactive DESC, Surname, FirstName;

BTW, I've developed the habit of using a saved query for these little
lookups. Then if the end user does decide they want a different result (such
as eliminating the inactive ones from the combo), there is just one query to
change, and every place in the app that uses this combo will update
correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"salad" <o...@vinegar.com> wrote in message
news:_MH3h.3585$ig4...@newsread2.news.pas.earthlink.net...

[salad]

Allen Browne wrote:
> The simplest solution is to sort the inactive records to the bottom. That
> way the old names still show correctly (even if the bound column is hidden),
> but as you type a name, it always chooses the active ones.
>
> This kind of thing:
> SELECT ClientID, Surname & ", " & FirstName AS FullName
> FROM tblClient
> ORDER BY Inactive DESC, Surname, FirstName;
>
> BTW, I've developed the habit of using a saved query for these little
> lookups. Then if the end user does decide they want a different result (such
> as eliminating the inactive ones from the combo), there is just one query to
> change, and every place in the app that uses this combo will update
> correctly.
>

I like your idea of sinking the inactives to the bottom and floating the
actives to the top.

[David W. Fenton]

"Allen Browne" <Allen...@SeeSig.Invalid> wrote in
news:454f49ca$0$3043$5a62...@per-qv1-newsreader-01.iinet.net.au:

> The simplest solution is to sort the inactive records to the
> bottom. That way the old names still show correctly (even if the
> bound column is hidden), but as you type a name, it always chooses
> the active ones.

But you'd also want BeforeUpdate validation to prevent the choosing
of an inactive one.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

salad <o...@vinegar.com> wrote in
news:73K3h.3098$L6....@newsread3.news.pas.earthlink.net:

That sounds like a good idea to me, too.

Another thing you could do is include all of them in the combo box
that is used for display and editing, but when you're adding a new
record, filter them out.

And, as I said in the response to Allen, you'll need a BeforeUpdate
validation to prevent a user from changing the value to an inactive
employee.

[CDMAP...@fortunejames.com]

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
CDMAP...@FortuneJames.com

[salad]

Yeah, I know how to filter. That's only part of the question.

Let's say you were my employee until last week. IF I had something like
Select Emp.* From Employee Where Active = True
for the combo box rowsource, you would not appear in records that I've
added this week. That's well and good.

But if I used that combo and reviewed a record where you were selected
from last week or before that field would be blank. The EmpID value
would still exist, but the combo would be blank.

So do I change it to
Select Emp.* From Employee
Where Active = True Or EmpId = [EmpID]
Now I show all active records or the value for the ID selected previously.

I suppose I could check in the OnCurrent event. If the record is new or
the value blank, create a SQL statement to select all records that are
active and stuff into the rowsource. If old record or value filled in,
create a SQL statement to select all records regardless of active status
and make that the rowsource.

I like Allen's approach to sorting the names by active status. This way
I am not designing rowsources depending on status of the record or value
of the combo.

[Allen Browne]

Yes, good thought.

I usually warn (rather than block) in Form_BeforeUpdate.
Sometimes old data needs be be entered.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David W. Fenton" <XXXu...@dfenton.com.invalid> wrote in message
news:Xns987399AF4A53Bf9...@127.0.0.1...

[CDMAP...@fortunejames.com]

salad wrote:

> Yeah, I know how to filter. That's only part of the question.

I was hoping to avoid the question :-).

>
> Let's say you were my employee until last week. IF I had something like
> Select Emp.* From Employee Where Active = True
> for the combo box rowsource, you would not appear in records that I've
> added this week. That's well and good.
>
> But if I used that combo and reviewed a record where you were selected
> from last week or before that field would be blank. The EmpID value
> would still exist, but the combo would be blank.
>
> So do I change it to
> Select Emp.* From Employee
> Where Active = True Or EmpId = [EmpID]
> Now I show all active records or the value for the ID selected previously.
>
> I suppose I could check in the OnCurrent event. If the record is new or
> the value blank, create a SQL statement to select all records that are
> active and stuff into the rowsource. If old record or value filled in,
> create a SQL statement to select all records regardless of active status
> and make that the rowsource.
>
> I like Allen's approach to sorting the names by active status. This way
> I am not designing rowsources depending on status of the record or value
> of the combo.

Thanks for the detailed explanation. I understand the problem.
Allen's idea is a good one but the question is a good one also. I'll
spend some time late tonight thinking about it.

James A. Fortune
CDMAP...@FortuneJames.com