If you have a form with subform, last one has no records, and you have a textbox on a main form which calculates total – then you can get #Error as a result of your calculation.
You can simply avoid this using IsError() function, just make the expression like this:
=IIf(IsError([frmMySubform].[Form]![txtTotal]),0, [frmMySubform].[Form]![txtTotal])
Interesting tip. I've seen this before, Greg
ReplyDeleteThis is a helpful one Alex. Thanks.
ReplyDeleteI've been using the following functions since A2 days
ReplyDeleteJim Dettman
Function AvoidError(n As Variant, varReplaceWith As Variant)
On Error GoTo AvoidError_Error
AvoidError = Nz(n, varReplaceWith)
AvoidError_Exit:
Exit Function
AvoidError_Error:
AvoidError = varReplaceWith
Resume AvoidError_Exit
End Function
So the control source of the subform textbox would look like this if, for example, you wanted to sum one of the subform field values:
ReplyDelete=sum(AvoidError([amount], "Replacement Value"))