I've used sum functions in footers before and never had a problem. I am entering =sum([txtQty]) in the control source of an unbound txtbox (located on a subform) to calculate the total number of items in an order. Seems simple enough but in this form I get an #Error message every time. Suggestions as to why??
txtQty is a bound control whose data is a number.
May soon think I am losing my mind! I have a more complicated sum that includes calculations but it isn't working eitherand since I can't even get this simple one to work I suspect it to be something the same between the two.
You can't sum a control, only a field. Try using the field name (and sometimes Access gets confused if there's a control with the same name as the field).
And I believe that to use Sum you need to handle any nulls or else it will not work properly. I could be mistaken about that on a form but I know it is true for a report.
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
Code:
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(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.
The sum([qty]) calculation actually works fine if the other calculation is deleted (I read that one miscalculating can cause all of them to so I checked). Which now leaves me at there must be a syntactical error in my =Sum(CalcExtension()) calculation detailed above.
Could you take a look and tell me if you notice any obvious errors in just that calculation?
