Suggestions:
1. Check that the subform text box is in the Form Footer section, not the
Page Footer section. It does make a difference.
2. Try changing the Control Source of the main form text box to:
=[SFrmInvoiceDetails].[Form]![TotalSalesAmount]
Probably won't make a difference, but is more explicit.
3. The Nz() is useful for converting Nulls, but won't solve the #Error.
4. If the subform is set up such that no new records can be entered (e.g. if
its AllowAdditions property is set to No, or it is based on a read-only
query), the subform area will display completely blank. In this case,
attempting to read the non-existent text box in the subform yields "#Error".
You can fix this by making changes so the subform can accept new records.
Alternatively, you can use an Immediate If expression to test for that case:
=IIf([SFrmInvoiceDetails].[Form].RecordsetClone.RecordCount > 0,
Nz([SFrmInvoiceDetails].[Form]![TotalSalesAmount],0), 0)
5. If that does not solve the issue, check for any other calculated controls
that could be interferring. Once one control cannot be calculated, Access
can give up on trying the others.
6. Make sure you have the latest service pack for your version of Access
from support.microsoft.com. We have seen cases with runtime Access where a
machine that had both the runtime and full version of Access intermittently
showed #Error until it was brought up to the latest service pack.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Charlene" <cp***@earthlink.net> wrote in message
news:pk*****************@newsread1.news.atl.earthl ink.net...
I have an Invoice/Invoice Details form/subform. In the page footer of the
Invoice Details subform I have a textbox with the control source set to
=Sum([SalesAmount]). I then pull this value into the main form with a
textbox whose control source is =[SFrmInvoiceDetails]![TotalSalesAmount]. This
works fine when there are one or more line items in Invoice Details but on a new
invoice before any invoice details are entered, the textbox on the main
form shows #Error. I would like it to show $0.00. I have tried the NZ function
in different places without success; I keep getting #Error no matter what I
try when there are no records in the subform. What is the correct way to set
this up so that the main form shows $0.00 when there are no records in the subform
and then the total of the line items after one or more line items are entered?
Thanks for all help!
Charlene