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

KACJR

Registered User.
Local time
Today, 07:55
Joined
Jul 26, 2012
Messages
81
I come again to the well of knowledge...

Having a brain cramp here...

I display US address information, Address, City, State, and ZIP Code, in a simple user demographics form.

Prior to displaying the current record in the form, I set a variable, BeforeFullAddress, consisting of a concatenation of all of these items and also set a variable AfterFullAddress, also consisting of a concatenation of all of these items. Thus, when the user enters the record, both the before and after addresses are identical.

When the user make a change to any one of the four address items, AfterFullAddress is updated to reflect any/all changes made. Once the user is done with the current record, I want to compare BeforeFullAddress and AfterFullAddress. If they are different, I would then send an email notification to individual that need to know about the address change.

I have all of the code built to handle the changes and the email process. Where I'm drawing a blank is determining when to trigger the before/after check and kick off the email message if necessary. What I want to do is before the user leaves the current record (next record, previous record, any record move operation), I want to do the before/after comparison, send the email if necessary, then go to the next desired record. I can't seem to find an appropriate event to do this.

Can someone point me in a good direction on this?

Kind regards,
Ken
 

Isaac

Lifelong Learner
Local time
Today, 04:55
Joined
Mar 14, 2017
Messages
8,738
I wanted to point out a feature you can leverage that is more built-in to Access. You might not like the idea of totally changing what you have already built, variable- and scope-wise (which I understand), but still, it's worth considering.

Did you know that bound form controls have a property called OldValue? This means that in the form's BeforeUpdate code, you can read the OldValue vs. Value and, in addition, it would be the right time to trigger the event you are looking for, I believe.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
21,358
Hi Ken. You are correct. There is an event when you enter a record, but none when you leave it. Perhaps the best workaround is the approach @Isaac just suggested. Cheers!
 

KACJR

Registered User.
Local time
Today, 07:55
Joined
Jul 26, 2012
Messages
81
I wanted to point out a feature you can leverage that is more built-in to Access. You might not like the idea of totally changing what you have already built, variable- and scope-wise (which I understand), but still, it's worth considering.

Did you know that bound form controls have a property called OldValue? This means that in the form's BeforeUpdate code, you can read the OldValue vs. Value and, in addition, it would be the right time to trigger the event you are looking for, I believe.

So this would be the BeforeUpdate event for the Form?
 

KACJR

Registered User.
Local time
Today, 07:55
Joined
Jul 26, 2012
Messages
81
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.
 

Isaac

Lifelong Learner
Local time
Today, 04:55
Joined
Mar 14, 2017
Messages
8,738
I think that might be a good choice, yes. In the form's BeforeUpdate event, even though it says 'before' update, you actually have 2 useful properties exposed:
- OldValue, which means the value that the control was before the record was even dirtied by the user
- Value, which means the value that is currently contained in the control--the one that is about to be saved.
 

Isaac

Lifelong Learner
Local time
Today, 04:55
Joined
Mar 14, 2017
Messages
8,738
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?
No, it executes once, immediately prior to when the Form saves that Record's changes to the Table.
I need to trigger the email after all four address fields have POSSIBLY been updated. When all four fields have been changed, I can check for that condition and trigger the email. However that's no 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
Right, so in your BeforeUpdate (form event) code, you compare all 4 controls' .OldValue vs .Value
If it's true that all 4 have changed (if that's the rule you want), then proceed...
 

KACJR

Registered User.
Local time
Today, 07:55
Joined
Jul 26, 2012
Messages
81
No, it executes once, immediately prior to when the Form saves that Record's changes to the Table.

Right, so in your BeforeUpdate (form event) code, you compare all 4 controls' .OldValue vs .Value
If it's true that all 4 have changed (if that's the rule you want), then proceed...

But all four changed is not the rule. It could be one or more of the four fields and I don't know if the user is done until the user leave the current record.

So "BeforeUpdate" executes before the user leaves the record?
 

Isaac

Lifelong Learner
Local time
Today, 04:55
Joined
Mar 14, 2017
Messages
8,738
But all four changed is not the rule
My mistake, I understood your quote:
all-four-fields-changed trigger
to mean the opposite.

Ok...it really doesn't matter. Whether you want the email sent if ONE of the 4 fields changed, or only if FOUR of the 4 fields changed, you just code accordingly.

Use the form's BeforeUpdate event and compare whatever controls you want to compare, OldValue vs. Value.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:55
Joined
Oct 29, 2018
Messages
21,358
I don't know if the user is done until the user leave the current record.
Hi. Like I said though, there's no event for that (leaving a record), so you may have to make the offered suggestion work
 

KACJR

Registered User.
Local time
Today, 07:55
Joined
Jul 26, 2012
Messages
81
I think my only course of action here is to do the check of the addresses fields at the point where the user clicks first-record/prev-record/next-record/last-record or presses page-down or page-up. Do the check and send the email before moving to the next record.
 

Isaac

Lifelong Learner
Local time
Today, 04:55
Joined
Mar 14, 2017
Messages
8,738
I don't know if the user is done until the user leave the current record
You do know when the user is done. It will be when Access decides to save that record, in its entirety, to the table. Access does not do this every time any control on a form is changed. It does this when the user is done with that record - and either moves on to another record, closes the form, or triggers an explicit Save button you may have.

Feel free to try it and find out. Of course you can make whatever decision you like, but the BeforeUpdate event is the only one that makes sense. It's the point in time where Access guarantees you the record is actually being saved. Period.
 

Isaac

Lifelong Learner
Local time
Today, 04:55
Joined
Mar 14, 2017
Messages
8,738
Plus conceptually, you wouldn't want to check it when "leaving a record" - IF there even WAS a way for Access to "leave a record" without triggering an update, which I don't think there is, especially in your case. Why trigger the rule if the record isn't even being saved to the table? Your rule would fire when nothing had been changed. (IF, like I said, that was even possible). The beforeupdate event is when almost all Access developers would put any code that relies on knowing what actually is being changed on a record. It's the place to put any validation type of code, for the most part.
 

KACJR

Registered User.
Local time
Today, 07:55
Joined
Jul 26, 2012
Messages
81
So I just tried the "BeforeUpdate" trigger and it did not work. The "BeforeUpdate" event triggered after I changed each address component so I had four email messages, one for each of address, city, state, and ZIP code.
 

Isaac

Lifelong Learner
Local time
Today, 04:55
Joined
Mar 14, 2017
Messages
8,738
Can you post that code?
The form's BeforeUpdate event only triggers a single time for when the entire record is being saved, which is what you want.
Sounds like you may have coded the control's BeforeUpdate event..
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:55
Joined
Feb 19, 2002
Messages
42,981
The form's BeforeUpdate event runs before the current record is saved. It is possible that Access will find an error and not save the record. Therefore, this is not an appropriate event to use to send the email or to create an audit trail. The correct event to use is the Form's AfterUpdate event. That runs immediately AFTER the record is saved. You have created both before and after variables which are still available. Put the code to compare them in the FORM's AfterUpdate event and send the email if they are different.

Once the BeforeUpdate event runs and control moves to the AfterUpdate event, the .OldValue prior to the save is lost so if you had not already told us that you were saving the before and after images, I would have instructed you to do exactly as you are doing.
 

Isaac

Lifelong Learner
Local time
Today, 04:55
Joined
Mar 14, 2017
Messages
8,738
I can see what Pat is saying, although the whole thing is premised on the idea that the BeforeUpdate event may actually trigger - and the update still fail, which I doubt. The advantage to my suggestion is it uses a functionality built-in to Access for precisely your purpose, using .OldValue vs. Value, and it's usually better to use a built in functionality rather than duplicating it.
The danger of the BeforeUpdate event actually firing, and the record still not being saved is probably no greater than the danger inherent in using your variables and losing their value in case of an error (and if a user presses the End key in the resulting runtime error window), which would also leave you with inaccurate results.

I can see advantages to both methods. One thing is sure - what you mentioned in your recent post means you're doing something differently than what I suggested, as I know it works - I've been using .OldValue during BeforeUpdate for years.

In case it helps, here is an attached database which demonstrates the method I suggested, which works.

Note there are 2 forms. One form is to demonstrate IF your rule is "trigger this when any 1 of the 4 fields are being changed", and another form is to demonstrate IF your rule is "trigger this when ALL 4 of the fields are being changed".
The rule only gets evaluated when the record is saved -- So you can trigger it by going to the next record (similar to your current situation).
 

Attachments

  • Testing 20200916 OldValue.accdb
    480 KB · Views: 237

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:55
Joined
Feb 28, 2001
Messages
27,001
It sounds like you are confusing the events. Sometimes Access has two events with the same name but a different reference. There is a FORM BeforeUpdate event and a CONTROL BeforeUpdate event. They fire at different times.


There is no event when you are about to navigate away from a record on which you did nothing. Here is a reference if you need it:

The FORM_BeforeUpdate event fires ONCE per record updated - including the idea of updating the record but not navigating after you update it. In which case you could end up doing a second update on the same record.

If you use the Form_BeforeUpdate for the problem you described above, you would have code that tested all four of your fields of interest before deciding to do nothing or decided to send your message.
 

KACJR

Registered User.
Local time
Today, 07:55
Joined
Jul 26, 2012
Messages
81
Just for clarification (I didn't write most of the original code)...
There are several "If Me.Dirty then Mr.Dirty = False" statements throughout the code. I would suspect that flipping Mr.Dirty would trigger "BeforeUpdate"?

To those asking, I was definitely working with the Form.BeforeUpdate events.
 

Users who are viewing this thread

Top Bottom