Sum function not summing?

[Lol Owen]

Can't believe I'm posting this Have tried every permutation I can find, including the previous requestes from other members, but it still won't work.
I have a subform on whose footer I've placed a text box to sum a valu, [SubTotal]. On the main form I've set the control source of a text box to the text box on the subform, and it just won't work Can someone please have a look at this and put me out of my misery?

Thanks, Lol

 

[giuseppeking]

ehm

you use the SQL function SUM on a field value.. That is the problem..
I can give you an advice.. it's the first solution I have in my mind.. there are probably better and faster ones.. but ..

build a matrix with the BARCODE and SUBTOTAL in two columns

BARCODE SUBTOTAL
--------- ----------
xxxx 123
yyy 124

update the subtotal when you add a new product and calculate via code the sum to put it in the textfield..

Another solution is to use a Listbox as cart and calculate the total iterating on the elements of the listbox..


bye
giuseppe

 

[Lol Owen]

Thanks Giuseppe but I think I'm just missing something simple here. i used the same technique on a previous dbase without a hitch Perhaps someone can point the way? Please!!

Lol

 

[giuseppeking]

yes but

look at the sum in the subform
It doesn't sum anything so it is obvious that you can't see the value in the main form..

SUM is defined for sql statements so..

 

[Lol Owen]

ahh but

I have used this technique before in a dbase and it worked. It is even suggested as a solution in the Forms forums when others have asked the question. I'm really at a loss, having followed the technique verbatim, or so I thought, as to why it doesn't work! C'mon experts, I've lost 3 pounds in weight this week with the stress of this project

Lol

 

[Rich]

=Sum([YourField]) in the subform footer

 

[Lol Owen]

Done it Rich. Put the unbound control in the footer, put it's control source =Sum([SubTotal)]. Put the control source of the text box on the main form to refer to the control on the subform footer and I just keep getting "#Name". I've tried all ways with the permutations to do this and it should work. have a look at the attached database if you would and you'll see what I mean.

Thanks a lot, Lol

 

[Rich]

Unless the db you've posted is 97ver I can't look at, however the fact that you're getting the #Name error almost certainly means that you haven't referenced the subform correctly.
Open the form in design view and use the build button to get the correct syntax

 

[Lol Owen]

Don't know how to save in 97 Rich. Glad to upload it or even e-mail it to you if you can tell me how. Using the same tables I've just recreated the basic form/subform layout without any of the requery stuff that exists in the original. i was still getting the #Error message. Let me know about the 97 conversion.

Cheers, Lol

 

[Rich]

I think it's Save As a previous version

 

[Jings00]

I got your form to work with the following changes...

Control Source Property for Transaction Total on Main Form:
=Forms!Transactions!TranSub!Text8

Control Source Property for Text8 on Subfrom is erroring also, change to:
=Sum([Quantity]*[Sell Price])

 

[Lol Owen]

Hi Rich, did Save As Previous Version. here's the offending article. Jings00 I altered to what you said and it won't work for me! Somethings afoot here!
Ignore the db1 name, it's the same dbase.

Lol

 

[Jings00]

Here is a copy of the working version I did...thought i covered all the changes i made..

 

[Lol Owen]

Thanks Jings00 that works brilliant. Funny thing is that's one of the permutations I tried and it didn't work! I think I know the reason, and it has a lot of fact to back it up. This is one of the files I have opened at college on the network. We have so many odd occurrences with software at college, it being networked, it isn't funny. One day the VB6 would not allow us to do .SetFocus. Absolutely refused to accept it as being possible!

Thanks again for your help, Lol