Updating a field in a Master form from a subform

[Guest]

Hello!
I would like an amount in a field on a Master form updated when a logical
field in a subform is checked by the user. If the logical field which is
called Approved? is checked, then take the amount stored in ApprovedCap field
in the subform and add that amount to the TotalCap field in the Master form.

I know this requires a logical function like the IF function, but I'm not
sure how to build it. Please help! Thank you very much. - Yvette

 

[Marshall Barton]

I would like an amount in a field on a Master form updated when a logical
field in a subform is checked by the user. If the logical field which is
called Approved? is checked, then take the amount stored in ApprovedCap field
in the subform and add that amount to the TotalCap field in the Master form.

I know this requires a logical function like the IF function, but I'm not
sure how to build it. Please help! Thank you very much. - Yvette

use an expression in the main form's text box:

=TotalCap + IIf(subform.Form.Approved,
subform.Form.ApprovedCap, 0)

 

[Guest]

Hi Marsh,

Thank you for the code. It worked but the TotalCap field does not keep or
store the result that has been added to it. It resets to the original cap
amount.

Also when I go to the next new record and click on the approved? field it
adds the new amount to the original cap amount again, but totalcap never
increases, and resets again when I go to the next record to the original cap
amount.

This is how I wrote the code based on your response.

TotalCap =[Cap_]+IIf([DETAILED PROJECT LISTING].[Form]![Approved?],[DETAILED
PROJECT LISTING].[Form]![ApprovedCap],0)

I hope what I'm saying is not confusing to you. Does this make sense to
you? Please I need your help again. Thank you.

 

[Marshall Barton]

I didn't think I was confused before, but now I am sure I am
at least missing something.

Maybe I need to understand what the approved cap value
represents. I thought it was a value in the subform
records, but now it appears that you want some kind of total
of multiple records.

You said that the main form value "resets" when you navigate
to a different record in the subform. But, it doesn't
reset, it only caclulates the value using the current
record.

Is the amount you want displayed on the main form the sum of
all the records in the subform??

If so, then the subform needs to calculate the sum by using
a text box in its footer section named txtTotalApprovedCap
using the expression:
=Sum(IIf([Approved?], ApprovedCap, 0))

The main form would then use:

TotalCap = [Cap_] + [DETAILED PROJECT
LISTING].[Form]!txtTotalApprovedCap
--
Marsh
MVP [MS Access]

Thank you for the code. It worked but the TotalCap field does not keep or
store the result that has been added to it. It resets to the original cap
amount.

Also when I go to the next new record and click on the approved? field it
adds the new amount to the original cap amount again, but totalcap never
increases, and resets again when I go to the next record to the original cap
amount.

This is how I wrote the code based on your response.

TotalCap =[Cap_]+IIf([DETAILED PROJECT LISTING].[Form]![Approved?],[DETAILED
PROJECT LISTING].[Form]![ApprovedCap],0)

I hope what I'm saying is not confusing to you. Does this make sense to
you? Please I need your help again. Thank you.

I would like an amount in a field on a Master form updated when a logical
field in a subform is checked by the user. If the logical field which is
called Approved? is checked, then take the amount stored in ApprovedCap field
in the subform and add that amount to the TotalCap field in the Master form.

I know this requires a logical function like the IF function, but I'm not
sure how to build it. Please help! Thank you very much. - Yvette