Calculating a total in a subform based on the record status

[Ammo]

Hi,

I have main form called 'frm_GrantPot' that has the
field 'AvailableGrant', the user can enter a figure in
this field.

This main form consists of a sub
form 'frm_GrantAllocation' which allows the
user to enter multiple records (datasheet view) which
relates to the record in the main form. Each record that
the user enters in the sub form, the user either selects
or does not select a 'yes' or 'no' (tick for 'yes') in
a 'GrantStatus' field of the subform. The subform also
consists of a field called 'AmountGrantAppliedFor'

I wish to create a field on the main form called 'Total
Grant Remaining'

The formula will be

GrantSpentToDate (Main Form) = AvailableGrant(Main Form)-
AmountGrantAppliedFor(Sub Form) where 'GrantStatus' field
is selected as 'yes'

How can I implement this?

Hope someone can help?

Best Wishes

Ammo

 

[Marshall Barton]

I have main form called 'frm_GrantPot' that has the
field 'AvailableGrant', the user can enter a figure in
this field.

This main form consists of a sub
form 'frm_GrantAllocation' which allows the
user to enter multiple records (datasheet view) which
relates to the record in the main form. Each record that
the user enters in the sub form, the user either selects
or does not select a 'yes' or 'no' (tick for 'yes') in
a 'GrantStatus' field of the subform. The subform also
consists of a field called 'AmountGrantAppliedFor'

I wish to create a field on the main form called 'Total
Grant Remaining'

The formula will be

GrantSpentToDate (Main Form) = AvailableGrant(Main Form)-
AmountGrantAppliedFor(Sub Form) where 'GrantStatus' field
is selected as 'yes'

Make sure the subform has its Form Footer section. Then add
a text box (name it txtGrantUsed) to the footer section and
set its control source expression to:

=Sum(IIf(GrantStatus, AmountGrantAppliedFor, 0))

Now, back on the main form, set yourTotal Grant Remaining
text box's vontrol source expression to:

=AvailableGrant - subformcontrolname.FORM.txtGrantUsed