Updating a field in a Master form from a subform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Yvette - 22 Nov 2005 16:29 GMT

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

Reply to this Message

Marshall Barton - 23 Nov 2005 00:49 GMT

>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
[quoted text clipped - 3 lines]
>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)

Signature

Marsh
MVP [MS Access]

Reply to this Message

Yvette - 29 Nov 2005 22:05 GMT

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.

> Hello!
> I would like an amount in a field on a Master form updated when a logical
[quoted text clipped - 4 lines]
> 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

Reply to this Message

Marshall Barton - 30 Nov 2005 01:00 GMT

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

Signature

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
[quoted text clipped - 20 lines]
>> 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

Reply to this Message