INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access Subform SUM() Displays #Error for calculated field on main Access Form

Access Subform SUM() Displays #Error for calculated field on main Access Form

Access Subform SUM() Displays #Error for calculated field on main Access Form

(OP)
I am trying to calculate the Grand Total for my Order Items on my main order form using the =SUM() function. It displays on the subform itself but errors out on the main form. I find it hard to figure out where I went wrong after many days and my Acces skills are beginner level. Solutions suggested in the link bellow have not solve the problem. =SUM([Quantity]*[Price]) or =SUM(NZ([Amount],0))

OrderID is the PK in the order form and orderDetailID is the PK in the Order Details subform.

Any Help would be highly appreciated.
I have attached the microsoft access file.

I am using Access 2013 on Windows 10.enter image description here

RE: Access Subform SUM() Displays #Error for calculated field on main Access Form

I did not download your file to look, but I wonder if it has something to do with the linking between the main form and subform. If it works in the subform by itself but not main form, that tells me it has to do with the linking.

For instance, if you built the form to sum all sales for one client, but the main form pulls one sales record at a time, then the subform would then not be able to pull ALL sales records. In that case it would either error out or give you just the value for that one sale.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

RE: Access Subform SUM() Displays #Error for calculated field on main Access Form

You need to reference the name of the subform control, not the Source Object.

Change the Control Source of Text36 to:

CODE --> ControlSource

=[Order Details].[Form]![totalPrice] 

=Sum([Amount]) will not work in the main form since Amount is not a field in the main form's record source.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: Access Subform SUM() Displays #Error for calculated field on main Access Form

(OP)
Thanks a lot dhookom for your solution, That cleared the confusion on another aggregate which the total bill on that order.
But I was also interested in the Grand Total which is the SUM() of all order or subtotal.
I would highly appreciate a hint on how to go about pulling that Grand Total off.

Regards

RE: Access Subform SUM() Displays #Error for calculated field on main Access Form

If you want the Sum of Amount for a set of records, that set of records must be in the current form's record source in order to use =Sum([Amount]).

You have some options:
1) use DSum()
=DSum("Amount","Your table or query name","Optional Where Condition")
2) use a subform with a record source of a totals query with the sum of Amount
3) use code to create a recordset that you can grab the sum and display it in an unbound text box.
4) write your own function similar to DSum() that you can use as a control source:
=MyFunctionName("maybe some arguments")

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close