Problems with calculated control in subform footer

[lvmoore]

I have the calculation located in the footer of the subform.
There is sometimes no rows in this form so I have used Allen Browne's IIF(FormHasData ([form]),calculation,0 ) syntax to show a zero if there aren't any rows to calculate on.

Consequently I copied his public function

Public Function FormHasData(frm As Form) As Boolean
'Purpose: Return True if the form has any records (other than new one).
' Return False for unbound forms, and forms with no records.
'Note: Avoids the bug in Access 2007 where text boxes cannot use:
' [Forms].[Form1].[Recordset].[RecordCount]
On Error Resume Next 'To handle unbound forms.
FormHasData = (frm.Recordset.RecordCount <> 0&)
End Function
 
'Notes
 
' 1. Leave the [Form] part of the expression as it is (i.e. do not substitute the name of your form.)
' 2. For Access 97 or earlier, use RecordsetClone instead of Recordset in the function.
' 3. A form with no records still has display problems. The workaround may not display the zero, but it should suppress the #Error.

Other relevant function (for calculating extended price):

CODE

Public Function CalcExtension(ByVal Quantity as integer, ByVal Price As Currency, ByVal DiscountPercent As Integer) As Currency
 
Dim Extension as Currency
Dim Discount as Double
 
Extension = Quantity * Price
Discount = DiscountPercent / 100
CalcExtension = Extension - (Extension * Discount)
 
End Function

Form is bound: tblOrderDetails

Two calculations:

First: summing the quantities
Control Name: txtqty
Control Source: tblOrderDetails .qty (long integer)

Attempted: =IIF(FormHasDat a([form]), sum([qty]),0)
Result: 0 when no records, #error when there ARE records


Second: summing the extended price
Control Name: txtExtension (but it is calculated using a function so I have put in the calculation rather than the control)
Involved Controls: txtqty (tblOrderDetail s.qty -- long integer); txtDetailsPrice (tblOrderDetail s.Price -- Currency); txtDiscount (tblorderDetail s.DiscountAmnt -- long integer)

Attempted: =IIF(FormHasDat a([Form]),Sum(CalcExten sion(nz([txtQty],0),Nz([txtDetailsPrice],0
),nz([txtDiscount],0))),0)
Result: 0 when no records, #error when there ARE records
Note: =CalcExtension( nz([txtQty],0),Nz([txtDetailsPrice],0),nz([txtDiscount],0)) is working well in my txtExtension control

I did try opening the subform outside of the parent/child relationship so it would have records (just chose sfrmOrderDetail s from the Object List) and tried the straight ahead =sum([qty]) and I got the #error message.

I just don't get it. This database has been compiled and compacted and repaired repetitively.

Why oh why???

[lvmoore]

I've discovered (by messing around with this subform outside of the master form) that the =sum([Qty]) works great as long as the other one is absent --> which tells me that the problem is in the other (sum(calcextens ion)) one. Can you please look at that syntax and tell me if you notice anything glaring?

Thanks