Yes/No Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ryan - 19 Mar 2007 20:40 GMT

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?

Reply to this Message

mscertified - 19 Mar 2007 22:37 GMT

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

> 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?

Reply to this Message

Ryan - 19 Mar 2007 22:46 GMT

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.

> 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
[quoted text clipped - 10 lines]
> > Is there a way to keep the previous records untouched and only remove that
> > employee from selectable items in the field?

Reply to this Message

Allen Browne - 20 Mar 2007 07:23 GMT

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.

Signature

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.

>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
[quoted text clipped - 5 lines]
> Is there a way to keep the previous records untouched and only remove that
> employee from selectable items in the field?

Reply to this Message