missing info from form

[skipatco]

I have a table to keep track of employees. In this table I have an box for
"active" or "inactive". If an employee doesn't work for me anymore, they go
to inactive. I don't want to erase them since they are connected to other
tables in the database. Each employee is assigned a unique number that is
used for other tables.

I have set up a query to find "active" employees. I use the query to
propigate combo boxes on several forms. This helps to limit the number of
employee names I have to choose from since I would never choose and
"inactive" employee when using forms. The problem is when I move and
employee to "inactive", they no longer show up in the combo box on previous
pages of the form where they were legitamately used while employeed. Their
unique id number still exists in the corresponding table. When I review older
forms entries or need to create a report, I need their names to show up.

Any suggestions on how to accomplish this?
S.

 

[Brian]

This is a perennial problem: as soon as you filter something out of a combo
box RowSource, it no longer appears, even when it is valid in an existing
record.

What I have done in this case is to show the Active/Inactive status as a
column in the combo box, and then sort first by this.

Here is an example:

SELECT Employee.EmployeeID, [LastName] & ", " & [FirstName] AS Employee,
IIf([Active],"Active","Inactive") AS Status, Employee.Active
FROM Employee
ORDER BY Employee.Active, Employee.LastName, Employee.FirstName;

This way, Inactive employees go in their own alphabetical list at the bottom
of the combo box list. A user could, of course, still navigate down the list
or type the name and get the inactive employeee, so you can then add code the
BeforeUpdate of the control or form if you really want to prevent anyone from
using an inactive employee. In my experience, though, it is normally
sufficient to simply sort them all to the bottom (out of sight, out of mind).

 

[skipatco]

That does the trick.
Thanks,
S.

This is a perennial problem: as soon as you filter something out of a combo
box RowSource, it no longer appears, even when it is valid in an existing
record.

What I have done in this case is to show the Active/Inactive status as a
column in the combo box, and then sort first by this.

Here is an example:

SELECT Employee.EmployeeID, [LastName] & ", " & [FirstName] AS Employee,
IIf([Active],"Active","Inactive") AS Status, Employee.Active
FROM Employee
ORDER BY Employee.Active, Employee.LastName, Employee.FirstName;

This way, Inactive employees go in their own alphabetical list at the bottom
of the combo box list. A user could, of course, still navigate down the list
or type the name and get the inactive employeee, so you can then add code the
BeforeUpdate of the control or form if you really want to prevent anyone from
using an inactive employee. In my experience, though, it is normally
sufficient to simply sort them all to the bottom (out of sight, out of mind).

I have a table to keep track of employees. In this table I have an box for
"active" or "inactive". If an employee doesn't work for me anymore, they go
to inactive. I don't want to erase them since they are connected to other
tables in the database. Each employee is assigned a unique number that is
used for other tables.

I have set up a query to find "active" employees. I use the query to
propigate combo boxes on several forms. This helps to limit the number of
employee names I have to choose from since I would never choose and
"inactive" employee when using forms. The problem is when I move and
employee to "inactive", they no longer show up in the combo box on previous
pages of the form where they were legitamately used while employeed. Their
unique id number still exists in the corresponding table. When I review older
forms entries or need to create a report, I need their names to show up.

Any suggestions on how to accomplish this?
S.