Form calculating

[Maria L]

I am building a Vacation database for my group. Using a
form the associate is able to pick a vacation date
choosing to take a whole day (1) or a half a day (0.5). I
want to the user to be able to enter their name, date and
using a check box select a whole day or half a day. After
they click save the Days_Left value in the Associate2
table will reflect the total-[ASSOCIATE2]![T V/F/P DAYS]
minus the vacation day (either a 1 or a 0.5).

If Me![WHOLE DAY].Value = True Then
[ASSOCIATE2]![DAYS_LEFT] = [ASSOCIATE2]![T V/F/P
DAYS] - 1
End If

What is the best way to do this?
I am building a Vacation database for my group. Using a
form the associate is able to pick a vacation date
choosing to take a whole day (1) or a half a day (0.5). I
want to the user to be able to enter their name, date and
using a check box select a whole day or half a day. After
they click save the Days_Left value in the Associate2
table will reflect the total-[ASSOCIATE2]![T V/F/P DAYS]
minus the vacation day (either a 1 or a 0.5).

If Me![WHOLE DAY].Value = True Then
[ASSOCIATE2]![DAYS_LEFT] = [ASSOCIATE2]![T V/F/P
DAYS] - 1
End If

What is the best way to do this?

 

[Reid]

It's rarely a good idea to store a totals value in a table. Rather, you
should store each deduction in hours in a table, then let the form calculate
the remaining total real-time. For example, say you have the following two
tables:

Table1: tblPeople
Columns: PersonID, FirstName, LastName, YearlyVacationDays, etc.

Table2: tblPersonVacationDays
Columns: ID (Autonumber), PersonID (foreign key to tblPeople), NumDays,
VacDate

For each person in tblPeople, you store the total number of days the person
is alloted per year (or whatever period of time you're dealing with).

As vacation days are used, you simply add their PersonID, the date and the
number of days used in tblPersonVacationDays. Over the course of the year,
each person will have many records in this table.

Then, on your form, you simply total up tblPersonVacationDays.NumDays for a
given date range and subtract it from tblPeople.YearlyVacationDays. This way
you have all the detail records and can quickly and easily compute the
current total on demand.

Hope this helps.

Reid