Updating other fields based on Info in One Field

[zonash001]

I need to record training details of employees using Access DB. For this, i've two tables ..one is empinfo containing following fields:
EmpID, Name, Desig, Dept, TotalTrHrs, TrHrsAvail, TrHrsBal

and the other one is TrDetails having the info:
EmpID,TrTitle,TrDate,TrDurationHrs,TrVenue,TrResources

i've designed a main form in access displaying Emp Info and its associated subform as Tr Details.
The forms works fine.

Now wht i need is that as soon as i enter TRDurationHrs (available in sub form) and press <Enter> to move on to next field, TRHrsAvail and TrHrsBal (available on main form) should get updated themselves with the values given underneath:

TRHrsAvail = cumulative sum of TRDurationHrs
TrHrsBal = TotalTrHrs - cumulatuve sum of TRDurationHrs

Wht control property do i need to set on TRDurationHrs field to get the work done?

 

[KenReay]

Hi

To begin, you are breaking a rule of good databse design, the total figures you have in the "header", can be calculated by summing the transactions, so why save them?, it is only a matter of time before some error occurs and they get out of step, leading to inconsistencies in your data. Similalry you do not need three columns in header record TotalTrHrs, TrHrsAvail, TrHrsBal (assuming you are going to break teh rule in teh first place) you need only TotalTrHrs, TrHrsAvail since TrHrsBal can be calculated in a query

Second, the underlying table in the subform is not updated until you move off the row, rather than off the column,

If you really want to do this despite the possible data consistency errors

Then you need code in the after update event of the subform

Dim rs as Recordset
Dim T as Long
Set Rs = Me.RecordsetClone
Rs.MoveFirst
T = 0
Do Until Rs.EOF
T = T + Rs!TRDurationHrs
Rs.MoveNExt
Loop
Parent.TotalTrHrs = T
Rs.Close
Set Rs = Nothing


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK