Sum Multiple records and display in text box #ERROR

[masons]

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]

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]

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]

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]

they Are unbound textboxs and in footer

 

[pbaldy]

Can you attach the db here?

 

[arnelgp]

you need to move through the recordset
to do the calculation.
do it on the Form's Current Event:

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

 

[masons]

Can you attach the db here?

sure. thank u for helping

 

[CJ_London]

suspect you need brackets in your calculation

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

witout the brackets, you are probably getting an overflow

 

[masons]

you need to move through the recordset
to do the calculation.
do it on the Form's Current Event:

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]

sorry, need to add Then:

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

 

[masons]

sorry, need to add Then:

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

i get error again
can u check my db? i shared it

 

[arnelgp]

ok, create a .zip archive and upload it here.

 

[masons]

ok, create a .zip archive and upload it here.

thanks

 

[arnelgp]

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

 

[masons]

you are amazing

 

[masons]

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]

zip and upload a sample db.

 

[masons]

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]

i don't have the the code that you added.
i don't even know how you did it.