Mar 17 2022 06:44 AM
Mar 17 2022 06:44 AM
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()
'doesnt refresh the summation though
Mar 17 2022 01:47 PMSolution
Mar 17 2022 04:27 PM
@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.
Mar 17 2022 04:45 PM
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.
Mar 17 2022 06:12 PM
Mar 17 2022 06:48 PM
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.
Mar 17 2022 07:05 PM - edited Mar 18 2022 12:59 AM
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
Mar 18 2022 03:57 AM
Mar 19 2022 01:52 PM
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
Mar 20 2022 05:43 PM