Help with Calculated Control

[RunTime91]

Greetings All...

I am struggling with setting up a Main Form (not a sub-form) TextBox control (TxtSysAvg) to calculate the average of data entered into 3 other TextBoxes.
The 3 textboxes that are being averaged are not tabled, and I would rather not have to create a table for them... The Average Measurement will be tabled.

Needless to say, I've tried several iterations of the code below - And I get nuthin but errors.

Private Sub TxtSys3_AfterUpdate()
Me.TxtSysAvg = Avg(Me.TxtSys1 + Me.TxtSys2 + Me.TxtSys3)
End Sub

Any help, as always, is greatly appreciated.



RT91

[CJ_London]

You cannot use avg in that way

you need to add the 3 values and divide by 3. You will get errors until all three controls have valid values unless you use the nz function around each control name

[orange]

I agree with CJ.

You might consider code along these lines.
eg. I used Form23 to test.

Private Sub Form23_AfterUpdate()
    Call CalculateAverage
End Sub

Private Sub TAVG_Click()
'event to force the calculation and display of the Average in TAvg
 Call CalculateAverage
End Sub

Private Sub t1_AfterUpdate()
'process avg after each textbox is valued
    Call CalculateAverage
End Sub

Private Sub t2_AfterUpdate()
'process avg after each textbox is valued
    Call CalculateAverage
End Sub
 
Private Sub t3_AfterUpdate()
'process avg after each textbox is valued
    Call CalculateAverage
End Sub

Private Sub CalculateAverage()
   ' Identify the variables
    Dim val1 As Double
    Dim val2 As Double
    Dim val3 As Double
    Dim avg As Double

    ' Check if the textboxes have valid numeric values; otherwise set to 0
    If IsNumeric(Me.T1) Then val1 = CDbl(Me.T1) Else val1 = 0
    If IsNumeric(Me.T2) Then val2 = CDbl(Me.T2) Else val2 = 0
    If IsNumeric(Me.T3) Then val3 = CDbl(Me.T3) Else val3 = 0

    ' Calculate the average of the values in the textboxes
    avg = (val1 + val2 + val3) / 3

    ' Set the value of the TAvg textbox
    Me.TAVG = avg
End Sub

[June7]

Simple expression in textbox will serve. Until all 3 have data, the result will show Null, shouldn't show error. =(TxtSys1 + TxtSys2 + TxtSys3)/3

Saving calculated value to table is usually not necessary and can be dangerous. Could use Calculated type field in table.