Combo showing non current employee

[swas]

Hello,

I have employees in a table. For a given job sheet form, the employee is
selected with a combobox listing all current employees. All fine.

If an employee leaves, they are marked as no longer current.

My problem is, if an old job record is looked at, the employee is not listed
since they are not current. The combo is blank.

I have gotten around this prior by editing the rowsource for [all current
employees] OR [selected employee].

But this only works in single form. I need it in a continuous form, and not
sure whether the best option is to reference the field in the sql, or have
dlookup, of if there is a more elegant approach I am not thinking of.

Comments appreciated.


swas

 

[Allen Browne]

This issue crops up regularly. Essentially, if the combo's bound column is
hidden, and its RowSource excludes the value, then it has nothing it can
show and so it appears blank.

The simplest way around that is NOT to the inactive values, but to sort them
to the bottom of the list so the AutoComplete doesn't go to them unless
there are no matching names.

Assuming a table with a yes/no fields named Inactive (check the box if the
person is inactive), you set the combo's RowSource to something like this:
SELECT ClientID,
Surname & ", " + FirstName & IIf([Inactive], " (Inactive)", Null) AS
FullName
FROM tblClient
ORDER BY Inactive DESC, Surname, FirstName, ClientID;

A more complex solution involves placing a text box over the combo, and
including the client table in the form's RecordSource so you have the name
available to show in the text box. When the text box gets focus, you
SetFocus to the combo, which lists only the active names for the user to
choose from. In general, I think this complexity is unwarranted, and it
still gives problems (e.g. trying to enter an old record once the employee
has left.)

 

[swas]

Thanks Allen.

That's nice and simple. I suppose depends on staff turnover ...the combos
would grow over time. Could also filter by date for the last couple of years
if a hassle.

Solves my problem though.


Thanks again.


swas