How I do i programatically force a record to update? (1 Viewer)

Mark E

New member
Local time
Today, 16:56
Joined
Apr 21, 2021
Messages
2
I have a main form with a subform. The main form also totals up data from the subtable in real time as data is entered using the DSUM method..

How do I force the record to update while still in the record so that the numeric fields just entered will be included in the totals? As it stands now, the updated totals do not include the current record while I am still in it.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:56
Joined
May 21, 2018
Messages
8,463
if the total are a calculated field then you can use the recalc method.
Me.recalc
Try adding that in the afterupdate event of the control.
If it is not a calculated control then you may have to do a refresh. This gets a little confusing because there are several things that can cause an update. Each differs in how intrusive
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:56
Joined
Oct 29, 2018
Messages
21,358
To force a record update, I typically use:

If Me.Dirty Then Me.Dirty=False
 

vhung

Member
Local time
Today, 13:56
Joined
Jul 8, 2020
Messages
235
How do I force the record to update while still in the record so that the numeric fields just entered will be included in the totals? As it stands now, the updated totals do not include the current record while I am still in it.

EventProcedure:
AfterUpdate=
DoCmd.RunCommand acCmdSaveRecord
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:56
Joined
Feb 28, 2001
Messages
27,001
This question is two-fold:

A - where is the total that you want to be updated?
B - where are the values that are the cause for needing an update?

If A is the MAIN form and B is the sub-form, then the update events are occurring in the sub-form but if no events are occurring in the main form it will not update. Not a problem, because the sub-form's AfterUpdate event could be use to trigger a direct update.

Suppose that the total you want on the main form is called MainTot and the field in the sub-form is SubFormFld and is in TheTable. (Just making up names.)

In the simplest case (no criteria), you could have an event in the sub-form...

Code:
Private Sub MySubForm_AfterUpdate()

Me.Parent.MainTot = DSum( SubFormFld, TheTable )

Exit Sub

Note that in this context, MainTot should be an unbound field unless you had to actually store it - which normally for this kind of situation is not very highly recommended.
 

Jbooker

New member
Local time
Today, 16:56
Joined
May 12, 2021
Messages
25
With
Code:
DoCmd.RunCommand acCmdSaveRecord
Access will save the form that has focus while using
Code:
 Me.Dirty = False
you're explicitly saving the form represented by the Me object.

For that reason Dirty = False can be preferred. On the other hand, the error message leaves a lot to be desired in the case of save failure. Something like "failed to set the property" vs "save record failed" so wrapping in a global public function that takes the Form as an argument and handles with custom error message is good practice.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:56
Joined
Feb 19, 2002
Messages
42,976
Hey there DBG, any reason why do it this way over the others?
Some years ago an MVP whose name is lost to history discovered that if he was running code in one form against objects in a different form, that other form might end up with the focus and therefore the standard save command - DoCme.RunCommand acCmdSaveRecord was acting on the wrong form. He then came up with the Me.Dirty = False hack.

I call it a hack because it isn't obvious what the command is doing and if you choose to use it, I suggest a comment. When I first encountered it, I thought it was another way to undo an update rather than force it to be committed.

I can't tell you exactly what the issue was only that I personally have never encountered it and so have never experienced a problem with the standard Access save method. But then I do access objects in other databases but I don't run code there so that may be why I've never encountered the issue.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:56
Joined
Sep 12, 2006
Messages
15,614
Some years ago an MVP whose name is lost to history discovered that if he was running code in one form against objects in a different form, that other form might end up with the focus and therefore the standard save command - DoCme.RunCommand acCmdSaveRecord was acting on the wrong form. He then came up with the Me.Dirty = False hack.

I call it a hack because it isn't obvious what the command is doing and if you choose to use it, I suggest a comment. When I first encountered it, I thought it was another way to undo an update rather than force it to be committed.

I can't tell you exactly what the issue was only that I personally have never encountered it and so have never experienced a problem with the standard Access save method. But then I do access objects in other databases but I don't run code there so that may be why I've never encountered the issue.

Interesting explanation, Pat. I had no idea there was a difference. I thought it was just two different ways of achieving the same thing. A formal way, and another way to achieve the same result by directly updating the "dirty" property. I must say I found the idea of me.dirty =false to be a little suspect, and generally use the docmd method.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:56
Joined
Feb 19, 2002
Messages
42,976
I thought it was just two different ways of achieving the same thing.
Some people use Me.Requery and Me.Refresh which I do not recommend at all since they are intended for other purposes but as a side effect, must save the current record first.

In my mind (such as it is these days), it is always best to use the "obvious" command. The one that Microsoft intended you to use rather than a command that as a side effect also does what you want.

Thanks Minty for the step down memory lane:)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 16:56
Joined
May 21, 2018
Messages
8,463
I will say it a little different. You want to specifically specify the thing that you want to control (as AB stated), you do not want to leave it to where the focus might be. The Docmd functions rely heavily on where the current focus is. Not just for the save command, but most.

I often see this when people write
docmd.close
instead of
docmd.close acform, "formname"

In the first case an unexpected object closes because the first case closes the thing with the focus. The second case closes a specific object.

Active anything like activecontrol can also be problematic. If you know the specific control or other object then specify implicitly do not leave it to chance that it will have the focus.
Me.dirty = false is specific and does not rely on something having the focus.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:56
Joined
Feb 19, 2002
Messages
42,976
context-dependent.
I don't disagree with the comments on the DoCmd. However, how often do you really need to manipulate data in a second form while you're in the middle of an update of the first form?

While I don't object to using Me.Dirty = False (if it's commented to avoid confusion), I do object to using Me.Requery and Me.Refresh to save a record.
 

Jbooker

New member
Local time
Today, 16:56
Joined
May 12, 2021
Messages
25
Its true the need for dirty = false is rare.
I use it within a db that’s used as a library db. In that case you have to wrap all the docmd methods in public functions within CodeDb in order to call them from the CurrentDb.

can we all agree that both options are better than?

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Who remembers that gem?
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:56
Joined
Feb 19, 2002
Messages
42,976
There are worse examples. Really old databases have all the variables as numbers :sick:
 

Users who are viewing this thread

Top Bottom