Solved Error #size! on calculated control based on subform value (1 Viewer)

[GaP42]

The form displays a Sales order, with the Sale Order items and the Payments received on dedicated tabs as subforms.
The Sales Order form has a calculated control showing the total of payments received. The formula is

=Val(Nz([frmsubPmt].[Form]![txtTotalPd],0))

The form when opened presents correctly, even if no payments have yet been received ($0 payments) - there are no payment records. (The Payments tab remains hidden until the Order is changed from Quote to Sale)
If a new sales order is created, it initially shows $0 for Total Payments. If I move to the Order Items tab (after selecting the purchaser - a required item) and then move to the Order Items tab to add items the #size! error appears in the text box.
The order can be closed and reopened - Total Payments shows $0 until I move to the Order items tab (no payments made)
If I add an item to the order #Size! remains. If I now close and reopen the Order, then #Size! no longer is displayed on any action.

BTW the calculated control for the total price for all items (on the Sales Order form) does not show this behaviour. It uses a reference like that for total paid: =[frmsubOrderItems].[Form]![SumAftDiscount]

Can't figure out how to fix the display.

Any assistance appreciated.

 

[arnelgp]

if you are using Navigation Form/subform, then there is only 1 subform for all your tabs and the "subform" is loaded
when you switch tab. initially, you are in Sales Order tab when the navigation form opens, and therefore your control Source
is correct. When you switch tab, [txtTotalPd] is no longer available.

what you need to do is used query to get the Total Payment and use a DLookup() to get the payment for each Payments.

 

[GaP42]

Thanks @arnelgp - the form is a single form with a tab control for the subforms. I will checkout the query suggestion for obtaining the total paid, rather than from the calculated control on the subform for payments: ie - use DLookup against a query that calculates the total paid for Sales Orders.

---

Thank you Arnelgp - now solved using a Dlookup of a query that provides the total of payments by orderid