calculations between fields in a form

how can you do a calculation between two text boxes within a form (frmPerson)? I have a field with 'AmountPaid' and one with 'AmountDue', the AmountDue is worked out from Cost (located in tblMemberType, but not seen on frmPerson) minus the AmountPaid (located in tblPaymentHistory).


Also, am i correct in thinking that the AmountDue should not be in any table, as it is simply a calculation? if so, how is this added to the form, as when attempting to crack this problem, i recreated my person form, adding in amount paid, from a select query. this appeared to work, until you try to edit information in the table, at which point, it is all locked. any ideas?

um.. try putting a textbox (naming it AmountDue) in your frmPerson form and put the calculation formula:

=[Tables]![MemberType]![Cost]-[Tables]![PaymentHistory]![AmountPaid]

I'm not sure if it works tho

This isnt working for me, am i right in thinking that this should go in the Expression Builder?

The form is based on a query, on top of the table - dont know if that has anything to do with my problem.

=Sum([Tables]![MemberType]![Cost]-[Tables]![PaymentHistory]![AmountPaid])

something like I think?

its still not working for me - although im not totally sure where i should place that expression.

i've tried getting it working through queries, but, i can only manage to get the calculation as an expression, within a select query. is there a way of displaying this value without having to append it? (as the data shouldnt be in a table - as far as i can gather).

in the Default avlue option on the properties dialogue.

its still not working unfortunately. the text box just remains blank when that code is entered.