Refresh Form value based on update Query

[tucker61]

I have a vba update query that sets the Claim Value - which works.

DoCmd.RunSQL "UPDATE QryqcCharges SET Claim_Value = " & ClaimValue & " WHERE Job_ID=" & Nz(Forms!FrmCharges.Job_ID, 0) & " AND [Quote/Charge]=" & CurformType & ";"

the value on my form does not refresh until i go out of the form and come back in . Even though i have the requery code in the after update field of a textbox.

How do i make sure this total is updated when the values are updated ?

Private Sub tbQty_AfterUpdate()
    ' Update Claim Value
    UpdateClaimValue 1
    ' Requery total and update claim value
    Forms!FrmCharges!tbTotalCharge.Requery
End Sub

 

[arnelgp]

is your form a single Form?
try:

Private Sub tbQty_AfterUpdate()
   Dim jobID As Long
   jobID = Me.Job_ID
    ' Update Claim Value
    UpdateClaimValue 1
    ' Requery total and update claim value
    With Forms!FrmCharges
           .Requery
           .Recordset.FindFirst "Job_ID = " & jobID
     End With
End Sub

or you can try:

Forms!FrmCharges.Recordset.Requery

 

[Gasman]

I would always go with the latter, since I found out about it.

 

[tucker61]

is your form a single Form?
try:

Private Sub tbQty_AfterUpdate()
   Dim jobID As Long
   jobID = Me.Job_ID
    ' Update Claim Value
    UpdateClaimValue 1
    ' Requery total and update claim value
    With Forms!FrmCharges
           .Requery
           .Recordset.FindFirst "Job_ID = " & jobID
     End With
End Sub

or you can try:

Forms!FrmCharges.Recordset.Requery

Thanks it is a single form, Forms!FrmCharges.Recordset.requery does work - because this is in a Afterupdate event - it is not including the current line in the total, So thinking i need to move it from this event to a different one.

 

[arnelgp]

use AfterUpdate Event of the form.

 

[Pat Hartman]

Are you saying that you have an update query that updates data in a table NOT bound to the form's RecordSource? Sounds like a problem waiting to happen and exemplifies the exact reason that experts recommend that you NEVER save calculated values.

If the update value is supposed to include the value for the current record then the obvious answer is that you MUST save the current record BEFORE you run the update query and then you MUST requery the current form if you want to show the update from the other table. Really bad practice all around but @arnelgp 's suggestion to run the update query in the Form's AfterUpdate event will get the current record's values included in the aggregation. HOWEVER, I'm not sure you will be able to run a requery of the current form in the Form's AfterUpdate event. In earlier versions of Access, this would have put the form into an endless loop. The current version checks the dirty flag so it it shouldn't