How to make running balance in a form

[Guest]

I have a form to record annual leave1) Beginning balance (2)no. days to
take (3) end balance

(1) Begin balance is sourced from Master Leave table
(2) No. days to take is form input
(3) end balance is (1)-(2), and will update back to Master table

I wrote the following code but not work. Would appreciate any expert advice
and many thanks in advance

****************************************************
Dim fx1 As adodb.Recordset 'from master leave table
Dim t As Variant ' caculate leave balance

Set fx1 = New adodb.Recordset
fx1.Open "TbMasterLeave", CurrentProject.Connection, adOpenStatic,
adLockOptimistic


Do While Not fx1.EOF
If fx1("EmployeeID") = Forms!FmTempLeave!EmployeeID Then
fx1("AnnualEntitled") = t
Me!AnnualEntitled = fx1("AnnualEntitled")
t = fx1("LeaveBal")
Me!CumLeave = t

t = t - Me!LeaveTaken
Me!LeaveBal = t

End If
fx1.MoveNext

Loop

 

[John Welch]

Kayline- Are you working with unbound forms on purpose? I'm not sure why you
can't just base your form on the 'Master' table, which will allow you to
have textboxes bound to "annual entitled" and 'leave balance'. Then you can
have an unbound textbox where you can enter current leave taken and a button
that when clicked runs code like this:

me.txtleavebalance = me.txtleavebalance-me.txtLeaveTaken.

Do you not want to keep records of leave dates and amounts? If so , you
probably want to have a separate table called LeaveEvents or something that
records leave amounts, dates, reasons, etc. You could make a subform to show
these events and sum them to get total leave taken.
Hope this helps
-John

 

[Guest]

Thank you very much for your wise advice, Mr Welch.
This is my first time using Newsgroup. I am amazed there is really kind and
wise people who will be so generous to volunteer knowledge to inexperience
user like me

I have had a separate table similar to LeaveEvents to track the period & no.
of days. I have a form for this LeaveEvents.I want the form to provide
(1) reference of balance of leaves,
(2) sum no. of leave days taken YTD,
(3) show users no. of days left, and
(4) update end balance in (3) above to the balance of lease in another
Matser Table.

With your wise enlightenment, I think I will create
(1) a MasterTable Form,
(2) make LeaveEvent table as subform,
(3) do the calculation per your advice
(4) do a update query to update the end balance back to the Master Table

Hope you would not mind to advice additional guidance.

 

[Guest]

Thanks a million for your time and expert advice , Mr Welch. I am able
to create a better form and do the running sum based on your advice. I have
been struggling for the whole week trying to read books and help section.

I could not find the answer until your advice come along. Many many thanks
again.
Jaylin
*****Jaylin Message ended*******