Yes/No Question

[Ryan]

I have an active employee Yes/No field in a table. If my employee has a
checkmark in the active field he shows up on a form called Batch Cover Sheet
and works great. However, If I uncheck the employee leaves then all previous
records that are related to that employee are left orphaned without a value.
Is there a way to keep the previous records untouched and only remove that
employee from selectable items in the field?

 

[mscertified]

That is the whole point of the active switch - so you don't have to delete
the employee. Once you delete the employee, all related records must cease to
exist (assuming you have referential integrity turned on).

"If I uncheck the employee leaves" - what does this mean???

-Dorian

 

[Ryan]

If I uncheck the box when the employee leaves. Sorry about that. I do have
referential integrity turned on. When I uncheck the box, all the records for
that employee show blank instead of that employees name just not showing up
in my selection combo box. All I want to happen when I uncheck the box is
for that employees name not to be selectable anymore, not to remove them from
all of the records they were in previously.

 

[Allen Browne]

Ryan, the simplest solution is to sort the inactive records to the bottom of
the list, rather than eliminate them.

The RowSource will be a query like this:
SELECT EmployeeID, Surname & " " + [FirstName] AS FullName, Active
FROM Employee
ORDER BY Active, Surname, FirstName;

Use the BeforeUpdate event of the control (or the form) to warn about or
block the selection of inactive individuals.