difficulty accessing computed text boxes on subform

[jimatqsi]

I'm getting "Application-Defined or object-defined error in procedure ..." sometimes, and sometimes not. This happens whenever I try to get the value from one of more of these text boxes on frmOEOrder.

My main form is frmOEOrder and it has multiple tabs. The first tab, pgeGeneral, reveals one of two subpages, either subMisc or subDetail

O

n frmOEOrder
!txtTotal is bound to
=cswNullToZero([txtSubtotal])+cswRoundAmount2([txtTax])+cswNullToZero([txtFreight])+cswNullToZero([txtMHRLabor])
 
!txtTax is bound to
=cswRoundAmount2(cswNullToZero([subDetail].[Form]![zstxtSalesTax])+cswNullToZero([subMisc].[Form]![zstxtSalesTax])+NZ([zstxtTaxFreight])+NZ([zstxtTaxMHR]))
 
!txtSubtotal is bound to
=cswNullToZero([Forms]![frmOEOrder].[subDetail].[Form]![zstxtTotal])+cswNullToZero([Forms]![frmOEOrder].[subMisc].[Form]![zstxtTotal])
 
zstxtTaxFreight is bound to 
=Nz([curFreight]*[dblExchangeRate])*NZ([dblTaxFreightPercent]/100)
 
zstxtTaxMHR is bound to
=Sum(cswNullToOne([dblQtyOrdered])*[dblManHoursRequired])
 
txtMHRLabor is bound to
=(cswRoundAmount2(cswNullToZero([subDetail].[Form]![zstxtMHRTotal])*cswNullToZero([Forms]![frmOEOrder]![txtHourly]))+cswRoundAmount2(cswNullToZero([subMisc].[Form]![zstxtMHRTotal])*cswNullToZero([Forms]![frmOEOrder]![txtHourly])))*[dblExchangeRate]
 
zstxtSalesTax is bound to
=Sum([curSalesTax])

The bold data items are from the underlying dataset that the subform or form is bound to.

I notice when I am debugging, as I stop at various places, sometimes #Error# is displayed in these texboxes that are bound to a formula.

The specific thing that gives me a problem is when the user clicks on another tab, the pgeDetail tab. In the tab_Change event I want to get the value from frmOEOrder!txtTotal, but I cannot access the value at that time, I get the Application-Defined or object-defined error.

What is determining whether I can access these text boxes or not? Must the subform be in focus or something like that? Do I have to capture this txtTotal value before the tab Change event?

Thanks,
Jim

[Stewart Ross]

Hi Jim. This looks more like one of two problems to me: null values in computations, as null values stop computations in their tracks, and Nz (non-null) functions which are returning empty strings instead of 0's.

Nz(someval) will return an empty string, not a zero, leading to errors in any computation in which it is embedded. For example, if A, B and C are null then all the query engine will see as a result of Nz(A) + Nz (B) + Nz (C) is something like

computed value: ++

instead of

computed value: 0+0+0

If you are going to use Nz for numeric values you will have to qualify it appropriately:

Nz(someval, 0) for values not used as divisors, or
Nz(someval, 1) for values used as divisors (to avoid division by zero errors).

In addition, the emboldened expressions you post ALL have field values unprotected against nulls (even though there is an Nz in one of them!). These are modified to protect those fields (and to make Nz return 0) below.

zstxtTaxFreight is bound to 
=Nz([curFreight], 0)*Nz([dblExchangeRate], 0)*NZ([dblTaxFreightPercent], 0)/100
 
zstxtTaxMHR is bound to
=Sum(cswNullToOne([dblQtyOrdered])*Nz([dblManHoursRequired], 0))
 
txtMHRLabor is bound to
=(cswRoundAmount2(cswNullToZero([subDetail].[Form]![zstxtMHRTotal])*cswNullToZero([Forms]![frmOEOrder]![txtHourly]))+cswRoundAmount2(cswNullToZero([subMisc].[Form]![zstxtMHRTotal])*cswNullToZero([Forms]![frmOEOrder]![txtHourly])))*Nz([dblExchangeRate],0)
 
zstxtSalesTax is bound to
=Sum(Nz([curSalesTax], 0))

-Stewart

[jimatqsi]

Stewart,
The question is why are the values sometimes null and sometimes not. Or (I think) why is the recordset empty (giving nulls) sometimes and sometimes not.

If I click on one tab the values in these text boxes appear correctly and can be accessed without problem. When I click on a different tab that becomes momentarily not the case (I see "#ERROR#" in the text boxes) and code lines referencing the values in the text boxes fail. But alternate clicks can give varying results.

I solved the problem by changing my approach. Instead of trying to grab the values from the screen I read through the order detail lines and compute the total of the detail, which should already be available on the screen.

But I still wonder why this happens. Maybe it's a warning against binding text boxes to computations based on the underlying recordset.

Jim