Help : SUM or DSUM? (1 Viewer)

NRGZ

New member
Local time
Today, 14:41
Joined
Aug 9, 2021
Messages
15
I got a basic invoice form that has a 'line total' (in a subform but i'm keep the invoice total in the subform), I want it to total all the 'line totals'
I have 'invoice total' in a table to record invoice total but i cant seem to get anything to work, it either errors or just doesnt do anything at all.

Code:
Private Sub Product_Code__AfterUpdate()

Dim ltotal As Double

Me.Product_Description_ = Me.Product_Code_.Column(1)
Me.Sales_Price_ = Me.Product_Code_.Column(2)
Me.VAT_Code = Me.Product_Code_.Column(3)
Me.VAT_Percentage_ = Me.Product_Code_.Column(4)
Me.Obsolete_ = Me.Product_Code_.Column(6)

Me.Line_Total = Me.Sales_Price_ * Me.Qty

ltotal = DSum("[Line_Total]", "Orderline")


' Me.Invoice_Total = DSum("Line_Total", "Ordeline")


End Sub

Many Thanks.
 

bob fitz

AWF VIP
Local time
Today, 14:41
Joined
May 23, 2011
Messages
4,717
Try:
= Sum(Sales_Price_ * Qty)
as the Control Source setting of a textbox in the Footer section of the sub form
 

NRGZ

New member
Local time
Today, 14:41
Joined
Aug 9, 2021
Messages
15
Try:
= Sum(Sales_Price_ * Qty)
as the Control Source setting of a textbox in the Footer section of the sub form
Nope #Error, infact even if i put =sum([Sales_Price_]*[Qty]) it still errors hence why i was looking at VBA route....
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:41
Joined
Jan 23, 2006
Messages
15,364
Can you post a copy of your database (zip format)?

Have you reviewed the Similar Threads at the bottom of this page?
 

NRGZ

New member
Local time
Today, 14:41
Joined
Aug 9, 2021
Messages
15
Can you post a copy of your database (zip format)?

Have you reviewed the Similar Threads at the bottom of this page?
Copy of database, No due to private company information held within database.
Similar thread, I ALWAYS trawl the net with various searches and also look within forums as much as i can before posting for help.
 

bob fitz

AWF VIP
Local time
Today, 14:41
Joined
May 23, 2011
Messages
4,717
Copy of database, No due to private company information held within database.
Similar thread, I ALWAYS trawl the net with various searches and also look within forums as much as i can before posting for help.
Could you not make a copy, remove all sensitive data and then post?
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:41
Joined
Jan 23, 2006
Messages
15,364
You may get some help from this video .

Or use some fake names in a copy of your database for posting.
Names like Porky Pig, Ima Hogg, Iminda Kitchen.... Big City, Centerville, .....
 

Gasman

Enthusiastic Amateur
Local time
Today, 14:41
Joined
Sep 21, 2011
Messages
14,044
There is a link in my signature for cleaning data for upload?
 

Minty

AWF VIP
Local time
Today, 14:41
Joined
Jul 26, 2013
Messages
10,354
Those field or control names with trailing underscores are really confusing/awkward to read.
Are they really necessary? When a control gets code created it adds an underscore so you're going to get a double one which is almost impossible to spot if you try and reference it somewhere.

Have you got Option Explicit at the top of all your code blocks?

Have you tried renaming your controls (not fields) to something like txtSalesPrice and txtQty and then using those names to manoeuvre things around in VBA, or setting the form footer control source as suggested by @bob fitz ?
 

NRGZ

New member
Local time
Today, 14:41
Joined
Aug 9, 2021
Messages
15
Think i've managed to work out what the problem was,
'Click the Data tab. In the Control Source property box, type the field name or expression for which you want to create the running sum.
For example, type ExtendedPrice for the ExtendedPrice field or, at the group level, type the expression =Sum([ExtendedPrice]).'

I'll see how i get on but i think this is it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 10:41
Joined
Feb 19, 2002
Messages
42,970
Do all your column names end in an underscore? How strange. It makes the names look incomplete but that is neither here nor there. Bob's suggestion should have worked which leads us to look deeper.
1. In what section are you using the Sum() Sum() implies you are summing the values from multiple rows so I have only seen them used in group or form footers
2. Is Sales_Price_ the name of a control or a field of the RecordSource? You can only reference controls in the current section. If you want to Sum() a control, you do it by repeating the internal calculation so you might need to change to Sum(UnitPrice * Discount) or whatever calculation Sales_-r9ce_ does.
 

Minty

AWF VIP
Local time
Today, 14:41
Joined
Jul 26, 2013
Messages
10,354
Think i've managed to work out what the problem was,
'Click the Data tab. In the Control Source property box, type the field name or expression for which you want to create the running sum.
For example, type ExtendedPrice for the ExtendedPrice field or, at the group level, type the expression =Sum([ExtendedPrice]).'

I'll see how i get on but i think this is it.

It would have been prudent to mention this was a report.
You mentioned subForms which led us to think this was in a form and not a report, and they behave differently.
 

Users who are viewing this thread

Top Bottom