By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,333 Members | 1,962 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,333 IT Pros & Developers. It's quick & easy.

#Error on SubForm field totalling.

P: 45
Hi all,

I'm stumped and it hurts to have to give up and ask for help, but even the omniscient Google limited by my mere biology can't solve this, and I'm still learning Access so here goes.

I have a main form containing a subform, which lists products within an order, in the footer of which is a text box called sfrmTotal whose control source contains the expression:
=Sum(Nz([SubTotal],0))

Back on the main form I have a text box with control source:
=[sfrmcOrderDetails].[Form]![sfrmTotal]

The subform also contains fields such as Discount, Tax, Quantity, etc. I have tried the above Sum() expression on all of the fields and every single one of them returns #Error on both the subform control and the main form one.

[Total] and [SubTotal] are calculated query fields, but [Quantity] and [Discount] are not. Should that even make a difference?

I have tried a function called nnz (not numeric zero), which makes no difference.

I was tweaking queries, checking names and messing with things for the best part of yesterday. I've learned a lot on the way, but I'm no closer to figuring out what is causing the #Error on ALL of the fields.

My last resort is to appeal to the Gurus of Bytes.com for divine assistance or at least guide me in the right direction of where I should be looking for a solution to banish the #Errors from my project.


Thanks in advance.

Jay
Feb 11 '12 #1

✓ answered by ADezii

Puzzling situation, JayF. Set the Control Source of the sfrmTotal Text Box in the Sub-Form Footer to:
Expand|Select|Wrap|Line Numbers
  1. =DSum("[Quantity]","eqryPODetailsExtended","[Purchase ID] = " & [Forms]![dfrmPODetails]![sfrmcOrderDetails].[Form]![Purchase ID])

Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,638
I think the problem is that you are trying to Summarize a Calculated Field. You would need to include the entire Expression within the Sum() Function, something similar to:
Expand|Select|Wrap|Line Numbers
  1. =Sum(Nz(([Quantity] * [Price])-(1- [Discount]),0))
Feb 11 '12 #2

P: 45
Thanks for your speedy reply ADezii, but the expression won't even work when trying to sum [Quantity], [Unit Cost] or similar non-calculated fields, e.g.:

=Sum(Nz([Quantity],0))
Feb 11 '12 #3

P: 45
In case it is pertinent, here is the SQL for the SubForm's underlying query:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblPODetails.*, tblPO.[Order Date], tblPO.[Paid Date], tblPO.[Supplier ID], eqryContactsExtended.Company, eqryContactsExtended.[Contact Name], (([Quantity]*[Unit Cost])-(([Quantity]*[Unit Cost])/100)*[Discount]) AS SubTotal, (([SubTotal]/100)*Nz([Tax Rate],0)) AS Tax, [SubTotal]+[Tax] AS Total
  2. FROM (tblPODetails LEFT JOIN tblPO ON tblPODetails.[Purchase ID] = tblPO.[Purchase ID]) LEFT JOIN eqryContactsExtended ON tblPO.[Supplier ID] = eqryContactsExtended.[Contact ID];

And the main forms's underlying query:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblPO.*, tblPaymentMethods.[Payment Method], eqryContactsExtended.[Contact Name], eqryContactsExtended.Company, eqryContactsExtended.[E-mail Address], eqryContactsExtended.CIF, eqryContactsExtended.[Address 1], eqryContactsExtended.[Address 2], eqryContactsExtended.City, eqryContactsExtended.County, eqryContactsExtended.[Country/Region], eqryContactsExtended.[Post Code], tblPOStatus.Status
  2. FROM ((tblPO LEFT JOIN eqryContactsExtended ON tblPO.[Supplier ID] = eqryContactsExtended.[Contact ID]) LEFT JOIN tblPaymentMethods ON tblPO.[Payment Method ID] = tblPaymentMethods.[Payment Method ID]) LEFT JOIN tblPOStatus ON tblPO.[Purchase Status ID] = tblPOStatus.[Status ID];
Feb 11 '12 #4

ADezii
Expert 5K+
P: 8,638
Is it possible for you to Attach a 'Sanitized' Version of your DB stripped of any Personal Info?
Feb 11 '12 #5

P: 45
Sure, I appreciate your taking the time to look at it.

The passwords for both FE and BE are asdf and you'll need to hold shift for the FE. It's full of dummy data, nothing sensitive.

The form I'm trying to get this working on is dfrmPODetails. As of preparing this database to upload, this form seems to be having a problem with the Keep1Open function in mdKeep1Open (I've not been anywhere near it). Could corruption be causing this behaviour and the #Error? I had the old GoSub problem a few versions back and had to /decompile it.

Again, thank you very much for your help on this. You and the other guys on this site are amazing.
Feb 11 '12 #6

P: 45
Sure enough, decompiling the version I've just sent you fixed the Keep1Open problem, but not the #Error.
Feb 11 '12 #7

ADezii
Expert 5K+
P: 8,638
@JayF:
We'll look at the DB as see what we can do.

BTW, what Version of Access are you using?
Feb 11 '12 #8

P: 45
Thanks. 2007.

I take it you managed to download it before I took it down? It's fine if you did, it just has my email addresses in the Dummy Data. I didn't want to leave it there forever as a spam precaution. I'll upload a new copy soon.

I'm in the process of rebuilding the queries and having a play with some other things, tweaking yet more things as I go. Nothing I do seems to change that #Error though.
Feb 11 '12 #9

ADezii
Expert 5K+
P: 8,638
@JayF:
I never saw the Attachment.
Feb 11 '12 #10

P: 45
Check back in 5 minutes.
Feb 11 '12 #11

P: 45
Ok. Uploaded again with some things changed.

The forms I'm banging my head against are dfrmOrderDetails and the confusingly named xsfrmOrderDetails (still playing with things), and dfrmPODetails and sfrmPODetails.

Password to the backend is asdf. Hold shift to open the front end menus.

Thanks again.
Feb 11 '12 #12

ADezii
Expert 5K+
P: 8,638
Puzzling situation, JayF. Set the Control Source of the sfrmTotal Text Box in the Sub-Form Footer to:
Expand|Select|Wrap|Line Numbers
  1. =DSum("[Quantity]","eqryPODetailsExtended","[Purchase ID] = " & [Forms]![dfrmPODetails]![sfrmcOrderDetails].[Form]![Purchase ID])
Feb 12 '12 #13

P: 45
I'm glad it is not just me that it was puzzling.

With this and the problem it was having with the Keep1Open function (which a /decompile fixed) I figured it could be corruption, so I moved everything over to a new shell, bit by bit, dropping some redundant objects. It still didn't work, at which point I decided to rebuild the subforms using the form wizard.

This solved the problem. Wizards work in mysterious ways.

The expression you conjured up also did the trick on the clean version, and gave me a new tool in my slowly growing box of tools. So thank you for that.

This is my first attempt at a project so any other feedback points you might have spotted while you were digging around would be more than welcome.

Jay
Feb 12 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.