Sum field in subform (1 Viewer)

Abidallah

New member
Local time
Today, 10:05
Joined
Jun 13, 2019
Messages
7
Hello
I have six fields in a subform
Text1
Text2
Tex3
Idproduct (number)
Idstore (text)
Qnty (number)
Main form "fautt"
Subform name "autot"

Access2007
Text1 =
Code:
DLookUp("balance","qn7","[idproduct]=" & [Forms]![fautt]![autot].[Form]![idproduct] & " and [idstore]='" & [Forms]![fautt]![autot].[Form]![idstore] & "'")
And text2 =
Code:
 IIf(([text1]<[Qnty]),([text1]-[Qnty]),0)
Everything works well till now
But The bug is
I tried to make text3 as
sum to text2 field values
I tried more than a hundred times but I failed
Any help ?
 

June7

AWF VIP
Local time
Today, 09:05
Joined
Mar 9, 2014
Messages
5,423
Aggregate functions must reference fields, not controls.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:05
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to the forum. Are you able to post a sample db?
 

Abidallah

New member
Local time
Today, 10:05
Joined
Jun 13, 2019
Messages
7
Thank you for quick response
I have attached a sample
 

Attachments

  • SAMPLE SUMFIELD.zip
    72.5 KB · Views: 88

theDBguy

I’m here to help
Staff member
Local time
Today, 10:05
Joined
Oct 29, 2018
Messages
21,358
Thank you for quick response
I have attached a sample
Hi. Thanks. In the sample db you posted, Text1 = 28 and Text2 = -71, what do you want Text3 to show?
 

June7

AWF VIP
Local time
Today, 09:05
Joined
Mar 9, 2014
Messages
5,423
Use DSum() on qn7.

Why does VoucherNo unbound textbox have DefaultValue of 11? Should this be a combobox?
 

Abidallah

New member
Local time
Today, 10:05
Joined
Jun 13, 2019
Messages
7
Why does VoucherNo unbound textbox have DefaultValue of 11? Should this be a combobox?

This is just a sample
And voucherno should be a textbox for The user innput the number he wants
 

June7

AWF VIP
Local time
Today, 09:05
Joined
Mar 9, 2014
Messages
5,423
But why let user enter a voucher number that doesn't exist? A combobox can prevent that.
 

Abidallah

New member
Local time
Today, 10:05
Joined
Jun 13, 2019
Messages
7
I know that but this form for add and edit data
As you see all texts box in main form are unbounded
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:05
Joined
Jan 20, 2009
Messages
12,849
I tried to make text3 as sum to text2 field values

June pointed out the problem in the first reply. Aggregate functions ONLY work on fields in the form's recordset. Controls cannot be summed.
 

Abidallah

New member
Local time
Today, 10:05
Joined
Jun 13, 2019
Messages
7
I know that.
But there is a way to sum this field.
is not silliness that I can not sum field
 

Abidallah

New member
Local time
Today, 10:05
Joined
Jun 13, 2019
Messages
7
The purpose of this process is to know that there are values ​​<> 0 within the text2(subform)
by other way.can i creat command button in main form to know that in any values without specified within text2(sub form) is <>0 by msgbox
 

June7

AWF VIP
Local time
Today, 09:05
Joined
Mar 9, 2014
Messages
5,423
Already suggested using DSum() on qn7.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 04:05
Joined
Jan 20, 2009
Messages
12,849
I know that.
But there is a way to sum this field.
is not silliness that I can not sum field

You are trying to sum a control, NOT a field.

The data you are trying to Sum is on the form which is part of the Project, not part of the Database, so the engine has no access to it.
 

June7

AWF VIP
Local time
Today, 09:05
Joined
Mar 9, 2014
Messages
5,423
Why would you need DLookup? If I understand correct, you want to sum the balance field from query qn7. Although, not sure makes sense to do that with Text3 in detail section. If you want something else then you need to make clear exactly what that is. Unfortunately, seems language barrier getting in the way.
 

Users who are viewing this thread

Top Bottom