Not refreshing calculated fields

[mafhobb]

Hi.

I have a bound subform that I use to add up the cost of bought items. This subform has a field for number of items, article number, cost and a calculated field which is extended cost. The code in this subform is the following:

Private Sub txtPrice_AfterUpdate()

'Update so it is always current
   On Error GoTo txtPrice_AfterUpdate_Error

    Me.txtExtended.Requery
    Exit Sub
    
txtPrice_AfterUpdate_Error:
    Dim ErrorForm As String
    Dim ErrorControl As String
    Dim ErrorCode As String
    Dim ErrorNumber As String
    ErrorForm = Nz(Screen.ActiveForm.Name, "No Form Loaded")
    ErrorControl = Nz(Screen.ActiveControl.Name, "No Control Loaded")
    ErrorNumber = Err.Number
    ErrorCode = Err.Description
    Call SendError(ErrorCode, ErrorNumber, ErrorControl, ErrorForm)
    Exit Sub

End Sub
Private Sub txtPrice_LostFocus()

'Make sure that the fields have data
   On Error GoTo txtPrice_LostFocus_Error

    If IsNull(Me.txtPrice) Then
        MsgBox "Bitte Stückpreis eingeben"
        Me.txtDescription.SetFocus
        Me.txtPrice.SetFocus
        Exit Sub
    End If
    
    If IsNull(Me.txtDescription) Then
        MsgBox "Bitte Beschreibung eingeben"
        Me.txtPrice.SetFocus
        Me.txtDescription.SetFocus
        Exit Sub
    End If
    
    If IsNull(Me.txtQuantity) Then
        MsgBox "Bitte Menge eingeben"
        Me.txtDescription.SetFocus
        Me.txtQuantity.SetFocus
        Exit Sub
    End If
    Exit Sub
    
txtPrice_LostFocus_Error:
    Dim ErrorForm As String
    Dim ErrorControl As String
    Dim ErrorCode As String
    Dim ErrorNumber As String
    ErrorForm = Nz(Screen.ActiveForm.Name, "No Form Loaded")
    ErrorControl = Nz(Screen.ActiveControl.Name, "No Control Loaded")
    ErrorNumber = Err.Number
    ErrorCode = Err.Description
    Call SendError(ErrorCode, ErrorNumber, ErrorControl, ErrorForm)
    Exit Sub
    
End Sub
Private Sub txtQuantity_AfterUpdate()

'Update so it is always current
   On Error GoTo txtQuantity_AfterUpdate_Error

    Me.txtExtended.Requery
    Exit Sub
    
txtQuantity_AfterUpdate_Error:
    Dim ErrorForm As String
    Dim ErrorControl As String
    Dim ErrorCode As String
    Dim ErrorNumber As String
    ErrorForm = Nz(Screen.ActiveForm.Name, "No Form Loaded")
    ErrorControl = Nz(Screen.ActiveControl.Name, "No Control Loaded")
    ErrorNumber = Err.Number
    ErrorCode = Err.Description
    Call SendError(ErrorCode, ErrorNumber, ErrorControl, ErrorForm)
    Exit Sub
    
End Sub

Then, at the bottom of the subform I have a number of calculated fields such as Subtotal, Tax and Total which have no code at all other than a calculation such as this "=Sum([Price]*[Quantity])" in their control sources.

The calculated field "Extended costs" always updates when the quantities or the costs are updated, but sometimes (and I have found no rime or reason as to when or why) the calculated fields "Subtotal", "Tax" and Total" do not.

When this happens all I have to do is close the form and reopen it and then those calculated fields shows the correct values.

This database's front end is used by a few people and sometimes I have found that the same subform will update the subtotal for one user yet it won't for another and a bit later it will work for both, none or it will for one but not the other.

I feel that I am not correctly refreshing or requering the data.

Can someone point me in the right direction? What am I missing here?

What is the proper way of doing this?

Thanks

mafhobb

 

[pr2-eugin]

If you have set the Control Sources of those UNBOUND text boxes as Sum() then it would auto recalculate, you do not need to worry about re querying. If the Unbound text boxes are on the Main Form, all you need to do is make sure that you are referring to the proper controls.

 

[mafhobb]

The problem with not having the requery statement in the code above is that the Extended Cost field (autocalculated) remains blank after updating the other fields even though the calculation is performed and the Subtotal autocalculated field updates to reflect the changes.

Why?

mafhobb

 

[mafhobb]

I have added a me.refresh to the subform's ufterupdate event and that seems to take care of the issue.

mafhobb