Calculating a total in a subform based on the record status

  • Thread starter Thread starter Ammo
  • Start date Start date
A

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
 
Ammo said:
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'
Click to expand...


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
 
Back
Top