Re: How to make running balance in a form

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.
--
Thanks a million for your time and expert advice :-)
Jaylin
*****Jaylin message end****************************************

"John Welch" wrote:

> 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
>
> "Kayline" <Kayline@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:283FAD4F-17F7-452B-802E-B076AAF79275@xxxxxxxxxxxxxxxx
> >I have a form to record annual leave:(1) 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
> > --
> > Kayline
>
>
>
.