Calculated Fields between subforms

[Peter Mac]

I have an "Orders" form that has three subforms:

1. Order Details
2. Payments
3. Shipping

I found in my Access book how to make the main Orders form see a calculated totals field from my Order Details Subform.

However, I need to share calculated fields BETWEEN subforms. For example, I need the Payments form to use calculated fields from the Order Details form. I can't make this work without getting the #error message. My book only tells me how to reference from Main form and subform.

How do I share calculated fields between subforms? (or can I?)

 

[CyberLynx]

What are you using now to reference from Main Form and SubForm?

.

 

[Peter Mac]

On my main form "Orders" I can see subform Order Details like this:

=[Order Details].Form!OrderTotal

I tried to reverse my logic and allow my "Payments subform" to see my Orders main form like this:

=Orders.Form!OrderSubTotal

That gave me a #Name? error

In my Payments subform I tried using

=[Order Details].Form!OrderTotal

to allow my Payments subform to see the Order Detail subform--also doesn't work.

It seems to me my subforms can't see my main form or other subforms.

Any ideas?

 

[Peter Mac]

I found this interesting article:
http://www.mvps.org/access/forms/frm0031.htm

I didn't build my forms in the manner they describe though.

For example, I have

1. Orders Main Form
2. Order details is a subform of ORders Main
3. Payments is a subform of Orders Main
4. Delivery is a subform of Orders main

In their case it seems they nested subforms inside each other. What do you think?

 

[CyberLynx]

I don't see anything wrong with the way you have laid out your Form. It's all a matter of preference in my opinion.

Because all your SubForms are all contained in one Form you need to specify the Main Form first when sharing between those SubForms.

Order Details is a SubForm contained within the parent Form named Orders Main. The line below works only if it is placed within the Control Source property window for a Control (a TextBox for example) located on the parent Form (Orders Main):

=[Order Details].Form!OrderTotal

Payments is also a SubForm contained within the parent Form (Orders Main). The same would apply if a Control on the Orders Main Form required data from the Payments SubForm:

=[Payments].Form!PaymentTotal

This however would do the same thing:

=[OrdersMain]![Payments].Form!PaymentTotal

In my opinion, you should always indicated where the Form is located by placing the parent Form first.

For example, to get the PaymentTotal from a Control on the Payments SubForm located on the Orders Main parent Form to a Control located on the Order Details SubForm use:

=[Orders Main]![Payments].Form!PaymentTotal

A control on the SubForm Order Details wants the contents of a Control in the Shipping SubForm use:

=[Orders Main]![Shipping].Form!.ShippingTotal

Keep in mind as well that you may wind up dealing with a Null value from time to time and you may want to handle that just in case. If this may be a situation then you can use:

=Nz([Orders Main]![Shipping].Form!.ShippingTotal, 0)


.