sum across columns??

[uguimess]

I have a subform in datasheet view. When entering a value in any of several "SurveyCount" textboxes, I need to update the sum of all those textboxes in another "CalculatedTotal" textbox within that record. How on earth is this done???

Note: Because users can add custom fields, I don't know at design time how many of the qualifying fields will be included in the sum.

-----------------------------------------
Where would we be if we didn't try?

 

[uguimess]

I tried this and it worked once, but seems to be in an endless loop which lets me navigate from field to field in the same record, but otherwise freezes me out.

Private Sub Form_AfterUpdate()    
    If Screen.ActiveControl.Name <> "txtTtl" Then Call UpdateMyTotal
End Sub

Private Sub UpdateMyTotal()
    Dim iTotal As Integer
    Dim ctl As Control
   
    iTotal = 0
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox And ctl.Tag <> 0 Then
            If Not Trim(ctl.Value & "") & "" = "" And IsNumeric(ctl.Value) Then iTotal = iTotal + ctl.Value
        End If
    Next ctl
    Me.txtTtl = iTotal
End Sub

-----------------------------------------
Where would we be if we didn't try?

 

[Knicks]

Can't you just use on the fields after update event a refernce to whatever the calcuated field is called

After Update:

me.calculatedfieldname.refresh
(whatever the name of calcuated field is)

 

[uguimess]

Thanks Knicks.

Because of a thypo in your reply, I am not sure what you are recommending.

I hear the term "calculated field" at times, but I am not sure precisely what it means; whether a field with some sort of formula associated with it, or just a field that holds the result of some calculation. So where would I perform the desired calculation? I am drawing a blank here.

The Sourceobject for the subform is a query of an underlying temp table. Does the Total field need to be in the temp table too or can it just be in the form?

-----------------------------------------
Where would we be if we didn't try?

 

[uguimess]

found another thread that clued me in to using Form_BeforeUpdate. It works now.

-----------------------------------------
Where would we be if we didn't try?

 

[uguimess]

Ahhhh!

Not totally working. You have to go to a new record to see the updated calculation. How can I get it to update when focus changes to another field within the same record???

-----------------------------------------
Where would we be if we didn't try?

 

[uguimess]

solved. can't remember how.

-----------------------------------------
Where would we be if we didn't try?