Sum Multiple records and display in text box #ERROR (1 Viewer)

masons

New member
Local time
Today, 05:47
Joined
Oct 7, 2020
Messages
14
Hi there
i have access 2016 and new to this forum. thank u for helping me.

i have some fields in this multiple records. and some textboxs. and i have field that Multiply these and show answer in every record of form. its like this =[field1]*[Text1]+[field2]*[Text2]+[field3]*[Text3]. and that field name is like text50
the problem is
i want to sum all of text50 in every records and show them in footer but i get #error
i did these before : sum([text50]} or dsum([text50]} or even sum([field1]*[Text1]+[field2]*[Text2]+[field3]*[Text3])
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:17
Joined
Aug 30, 2003
Messages
34,752
It would be your last effort, summing the calculation. Or moving the calculation out to the form's source query so it becomes a field you can sum.
 

masons

New member
Local time
Today, 05:47
Joined
Oct 7, 2020
Messages
14
How can i calculation out of form?. [text1] and [text2] and [text3] are numbers that people should type in form. they are not stored in tables. so i just can calculate in form.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:17
Joined
Aug 30, 2003
Messages
34,752
They're unbound textboxes? I don't see how they can be if you're displaying multiple records; they would display the same for every record. Can you attach the db here?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:17
Joined
Aug 30, 2003
Messages
34,752
Can you attach the db here?
 

arnelgp

error reading drive A:
Local time
Today, 10:17
Joined
May 7, 2009
Messages
10,883
you need to move through the recordset
to do the calculation.
do it on the Form's Current Event:
Code:
Private Sub Form_Current()
Dim dblSum As Double
With Me.RecordsetClone
    If Not (.BOF And .EOF)
        .MoveFirst
    End If
    Do Until .EOF
        dblSum= dblSum + Nz([field1],0)*Nz([Text1],0)+Nz([field2],0)*Nz([Text2],0)+Nz([field3],0)*Nz([Text3],0)
        .MoveNext
    Loop
    
End With
Me!txtSum = dblSum
End Sub
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:17
Joined
Feb 19, 2013
Messages
12,610
suspect you need brackets in your calculation

sum(([field1]*[Text1])+[(field2]*[Text2])+([field3]*[Text3]))

witout the brackets, you are probably getting an overflow
 

masons

New member
Local time
Today, 05:47
Joined
Oct 7, 2020
Messages
14
you need to move through the recordset
to do the calculation.
do it on the Form's Current Event:
Code:
Private Sub Form_Current()
Dim dblSum As Double
With Me.RecordsetClone
    If Not (.BOF And .EOF)
        .MoveFirst
    End If
    Do Until .EOF
        dblSum= dblSum + Nz([field1],0)*Nz([Text1],0)+Nz([field2],0)*Nz([Text2],0)+Nz([field3],0)*Nz([Text3],0)
        .MoveNext
    Loop
   
End With
Me!txtSum = dblSum
End Sub
can tell me more about this?

and it get syntax error and its about this line: If Not (.BOF And .EOF)
 

arnelgp

error reading drive A:
Local time
Today, 10:17
Joined
May 7, 2009
Messages
10,883
sorry, need to add Then:

If Not (.BOF And .EOF) Then
.MoveFirst
End If
 

arnelgp

error reading drive A:
Local time
Today, 10:17
Joined
May 7, 2009
Messages
10,883
ok, create a .zip archive and upload it here.
 

arnelgp

error reading drive A:
Local time
Today, 10:17
Joined
May 7, 2009
Messages
10,883
ok see the Code on the Form's current event.
you need to modify the formula in Rewards column, coz it is using the SumOfRewards (Text19).

after you modify the formula, go again to the Code and modify the formula
for the SumOfRewards
 

Attachments

  • access forum share.zip
    49.3 KB · Views: 20

masons

New member
Local time
Today, 05:47
Joined
Oct 7, 2020
Messages
14
ok see the Code on the Form's current event.
you need to modify the formula in Rewards column, coz it is using the SumOfRewards (Text19).

after you modify the formula, go again to the Code and modify the formula
for the SumOfRewards
hey thank u but some of points dont work corectly and show wrong numbers,
and the weird thing is when i click on each textboxs in form the form recalculate te numbers and show another number in all textboxs when i just change my focus on text boxes
 

masons

New member
Local time
Today, 05:47
Joined
Oct 7, 2020
Messages
14
ok see the Code on the Form's current event.
you need to modify the formula in Rewards column, coz it is using the SumOfRewards (Text19).

after you modify the formula, go again to the Code and modify the formula
for the SumOfRewards
Download the db u sent here before
 

arnelgp

error reading drive A:
Local time
Today, 10:17
Joined
May 7, 2009
Messages
10,883
i don't have the the code that you added.
i don't even know how you did it.
 

Users who are viewing this thread

Top Bottom