Yes/No Prompt

[ladybug via AccessMonster.com]

I have a Leave of Absence table that has Employee ID, type of leave, Begin
date, and End Date.
I have another table that has all employees. This includes Employee name and
Id. I want to add to this table a yes/no box.

If an employee has an entry in the Leave of Absence table with no end date
(means they are currently out on leave) I want the Yes/No box to have "Yes"
selected. If it has an end date or they do not have an entry in the Leave of
Absence table at all I wnat the Yes/No box to have "No" selected.

Can anyone assist me in a solution to do this? Thank you in advance.

 

[Klatuu]

You really don't need an additional field to do this. Make something like
this the Control Source of your Check box:

=IIf(IsNull(DLookup("[EndDate]","Absence", "[EmployeeID] = '" &
Forms!MyFormName!Employee & "'"), True, False)

 

[ladybug via AccessMonster.com]

Thank you for the suggestion. I apoligize, but I don't know how to make this
work. I changed your code and entered in the appropriate name for Employee
ID and my Leave of Absence Form. Is that all I needed to change?

=IIf(IsNull(DLookup("[EndDate]","Absence", "[chrUserID] = '" &
Forms!sfrmLeaveofAbsence!Employee & "'"), True, False)

What do you mean by make it the Control Source of my check box? When I think
of Control Source I think of forms. I do not have this check box in a form.
It is just in my Employee table.

The Leave of Absence table is to keep a running track of each time an
employee is out on leave. I didn't want to just put a Yes/No check box on
the form because that would not keep track of each Absence entry. I hope
that I am making some sense. Sorry, I do not know much about code.

You really don't need an additional field to do this. Make something like
this the Control Source of your Check box:

=IIf(IsNull(DLookup("[EndDate]","Absence", "[EmployeeID] = '" &
Forms!MyFormName!Employee & "'"), True, False)

I have a Leave of Absence table that has Employee ID, type of leave, Begin
date, and End Date.

[quoted text clipped - 7 lines]

Can anyone assist me in a solution to do this? Thank you in advance.

 

[Klatuu]

What you are wanting to do is store a calculated value in a table. This is
never a good idea. You should only calculate values to present in forms,
queries, or reports.
If you are not using a form to enter data in your table, that is another
error. You may as well use Excel, it would be easier.

Thank you for the suggestion. I apoligize, but I don't know how to make this
work. I changed your code and entered in the appropriate name for Employee
ID and my Leave of Absence Form. Is that all I needed to change?

=IIf(IsNull(DLookup("[EndDate]","Absence", "[chrUserID] = '" &
Forms!sfrmLeaveofAbsence!Employee & "'"), True, False)

What do you mean by make it the Control Source of my check box? When I think
of Control Source I think of forms. I do not have this check box in a form.
It is just in my Employee table.

The Leave of Absence table is to keep a running track of each time an
employee is out on leave. I didn't want to just put a Yes/No check box on
the form because that would not keep track of each Absence entry. I hope
that I am making some sense. Sorry, I do not know much about code.

You really don't need an additional field to do this. Make something like
this the Control Source of your Check box:

=IIf(IsNull(DLookup("[EndDate]","Absence", "[EmployeeID] = '" &
Forms!MyFormName!Employee & "'"), True, False)

I have a Leave of Absence table that has Employee ID, type of leave, Begin
date, and End Date.

[quoted text clipped - 7 lines]

Can anyone assist me in a solution to do this? Thank you in advance.