Full Version: #error In Control With A Subform Control Source
UtterAccess Forums > Microsoft® Access > Access Forms
alsoto
Hello,
I have a form which is basically a Student ledger. A subform shows payments made. At the bottom of the form, I have some controls that summarize the student's account, including a control called PaymentsMade. The control source is = [Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount].
It works perfectly when a student has at least one payment, but I keep getting #Error whenever a student has not made any payments. Entering a $0 payment for the student solves the problem, but obviously, that is a poor option.
I have tried:
Nz([Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount],0) as well as
IIF([Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount] Is Null, "0", [Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount]) but neither solved the problem.
Any suggestions would be greatly appreciated.
Thanks in advance,
Al
polant
Hi
Try the IsNumeric inspection function and see if it solves the problem.
IIf (IsNumeric([Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount]), [Forms]![frmStudentsPaymentLedger2]![subfrmPayments]![SumPaymentAmount] , 0)
alsoto
Thank you for responding. When I tried IsNumeric, I got #Name? instead of #Error.
l
polant
Hi there
guess that SumPaymentAmount is a textbox on the subform that sums the payment field.
Usually the IsNumeric function helps in transferring a total value from a subform to the main form. Have you checked that you are referencing SumPaymentAmount correctly?
alsoto
Yes. It definitely calculates perfectly when there are payments made. Problem only occurs when there are no payments.
even entered a $0 payment and that fixed the problem, but it's a lousy solution.
Al
polant
Is it possible to upload a copy of your db (with test data only)?
polant
Check out the attached example I put together.
think that its harder to transfer a total value from a subform to the main form when the subform's allow additions property is set to No. When there are no records in the subform, we get the #Error even with the use of Nz.
The only way I found around this is by the use of IsNumeric. In the example, the allow additions property of the subform is set to No. Check out student no. 2. There are no payments, yet the total on the main form displays correctly as 0.00.
Hope this helps.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.