Subform with a Few Unbound Calculated Textboxes Is Displaying #Error and #Type!

[colbywolford]

Hello,

I'm a novice Access user piecing together what I can find on the internet and in some reference books to create my own contact/accounting/invoicing database.

That being said, I've created a form with a subform for invoicing. Everything was a struggle once I started trying to do new and better things. However, as you can see in screenshot at the end of this post, two fields in the footer of the subform (Subtotal and Total) are not working as they should. The grayed-out field is simply a data copy field since I found out that you can't perform calculations on a calculated field.

Access Version
2013

Field Information

• Name: Subtotal
  Formula: =Sum([LineTotalCopy])
  The [LineTotalCopy] field is hidden in the screenshot, but it resides in the subform record as another data copy field like [SubtotalCopy].
• Name: SubtotalCopy
  Formula: =[Subtotal]
  As stated before, this is just a data copy field used for calculations.
• Name: TaxRate
  Formula: =[Forms]![Invoice_Table]![TaxRate]
  This field pulls the tax rate associated with the client because there are three tax rates in Pennsylvania depending on the location of the work.
• Name: Total
  Formula: =[Subtotal]*[TaxRate]

For fixing #Error, I've also tried using "=Sum(Nz([LineTotalCopy],0)" (obviously without the quotation marks), but that didn't work either. In doing a Google search, I've noticed a lot people having issues with the Sum function in subforms; unfortunately, the solutions given for those problems either 1) didn't work for me, or 2) weren't written in plain English that I could understand.

I'm mystified by the #Type! error because all of the fields involved in the Total calculation are set to Currency with 2 decimal places and TaxRate is set to Percentage with two decimal places. I don't understand why #Type! would even be an issue/factor.

Thank you, in advance, for any help or time you can give me on this problem. PLEASE, do not point me to the Northwind Database without telling me exactly what to look for. I've tried looking in that file a few times previously, and nothing came of it but confusion and perplexity as I got lost amid all the tables, queries, forms, and so on. I don't even know what I'm looking for--or even where to start looking.



All help/suggestions are greatly appreciated!

Colby

[Perceptus]

I think you need to put a hidden unbound field un the child forms footer. have it handle the calculation formula and be invisible. In the Parent form reference the child forms footer control.

[colbywolford]

I think you need to put a hidden unbound field un the child forms footer. have it handle the calculation formula and be invisible. In the Parent form reference the child forms footer control.

All of the calculations that are receiving errors are in the child form (subform) footer. The only field pulling from the parent form (main form) is the TaxRate field, and that field is the one I'm not having a problem with (not that I can tell anyway). I'm not understanding what you mean about having the parent form reference the child form footer control. Why would I need to do that? The parent form and child form are linked by the parent form's ClientID and the child form's ClientID_FK. Can you explain what you mean a little bit more?

Thank you for the reply,
Colby

[Perceptus]

I think =Sum(Nz([LineTotalCopy],0) will evaluate nulls to 0. but "" or blank will return nothing. which then breaks the sum calc.

[colbywolford]

The only way I can make this work is by bypassing the [LineTotalCopy] field and using the formula =Sum([Qty]*[ServiceRate]) in the [Subtotal] field. I don't like this as it's just duplicating the [LineTotalCopy] formula and doesn't answer why I am getting the #Error and #Type! issues.

[Missinglinq]

...I'm not understanding what you mean about having the parent form reference the child form footer control. Why would I need to do that...

Perceptus was referring to displaying the results of a calculation done on a Subform but needing to be displayed on the Main Form. You'd have an Unbound Textbox on the Main Form and use this Control Source for it:

=SubformName.Form!ControlNameOnSub

where SubformName is just that, the name of the Subform Control (not the original Datasheet Form name, unless they are one and the same) and ControlNameOnSub is the name of the Control on the Datasheet View Form Footer where the calculation was performed.

...The only way I can make this work is by bypassing the [LineTotalCopy] field and using the formula =Sum([Qty]*[ServiceRate]) in the [Subtotal] field. I don't like this as it's just duplicating the [LineTotalCopy] formula...

Like it or not, you cannot run any Aggregate Function against a Calculated Field, you have to run it against the Expression used for the Calculated Field...that's simply one of the rules the Access Gnomes enforce! Basically, it's usually quicker to rerun a calculation than it is to retrieve the data from a Table, which is why Calculated Fields, with a few rare exceptions, should never be stored.

Here's a couple of links that may help you understand the #Name and #Error that are being displayed. In general, both mean that Access cannot, for some reason, evaluate the Control Source of the Control to something that is meaningful.

Resolve #Name Error in a Form/Report

Resolve #Error when the Subform has No Records

Linq ;0)>

[colbywolford]

Like it or not, you cannot run any Aggregate Function against a Calculated Field, you have to run it against the Expression used for the Calculated Field...that's simply one of the rules the Access Gnomes enforce! Basically, it's usually quicker to rerun a calculation than it is to retrieve the data from a Table, which is why Calculated Fields, with a few rare exceptions, should never be stored.

Thanks, Linq, for clarifying Perceptus' answer and for the links that I will check out after my school finals are finished next week. However, there is one thing that I don't understand. Since my [LineTotal] holds the formula and [LineTotalCopy] is equal to the value of [LineTotal], why doesn't =Sum([LineTotalCopy]) work as [LineTotalCopy] is not a calculated field?

Thanks for the help!
Colby