Solved Saving a record After update event (1 Viewer)

ypma

Registered User.
Local time
Today, 15:03
Joined
Apr 13, 2012
Messages
643
Hello fellow access users. I am attempting to save a record within script of the after an update event to allow another procedure to take place based on saved new details of the field

Example: On the status changing to Completed, today date is inserted into the Date completed field of the form. This runs correctly, but is not save until I leave the record.

Following this I have an append query to create a new task based on the date completed.

The append query has criteria for a date and if the record has not been saved the date field is blank and the new task is not created.

Any advice would be appreciated
 

Isaac

Lifelong Learner
Local time
Today, 08:03
Joined
Mar 14, 2017
Messages
8,738
Could you use BeforeUpdate instead?
 

ypma

Registered User.
Local time
Today, 15:03
Joined
Apr 13, 2012
Messages
643
Could you use Before Update instead?
Isaac, thank you for responding to my post. I have tried using the Before update event, but still have the problem of not being able to save the record within that event . I can save the record on the lost focus event, however that is too late for the append query to run correctly as I stated in my post the date is required . Hope this makes sense.

Regards Ypma
 

Isaac

Lifelong Learner
Local time
Today, 08:03
Joined
Mar 14, 2017
Messages
8,738
Ok, I think I understand the "flow" now.
How about use the BeforeUpdate event to save the date value in a global variable, then reference that global variable in a function, and reference that function in your Append Query?
There are probably a few ways to do this but that's one.
 

ypma

Registered User.
Local time
Today, 15:03
Joined
Apr 13, 2012
Messages
643
Ok, I think I understand the "flow" now.
How about use the BeforeUpdate event to save the date value in a global variable, then reference that global variable in a function, and reference that function in your Append Query?
There are probably a few ways to do this but that's one.
I like the sound of your suggestion and will work on it. I need my beauty sleep so will attempt your suggestion tomorrow.

Regards Ypma
 

moke123

AWF VIP
Local time
Today, 11:03
Joined
Jan 11, 2013
Messages
3,852
Example: On the status changing to Completed, today date is inserted into the Date completed field of the form. This runs correctly, but is not save until I leave the record.
I'm assuming your either using a checkbox or combobox to change the status to "completed" The afterupdate event of that control is then running code to insert the date in the date field. Correct? If so, the afterupdate event of the date field does not fire when done through code.

In the afterupdate of the check\combo control you can explicitly save the record with me.dirty=false after inserting date.
 

ypma

Registered User.
Local time
Today, 15:03
Joined
Apr 13, 2012
Messages
643
I'm assuming your either using a checkbox or combobox to change the status to "completed" The afterupdate event of that control is then running code to insert the date in the date field. Correct? If so, the afterupdate event of the date field does not fire when done through code.

In the afterupdate of the check\combo control you can explicitly save the record with me.dirty=false after inserting date.
Thank you Moke, You are correct I am using a combox . I have entered If Me.Dirty Then Me.Dirty = False after the if code to insert the date , this is followed by the run append query .
,but the append query requires this date which has not been saved as the me.dirty appears to be ignored please advise. Due to my basic VB skills I am struggling with Isaac suggestion and was hoping the Me. Dirty = False would solve my problem

Regards Ypma .
 

ypma

Registered User.
Local time
Today, 15:03
Joined
Apr 13, 2012
Messages
643
Can you post a copy of the db
Bob, thank you for your interest, I had a light bulb moment and realised the date I was trying to save was todays date and all I had to do was append Date() to the completed date field in the task .Problem resolved not solved

Moke and Iscaac thank you for you input
Regards Ypma
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:03
Joined
Feb 19, 2002
Messages
42,985
I'm not sure what you think happened but to save the current record, you have several choices. One explicit save method and all the others are hacks.
DoCmd.RunCommand acCmdSaveRecord

In rare situations (I have never encountered in 25+ years of using Access), you might have to use:
Me.Dirty = False ''' force record to save
 

Isaac

Lifelong Learner
Local time
Today, 08:03
Joined
Mar 14, 2017
Messages
8,738
Moke and Iscaac thank you for you input
Due to my basic VB skills I am struggling with Isaac suggestion and was hoping the Me. Dirty = False
Hey I just saw this thread after losing track of it for a bit. Just to clarify what I thought your need was, vs. what I suggested. Since I hadn't elaborated before and you mentioned needing clarity.

My understanding of the need:
....You need to take a value (date) that was only present in the BeforeUpdate event of a form record, and then "do something" with it, (like running an Append Query) AFTER the form updated.

My suggestion was:
  1. In a new module (for simplicity), declare a global variable, like: Global dtSomething as Date
  2. In forms' BeforeUpdate event, when you know the value of the date:
    1. set the form control value like you already are probably: Me.ControlName.Value=Now()
    2. assign that value to the global variable dtSomething=Me.ControlName.Value
  3. Create a Public Function, which you can do in a Regular Module as: Function FunctionName() as Date with one line of code: FunctionName = dtSomething
  4. Refer to that function in your Query. You can refer to the function directly as FunctionName()
I don't know whether this advice is relevant any more given the ongoing convo., but just to clarify one possible solution based on what I thought your problem was.

PS, @Pat Hartman I agree with most of what you posted with the exception of saving the record. In my humble opinion, Me.Dirty=False is absolutely the preferred way to save a record, and is exceedingly common. I try to avoid using RunCommand almost as much as I would DoMenuItem. It's just too context-sensitive. Formobject.Dirty seems, to me, the very most "explicit". But I can see reasonable minds on both sides of this I guess. I understand that from an extremely theoretical perspective, you may be correct in referring to it as a hack. But the access developers that use this is, I would humbly suggest, probably a majority. I don't use RunCommand if I can avoid it, nor DoMenuItem for the same reason - it's too sensitive on the context of focus and display and parent/host object to the code. Formobject.Dirty leaves nothing to the imagination.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:03
Joined
Feb 19, 2002
Messages
42,985
DoCmd.RunCommand acCmdSaveRecord is the only VBA command to explicitly save a record. Me.Dirty = False looks more like a Cancel event and implies that the dirty property is simply being unset rather than the record is being saved. The Me.Dirty solution was developed because someone discovered a problem where apparently multiple forms were in play and code running in one form either affected or did not affect some other form. That actual issue seems to be lost to time.

FormObject.Dirty is a property and a method. Me.Dirty = False is the "property" and Form_Dirty(Cancel As Integer) is the event but the event runs ONCE when the first character is typed to dirty the record. DoCmd.RunCommand acCmdSave applies to the form object.

Referring to menu items has been a problem since day 1 of access since menus change with each new version of Access and items move around at the whim of the designer and eventually we ended up with the Ribbon. RunCommand has always been a better way of running built in Access commands since it doesn't change from version to version except to add/deprecate items. A lot of the bad coding techniques come directly from Access wizards and app templates.
 

Isaac

Lifelong Learner
Local time
Today, 08:03
Joined
Mar 14, 2017
Messages
8,738
A lot of the bad coding techniques come directly from Access wizards and app templates.
I agree with that, and would add that bad coding techniques also come from developers who spent most of their life swearing-off using custom code in favor of built-in objects, and were only forced in latter years to start learning some code. I have had to come into companies where older Access developers used mostly switchboards and macros, and were ultimately forced to open themselves up to the world of coding.......But the code they adopted was learned mostly from things like: Viewing underlying Switchboard code, Viewing "convert macro to vba" code, and the like. These individuals naturally gravitate toward menu commands, without perhaps realizing there is a more unambiguous way to refer to the object.

RunCommand has always been a better way of running built in Access commands
Hmm, I think that depends. As long as you are very aware of the context that command is running within, it's fine. But I would always prefer a method that gave me an opportunity to reference a specific object, over a command that keeps its fingers crossed what the active or implicitly referenced object IS. The main weakness of the RunCommand save method is simply stated: the form you hope record is being saved, has to just so happen to have the focus on that moment. As Allen Browne states: acCmdSaveRecord " fails if the form does not have focus ". "Old days" vs. modern days has nothing to do with this fact - it is still true.

Like I said, in theory you are correct that Me.Dirty is not explicity designed as a Save command. But I think it is preferable to using RunCommand..as you mentioned, RunCommand is context-sensitive, whereas FormObject.Dirty=False will absolutely, unequivocally save any referenced form's record immediately. RunCommand would be more ambiguous depending on which object is hosting the code it is being used in, which form has the focus, etc.
 
Last edited:

moke123

AWF VIP
Local time
Today, 11:03
Joined
Jan 11, 2013
Messages
3,852
Due to the boolean nature of " If Me.Dirty Then Me.Dirty = False" , The one thing you need to be sure of is that the form is actually dirty before you run it.
 

Isaac

Lifelong Learner
Local time
Today, 08:03
Joined
Mar 14, 2017
Messages
8,738
Due to the boolean nature of " If Me.Dirty Then Me.Dirty = False" , The one thing you need to be sure of is that the form is actually dirty before you run it.
Why?
(I may not be understanding your post here - not sure).

Although I do see a lot of "If Me.Dirty Then Me.Dirty=False", I can't say I see what is the need for the test.

I just created a test bound form, opened it without dirtying it, and ran a Me.Dirty=False, with no complaints from Access.
It just triggers the Save event....which doesn't really care if it is running unnecessary or necessarily.
 

moke123

AWF VIP
Local time
Today, 11:03
Joined
Jan 11, 2013
Messages
3,852
Although I do see a lot of "If Me.Dirty Then Me.Dirty=False", I can't say I see what is the need for the test.
Either can I. I was refering to the OP's use of it. In my post (#6) I use Me.Dirty = False.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:03
Joined
Feb 19, 2002
Messages
42,985
If you have code running in a form AFTER you open another form, perhaps you deserve what you get. Don't forget that Me.Requery and Me.Refresh also as a side effect save the current record and sadly, many people who don't know any better use those methods. We usually see the questions regarding the unexpected side effects that occur when you use random methods for their side effects rather than their intended purpose.
 

Users who are viewing this thread

Top Bottom