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

masons

New member
Local time
Tomorrow, 02:37
Joined
Oct 7, 2020
Messages
15
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
Today, 15:07
Joined
Aug 30, 2003
Messages
36,118
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
Tomorrow, 02:37
Joined
Oct 7, 2020
Messages
15
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
Today, 15:07
Joined
Aug 30, 2003
Messages
36,118
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?
 

masons

New member
Local time
Tomorrow, 02:37
Joined
Oct 7, 2020
Messages
15
they Are unbound textboxs and in footer
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:07
Joined
Aug 30, 2003
Messages
36,118
Can you attach the db here?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:07
Joined
May 7, 2009
Messages
19,169
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, 22:07
Joined
Feb 19, 2013
Messages
16,553
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
Tomorrow, 02:37
Joined
Oct 7, 2020
Messages
15
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:07
Joined
May 7, 2009
Messages
19,169
sorry, need to add Then:

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:07
Joined
May 7, 2009
Messages
19,169
ok, create a .zip archive and upload it here.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:07
Joined
May 7, 2009
Messages
19,169
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: 155

masons

New member
Local time
Tomorrow, 02:37
Joined
Oct 7, 2020
Messages
15
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:07
Joined
May 7, 2009
Messages
19,169
zip and upload a sample db.
 

masons

New member
Local time
Tomorrow, 02:37
Joined
Oct 7, 2020
Messages
15
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:07
Joined
May 7, 2009
Messages
19,169
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