#Error on SubForm field totalling.

[JayF]

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

[ADezii]

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:

=Sum(Nz(([Quantity] * [Price])-(1- [Discount]),0))

[JayF]

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))

[JayF]

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

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
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:

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
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];

[ADezii]

Is it possible for you to Attach a 'Sanitized' Version of your DB stripped of any Personal Info?

[JayF]

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.

[JayF]

Sure enough, decompiling the version I've just sent you fixed the Keep1Open problem, but not the #Error.

[ADezii]

@JayF:
We'll look at the DB as see what we can do.

BTW, what Version of Access are you using?

[JayF]

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.

[ADezii]

@JayF:
I never saw the Attachment.

[JayF]

Check back in 5 minutes.

[JayF]

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.

[ADezii]

Puzzling situation, JayF. Set the Control Source of the sfrmTotal Text Box in the Sub-Form Footer to:

=DSum("[Quantity]","eqryPODetailsExtended","[Purchase ID] = " & [Forms]![dfrmPODetails]![sfrmcOrderDetails].[Form]![Purchase ID])

[JayF]

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