calculate a control and store in table.

[Guest]

I have this form which is bound to a table and shows the vacation days of an
employee.Whenever an employee vacation details is entered the form's controls
should be updated so I have this code which I used in the OnCurrent
event.These are the controls

Forward -no.of days left from previous year.(Not a calculated field.)
No.of Days Due- no.of days left for current year.(Not a calculated field.)
Balance -Total no. of days left.(Not a calculated field.)


With Me

If .Forward > .To_Be_Taken Then
.Forward = .Forward - .To_Be_Taken

Else:
If .Forward = 0 Then
.No_of_Days_Due = .No_of_Days_Due - .To_Be_Taken
.Balance = .No_of_Days_Due - .To_Be_Taken

Else:
If .Forward < .To_Be_Taken And .Forward > 0 Then
.Forward = 0
.No_of_Days_Due = .No_of_Days_Due - .To_Be_Taken - .Forward
.Balance = .No_of_Days_Due - .To_Be_Taken - .Forward

End If

End If

End If

End With

But the problem is that it doesnt work.Can anyone pls tell me what I am
doing wrong.

 

[John W. Vinson]

I have this form which is bound to a table and shows the vacation days of an
employee.Whenever an employee vacation details is entered the form's controls
should be updated so I have this code which I used in the OnCurrent
event.These are the controls

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

You say "it doesn't work". In what way does it not work? What error messages
or wrong values are you getting?

John W. Vinson [MVP]

 

[Guest]

ok i have written the code in a function and called it in the update event of
the controls but the values are wrong.I think i need to check my function
again.Thx

 

[Guest]

ok i have a main form were i can view an employees leave details and a
command button to open another form in order to enter an employees leave
details if there is any left.The main form has a subform which shows the type
of leave the employee took,the date of apllication,etc.When I open the form
from the main it should be a data entry form with the caculated
fields-Forward,Balance,No_of_Days_Due should already contain the values from
the previous leave application for that employee. and when I enter the 2
dates the caculated fields should show the new values.Is this possible??