Results 1 to 7 of 7
  1. #1
    colbywolford is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Souderton, PA, USA
    Posts
    11

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

    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

    Click image for larger version. 

Name:	dberrors.png 
Views:	24 
Size:	33.7 KB 
ID:	24489
    Attached Thumbnails Attached Thumbnails dberrors.png  
    Last edited by colbywolford; 04-28-2016 at 09:13 AM. Reason: wrong screenshot

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    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.

  3. #3
    colbywolford is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Souderton, PA, USA
    Posts
    11
    Quote Originally Posted by Perceptus View Post
    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

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    I think =Sum(Nz([LineTotalCopy],0) will evaluate nulls to 0. but "" or blank will return nothing. which then breaks the sum calc.



  5. #5
    colbywolford is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Souderton, PA, USA
    Posts
    11
    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.

  6. #6
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by colbywolford View Post
    ...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.

    Quote Originally Posted by colbywolford View Post
    ...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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    colbywolford is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    May 2014
    Location
    Souderton, PA, USA
    Posts
    11
    Quote Originally Posted by Missinglinq View Post
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. #Type! error displaying in report
    By KACJR in forum Programming
    Replies: 1
    Last Post: 10-19-2015, 04:05 PM
  2. Calculated fields showing Type error
    By scoe in forum Forms
    Replies: 2
    Last Post: 10-09-2014, 08:23 AM
  3. Replies: 9
    Last Post: 01-09-2014, 02:58 PM
  4. Replies: 3
    Last Post: 04-03-2013, 11:29 PM
  5. Get average of unbound textboxes.
    By markhook in forum Forms
    Replies: 8
    Last Post: 07-14-2006, 09:30 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums