• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Solved Actions to take before move to a different record in a form (1 Viewer)

Isaac

Lifelong Learner
Local time
Today, 06:07
Joined
Mar 14, 2017
Messages
2,389
There are several "If Me.Dirty then Mr.Dirty = False" statements throughout the code
What that does: If the record has unsaved changes (i.e., someone has changed a control's value, but the record isn't saved yet), then Save the Record.

If you have people who coded that willy-nilly all over the Controls' Change, Enter, Exit, or AfterUpdate events, then no wonder it's confusing--your form is saving a record constantly, and far more than is needed.

Yes, Me.Dirty=False will 1) save the record, 2) trigger the beforeupdate event.

And by the way, if the db you inherited has all of that 'extra' code that it sounds like it does, where it is forcing the form to Save the entire record many more times than is necessary, then you'll end up with the same set of problems even if you use the form's AfterUpdate event...because if an end user edits one single control and then some code fires "Me.Dirty=False", that will trigger a Save...which will trigger BOTH the BeforeUpdate AND the AfterUpdate events, and you'll be left with a confusing situation, because the code will be forcing the entire record to save over and over while the poor user is just editing individual controls!

First order of business - make sure there is no code to explicitly force a save (me.dirty=false), anywhere except where it really should be, which might well be "nowhere", since the form will save the record any time someone goes to another record or closes the form..

To those asking, I was definitely working with the Form.BeforeUpdate events.
Well, all I can say is I posted a working database as an example to help you out. It works. Check it out. Good luck with your project!
 
Last edited:

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 08:07
Joined
Feb 28, 2001
Messages
18,012
Yes, forcing the "Dirty" flag to FALSE would force an update and trigger all update-related FORM events.
 

vhung

Member
Local time
Today, 06:07
Joined
Jul 8, 2020
Messages
144
I'm not sure that "BeforeUpdate" satisfies my need. If I'm reading correctly, "BeforeUpdate" is executed anytime any field on the form is changed? My dilemma is I need to trigger the email after all four address fields have POSSIBLY been updated. When all four fields have been changed, that's easy; I can check for that condition and trigger the email anytime while I'm on the current record. However that's not always the case; an individual could move to a new address in the same city and/or state, in which case the all-four-fields-changed trigger doesn't work. I can only trigger the check once the user leaves the current record.

I think I'm stuck.
quite hard
>address, city, state, and ZIP code.
>using "BeforeUpdate" could be possible, where there is 1 field that received your old "address, city, state, and ZIP code" as field1
>could be the other field received your new "address, city, state, and ZIP code "AfterUpdate" as field2
>in a quick way: could use messagebox where; field1 and field2 should be measued by its length; if f1=f2 maybe unchange; but if f1 is not = f2 then has changes
>others use tag
 

arnelgp

error reading drive A:
Local time
Today, 21:07
Joined
May 7, 2009
Messages
10,566
Code:
dim tfEmail As boolean

private sub form_beforeupdate(cancel as integer)
    tfEMail = (AfterFullAddress <> BeforeFullAddress)
end if

private sub form_afterupdate()
    if (tfEMail = True) Then
        'your code here to email it
    end if
    tfEMail = False
end sub
as proposed, do it on the Form's Before Update event.
and since it is a Validation event, i would try to make the code
short:
 

arnelgp

error reading drive A:
Local time
Today, 21:07
Joined
May 7, 2009
Messages
10,566
mr.vhung how are you?
filipino?
 

arnelgp

error reading drive A:
Local time
Today, 21:07
Joined
May 7, 2009
Messages
10,566
i am! and also the famous theDBGuy!
 

arnelgp

error reading drive A:
Local time
Today, 21:07
Joined
May 7, 2009
Messages
10,566
taga bulacan ako, si db nasa estate. ikaw?
 

arnelgp

error reading drive A:
Local time
Today, 21:07
Joined
May 7, 2009
Messages
10,566
nagka usap din kmi ni dbg, taga-Caloocan xa.
kaya pala un mga form no, pang lgu?!
ok, baka ma-huli tau.
 

KACJR

Registered User.
Local time
Today, 09:07
Joined
Jul 26, 2012
Messages
67
Well, the original developer must have had some serious trust issues. I think I disabled close to 50 "Dirty=false" statements. So "BeforeUpdate" now seems to be doing what it is supposed to do.

One last anomaly, though. When I move to a new record, it appears that "BeforeUpdate" is triggered before the form is actually rendered. I looked for more "Dirty=false" statements but can't find any. It's not doing any harm because the address hasn't been changed at this point, but it seems unusual.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:07
Joined
Feb 19, 2002
Messages
29,746
Isaac,
Are you really willing to knowingly write code that will fail under well known conditions despite their lack of frequency?

There really isn't any option. You do not want to send an email if the record is not actually saved. Period.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:07
Joined
Feb 19, 2002
Messages
29,746
The "Dirty = False" statements should ALWAYS be encased in an If statement. You don't want to force a record to save unless it was changed.
Code:
If Me.Dirty = False Then
   Me.Dirty = False
End If

The Form's BeforeUpdate event should only ever run if the record was dirtied. If it is running every time you scroll to a new record, something in your code is dirtying the record and it shouldn't be.
 

Isaac

Lifelong Learner
Local time
Today, 06:07
Joined
Mar 14, 2017
Messages
2,389
When I move to a new record, it appears that "BeforeUpdate" is triggered before the form is actually rendered. I looked for more "Dirty=false" statements but can't find any. It's not doing any harm because the address hasn't been changed at this point, but it seems unusual.
That sounds normal to me. The instant that you start going to a different record, Access is saving that current record, which triggers the event.

Isaac,
Are you really willing to knowingly write code that will fail under well known conditions despite their lack of frequency?
Not sure what you mean by well known condition. As I pointed out before, odds also exist that your suggestion of variables could result in an inaccurate result, too. Neither of our suggestions was 100% certain never to produce inaccurate results. I guess if either of us wanted to get to the "zero chance of inaccuracy" spot, then we would both have suggested a table-driven method only...

(You could also claim that when a SQL server transaction is committed, that's "not to be fully trusted" yet, because the server room might blow up after that point but before the log checkpoint, but most people are good at the 'committed' point) : )
So that's my take on it.

But, out of curiosity, can you give me an example of a "well known condition" that would cause a form's BeforeUpdate event to actually trigger, but still err (and not because of a mistake in the BeforeUpdate event code) before completely updating?

Put another way: Once the BeforeUpdate event fires, and if there was no code inside the event (just the stub), theoretically could it ever fail in between the time it first fires, and the time it ends? In other words, all else being equal, do you not believe that BeforeUpdate firing is an indication that the update is going to succeed?

Regardless of the answer, I stand by my evaluation that neither method suggested was perfectly guaranteed to be accurate in absolute terms. A table driven approach might have.
Your view on my suggestion is roughly how I feel about your oft-suggested DoCmd.RunCommand, in cases that are extremely context/focus-sensitive. I'd say it's much easier to damage one's data by using DoCmd.RunCommand (and have the Focus on the wrong form/subform), compared to my suggestion in this post.
To each his own - people are going to have different takes on the relative risks of all mentioned items.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:07
Joined
Feb 19, 2002
Messages
29,746
Thanks for correcting my typo.

Neither of our suggestions was 100% certain never to produce inaccurate results.
We're going to have to agree to disagree about that.
 

KACJR

Registered User.
Local time
Today, 09:07
Joined
Jul 26, 2012
Messages
67
The "Dirty = False" statements should ALWAYS be encased in an If statement. You don't want to force a record to save unless it was changed.
Code:
If Me.Dirty = False Then
   Me.Dirty = False
End If

The Form's BeforeUpdate event should only ever run if the record was dirtied. If it is running every time you scroll to a new record, something in your code is dirtying the record and it shouldn't be.
Sorry. All of the Dirty commands were part of an If...Then as you noted. In any event, I commented them all out until I'm certain that everything is functioning as it should. From the looks of it, the Dirty commands were overkill.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:07
Joined
Feb 19, 2002
Messages
29,746
Since Access naturally saves a dirty record when something causes it to move to a different record or close the form, the only time I ever force a record save is with code in the click event of a button. Sometimes the user needs to see some generated data. Other users simply like the comfort of a deliberate save action. Code to force a save in every control is not only overkill, it is just plain wrong. Your form's BeforeUpdate event should have validation code that checks for valid data and missing data and prevent a record from being saved if it is incomplete or invalid. Saving in every control prevents this proper practice.
 

Users who are viewing this thread

Top Bottom