Tracking time off form error

[Indigobuni]

I've built a simple db for tracking employee's PTO (paid time off). I'm experiencing an error with a field I want to add to the form, and can't resolve it. Sad, because I don't think it's a complicated fix, but the solution evades me.

Two tables
1 - Employees (name, hire date, Total Annual PTO)
2 - Empl Days Off Data (Date off, hours used, reason)
The two have a one-to-many relationship via ID (Employees) and Employee ID (Empl Days Off Data)

Two queries
1 - Total PTO hours used by employee (Name, Total Annual PTO, sum of hours used for each employee - based off the Empl Days Off Data)
2 - PTO Remaining (Name, Total Annual, Sum of hours, PTO Remaining)
Query 2 is based off query 1

One form with a subform
Subform lets me enter the data for each employee. The form shows name, hire date, Total Annual.

What I want is to show the PTO Remaining on the form as well. I've attached the db with limited data in case someone wants to see. Thanks for any help.

 

[jzwp22]

One way to display the remaining amount is to use a domain aggregate function DSum() that would sum up how many hours have been used and subtract that from the total allowed. The domain aggregate functions are not the most efficient, but I think that in this case it should be OK. The domain aggregate functions are a little tricky to set up because of the syntax, so you will want to consult the Access help section for more detail. If the DSum() does not find any records to sum, it returns Null which doesn't help you in calculating the remaining amount (Total PTO-NULL does nothing for you), so you will have to check the result of the DSum() to see if it is Null and if so, give you a zero instead, so we will need to nest the DSum() within within an IsNull() function which in turn has to be nested in the conditional function IIF() .(check the help section for more on these functions also). You also have to make sure the DSum() sums the correct records which means you need to limit the records to the employee shown on the main form and limit the records to the current year (I assume). With all that said, this is what the general expression will look like this:

=[Total Annual PTO]-IIF(IsNull(DSum()),0,DSum())

The general form of the IIF() function is IIF(expression,true part, false part)

So if the IsNull() expression is true, a zero is returned, if it is false the value of the DSum() function is returned.

Putting in the full syntax of the DSum() function, this is what your textbox expression will look like:



=[Total Annual PTO]-IIf(IsNull(DSum("[Hours used]","[employee days off data]","[employee ID]=" & [ID] & " AND year([Date off])=" & Year(Date()))),0,DSum("[Hours used]","[employee days off data]","[employee ID]=" & [ID] & " AND year([Date off])=" & Year(Date())))


The other thing is that you would probably want the remaining value to be updated when records are added/changed or deleted in the subform. In order to accomplish this, you need some code in the After Update and On Delete events of the subform. That code very simply just requeries the control on the main form that holds the remaining amount. In your case the textbox name was text42, so the code would look like this:

Me.Parent.Text42.Requery

Me. is just a short hand for the current form. Parent is a shorthand for the main form on which the subform is located. Requery just tells Access to rerun the expression in the text42 textbox.

I've attached your databased modified with the expression and code.

 

[Indigobuni]

Thank you so much! This is just what I was looking for.

 

[jzwp22]

You're welcome.