Updating calculated fields on one form from another

[Viv1]

The database has a form called Member, when I created the form I selected 4 fields from the two Fees tables linked using the Membership_Grade, as the total amount payable by each member is made up by 1 fee field or adding 2 of the fees fields together. To work out the total amount there is a VBA function that is called every time someone’s details change on the Member form. The problem is the fees are updated every year and although this is reflected in the 4 linked fees fields, the balance derived using the Call function is not used so the balance remains the same. Any ideas as the Call function uses several factors to calculate the balance and otherwise I have to click a button for each member when their details are shown on the form.

Many thanks, Viv

 

[KenReay]

Hi

Do not quite follow that, are you saving the Total Fee in the Memebers table?, if yes, this problem demonstrates exactly why you should not do that (it breaks the rules of good database design), but if you must, you will have to include a routine in the anual update of fees to also update the Total saved in the member record

Regards

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

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

UK

 

[KenReay]

Hi

Do not quite follow that, are you saving the Total Fee in the Members table?, if yes, this problem demonstrates exactly why you should not do that (it breaks the rules of good database design), but if you must, you will have to include a routine in the anual update of fees to also update the Total saved in the member record

Regards

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

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

UK

 

[Viv1]

Hi,
Yes the balance for each member is a field in the Member table, the fees are in the fees table and the calculations are performed in VBA on the member form.

I can't change this design now as we have members entered and as it would have been a 1 to 1 relationship between Member and Member_Balance I didn't think a separate table was required.

Is there nowhere to refresh all records and call my function? I have tried Form_Load and then Call function but this doesn't seem to work.

Thanks, Viv

 

[PHV]

And what about an update query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Viv1]

I would have to write 8 update queries and have a to run them all, I just wondered if there was any other way around it? No such luck!

Thanks, Viv