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
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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(FormHasData([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 (tblOrderDetails.qty -- long integer); txtDetailsPrice (tblOrderDetails.Price -- Currency); txtDiscount (tblorderDetails.DiscountAmnt -- long integer)
Attempted: =IIF(FormHasData([Form]),Sum(CalcExtension(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 sfrmOrderDetails 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???