Refresh Sum in form

[Tony2021]

Hello Experts,

I have a form in datasheet. 

There is a sum in one of the columns (I used the sum button and choose "sum" in the combo box in the record below)

When I change a value in the record, the sum doesnt change until I click on another record.  

How can I make the sum change when I update a field on the record? 

I thought the below would work:

Private Sub Form_AfterUpdate()

Me.Refresh
Me.Recalc
'doesnt refresh the summation though

End Sub

[#2 · Accepted solution]

Changes to records are not saved until you move off that record or click Enter (which has the same effect). That behaviour is by design in Access databases.

Your code will not work - the form has not been updated whilst you remain on the record.
Once you leave the record, the update occurs automatically with no need to requery, recalc, refresh or even repaint!

[#3]

@Tony2021 As Colin pointed out, you don't need to do anything except move focus to another record. However, if you have a business need to see the updated sum without moving to a new record, you can force the save by adding:

Me.Dirty = False

to your code before refreshing the form.

[#4]

@George_Hepworth 

Hello, thank you for the response.

I understand that maybe this cant be done.
George, I tried Me.Dirty as instructed but it still did not refresh teh sum.  I also tried to save the record. i also added it to the form's On Dirty Event. 

Let me know if any other suggestions or if I should forget about this.  

[#5]

on the "Column" you are Totalling, add code to it's AfterUpdate event to force save the record.

private sub theColumnName_AfterUpdate()
Me.Dirty = False
end sub

[#6]

@arnel_gp 

Hi Arnel,

That unfortunately didnt seem to work either.  

I tried Me.Dirty = False first on that column I am summing and then I added all the other stuff (refresh, recalc save record...) thinking that might refresh it but that didnt work either. 

The column I am summing is txtCAWCCalc. Its a calculated column. 

Let me know if I am not following. 

[#7]

Using Me.Dirty=False has exactly the same effect as what I stated in post #2.
Doing that will also move the focus to another record (or field) and so the total will be updated
None of the other code you posted is needed

That applies whether or not you are using a calculated field. You MUST move to a different record to update the calculated field total

[#8]

Is it a subform?

[#9]

aside from Me.Dirty = False,
you can also add code to Requery the Control.

Me.Dirty = False
Me!txtCAWCCalc.Requery

[#10]

Yes it is a subform. I added the Me!txtCAWCCalc.Requery to all the events above but didnt refresh.

Unless there is a trick some other crafty trick maybe ISLADOGS post is best advice (it cant be updated). Not sure if I could close the subform and reopen somehow (with screen updating off) but might be more of a headache than its worth. Let me know if any other suggestions. thank you

[#11]

@Tony2021 

To me, closing & reopening the form really seems like using a sledgehammer to crack a nut.

If you REALLY want to do this, you could consider using the Change event as follows:

Private Sub YourFieldName_Change()
  Me.Refresh  
End Sub

Or use Me.Recalc instead of Me.Refresh

In either case the total will update without you leaving the record 

Don't use Me.Requery as that moves the focus to the first record

BUT there is a downside to using the Change event - you can only enter one digit at a time after which the sum updates...then move the cursor, enter/edit another digit...cauing another sum update and so on

You could also use the KeyUp event in the same way:

Private Sub YourFieldName_KeyUp(KeyCode As Integer, Shift As Integer)
    Me.Recalc
End Sub

It has the same issues

In my opinion, both of those will be both tedious and very irritating for end users

But in the end its your choice

[#12]

thanks guys. I think I will forget about this one. I thought it would be more simple. ISLADOGS: The on change event, i agree, would be an irritant since it would update after each digit. thanks again for the tips!