How to recognize which field of a from is going to be updated? (1 Viewer)

deletedT · Dec 19, 2019

Just curious to know if it is possible to recognize which field(s) of a bound single form is going to be updated, prior to moving to the next record.

A user opens a form, Edits a field(textbox,combo box, checkbox etc) and moves to the next record. In before update event of the form is it possible to recognize which field(s) has been edited?

I know that I can:

Use before update event of the objects in a form. I don't want to use this because a user may update the value of a field, but change his mind and undo the changes before moving to the next record.
Use On-current event to save the current value of the fields to a set of variables or a temp table, then before updating the record, check the current values with previous ones.

What other options do I have?
FE is Microsoft Access & BE is SQL server.

Thanks for sharing your thoughts....

Micron · Dec 19, 2019

Use before update event of the objects in a form. I don't want to use this because a user may update the value of a field, but change his mind and undo the changes before moving to the next record.

For a bound form, there is the OldValue property of controls, which can be compared to the saved value. That could be a lot of comparing if you're talking about many fields. Long ago there was a post somewhere about doing this sort of thing at the table level, but I can't recall how. Do remember that it was flaky though.

Have you considered some other approach, such as allowing enabling of one control at a time and using the OldValue and Value property comparison of one field? I suppose you can also initiate an edit mode, append the temp record to edits table and then compare the record when user tries to close or navigate off. I can't think of anything that's not going to involve a field by field comparison regardless of how you do it when several fields are involved.

Won't surprise me if you get a better and really smart suggestion after my post.

theDBguy · Dec 19, 2019

Hi Tera. You said this:

Tera said:
I don't want to use this because a user may update the value of a field, but change his mind and undo the changes before moving to the next record.

Are you saying you want to know if the user attempted to change/update a field, no matter what? And you want to know which one it was or which ones they were? Do you mind if I ask why? I'm just curious...

There is a way to find out which fields were updated at the table level, but it sounds like that would be too late for you, especially if the user cancels the changes.

deletedT · Dec 19, 2019

@Micron & @theDBguy

I don't want or can't Disable/Enable objects on the form. Because the users need to be able to edit the records.
I just want to run some other functions or do some calculations in case they edit specific fields.

Some of these functions should run prior to the changes take effect.
For example if a user edits the delivery date of an order, I need to send an automated mail to the customer to let them know the new delivery date.
Or if someone changes the used materials for manufacturing a part, a new quote should be produced, a mail should be sent to cancel the previously ordered materials and send a new order for the material's going to be used etc etc.
Or if the used tools for manufacturing a part is changed by a machine operator on the shop floor, I need to warn the designer of the part to be aware of.
There are other fields that if being edited, different actions need to be taken.

Some of these procedures need to be run prior to the changes, some may run after the changes.

theDBguy said:
There is a way to find out which fields were updated at the table level, but it sounds like that would be too late for you, especially if the user cancels the changes.

Yes, unfortunately it may be too late for this situation. But I appreciate if you show me how, because I'm sure someday it will come handy. But still I need to know all the options I have to recognize which fields a user is going to update. (prior to after-update event)

@Micorn thanks for mentioning OldValue. I knew about it but have never used it. I do a research to see if it can do what I'm looking for.

gemma-the-husky · Dec 19, 2019

The normal events to use are the before update, or maybe the after update events of the controls you want to test.

if there is a chance of the user undoing a change after taking the action then you could force a record save after the field edit.

Alternatively, you can use the form's before update event to test whether any fields you need to know about have been changed.

It's not trivial, so you need to consider exactly how you need to manage the data.

The_Doc_Man · Dec 19, 2019

If all of the fields are bound, then in the BeforeUpdate event, you can do something similar to this:

Code:

For Each ctl In Me.Controls
   Select Case ctl.Type
     Case acTextBox, acListBox, acCheckBox, etc.
       If ctl.ControlSource <> "" Then
         If ctl.Value <> ctl.OldValue Then
'          this control has changed
         End If
       End If
     Case Else
   End Select
Next ctl

You can look up the control types in this list:

https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa224135(v=office.11)

However, I will also give you an off-the-wall but true answer to the literal question.

know if it is possible to recognize which field(s) of a bound single form is going to be updated,

All of them are updated. Access rewrites the entire record on updates (to the best of my knowledge). Not all of them will change, but all of them are written.

theDBguy · Dec 19, 2019

Tera said:
Yes, unfortunately it may be too late for this situation. But I appreciate if you show me how, because I'm sure someday it will come handy. But still I need to know all the options I have to recognize which fields a user is going to update. (prior to after-update event)

Hi. To verify if a record was updated at the table level, you can use a TRIGGER in SQL Server

The_Doc_Man · Dec 19, 2019

Good point, theDBguy, since the BE is SQL Server in Tera's case. But unless there are transactional records involved, only the FE knows what is going to be changed, and it knows that during the Before_Update event.

Here is a reference for event order.

https://support.office.com/en-us/ar...ects-e76fbbfe-6180-4a52-8787-ce86553682f9#bm2

For update-related event sequences in forms, the sequence is:

BeforeUpdate (form) >> AfterUpdate (form) >> Exit (control) >> LostFocus (control) >> Current (form)

In the BeforeUpdate event, the .Value and .OldValue will clearly show a difference for the updated fields. By the time Current rolls around, .Value and .OldValue will match each other because at that point, the form is "current" with respect to the record.

theDBguy · Dec 19, 2019

Hi Doc. The information about Triggers was provided for future reference only, as requested by the OP. We already agreed it doesn't apply in the current situation, because it will be "too late" to be useful for the OP's purposes. Cheers!

Pat Hartman · Dec 19, 2019

I don't like using ANY control events or the form's BeforeUpdate event to send email notifications because at that time, you don't know if the record will actually be saved. However, by the time the form's AfterUpdate event runs, you have lost the information regarding which fields changed.

My solution would be to use the AfterUpdate event of the specific control that will trigger the email and set a flag in a hidden control on the form. Then in the AfterUpdate event of the form, check the flag to see if any email needs to be sent. Then clear the flag. This should minimize the checking that needs to happen and will prevent you from sending emails for records that don't actually get saved.

deletedT · Dec 20, 2019

Pat Hartman said:
My solution would be to use the AfterUpdate event of the specific control that will trigger the email and set a flag in a hidden control on the form. Then in the AfterUpdate event of the form, check the flag to see if any email needs to be sent. Then clear the flag. This should minimize the checking that needs to happen and will prevent you from sending emails for records that don't actually get saved.

That's exactly what I intend to do. The mail/calculation will be processed after-update event. But I need to check which fields have been edited to be able to trigger the correct process. After update in some cases won't work, because I need the values of several fields before being edited to use in my calculation.

Editing deliveryDate will trigger a different process than editing UsedMaterials field or UsedTools field. I was trying to find which fields are going to be updated to set the necessary flags.

deletedT · Dec 20, 2019

I want to thank all who shared their knowledge here to solve the problem. specially @Micron for suggesting oldValue, @The_Doc_Man who shared a sample code and @theDBguy for his suggestion and the link.
It seems the OldValue property suggested by @Micron satisfies my need. I'm experimenting different situation here and checking if it passses all tests. And probably publishing the new version of this database.

For now I mark the thread as answered, but don't feel relieved, because I'll wake you up if I hit a wall.

Million thanks

Micron · Dec 20, 2019

don't feel relieved, because I'll wake you up if I hit a wall.

In the meantime, I'll just keep on dreaming...

theDBguy · Dec 20, 2019

Tera said:
That's exactly what I intend to do. The mail/calculation will be processed after-update event. But I need to check which fields have been edited to be able to trigger the correct process. After update in some cases won't work, because I need the values of several fields before being edited to use in my calculation.

Editing deliveryDate will trigger a different process than editing UsedMaterials field or UsedTools field. I was trying to find which fields are going to be updated to set the necessary flags.

Hi. Sounds to me like you have at least two options here (maybe more). For example, given a form with three fields from a table: Field1, Field2, and Field3. And updating each field will run a different function like: Action1, Action2, and Action3. So,

Option #1
In the AfterUpdate event of the Form, you might try something like (pseudocode):

Code:

If Updated(Field1) Then
    Do Action1
ElseIf Updated(Field2) Then
    Do Action2
ElseIf Updated(Field3) Then
    Do Action3
End If

or Option #2
In the AfterUpdate of each field:

Code:

Field1_AfterUpdate()
Do Action1

Code:

Field2_AfterUpdate()
DoAction2

Code:

Field3_AfterUpdate()
DoAction3

theDBguy · Dec 20, 2019

Tera said:
I want to thank all who shared their knowledge here to solve the problem. specially @Micron for suggesting oldValue, @The_Doc_Man who shared a sample code and @theDBguy for his suggestion and the link.
It seems the OldValue property suggested by @Micron satisfies my need. I'm experimenting different situation here and checking if it passses all tests. And probably publishing the new version of this database.

For now I mark the thread as answered, but don't feel relieved, because I'll wake you up if I hit a wall.

Million thanks

Oops, looks like you posted this while I was typing the two options I posted above.

Glad to hear you got it sorted out. Good luck with your project.

gemma-the-husky · Dec 29, 2019

as a slight variation, what you could do if a user is editing a record (rather than entering a new one) is use the control's after update, and then immediately save the record, and send the notification. Useful in some cases.

How to recognize which field of a from is going to be updated? (1 Viewer)

deletedT

Guest

Micron

AWF VIP

theDBguy

I’m here to help

deletedT

Guest

gemma-the-husky

Super Moderator

The_Doc_Man

Immoderate Moderator

theDBguy

I’m here to help

The_Doc_Man

Immoderate Moderator

theDBguy

I’m here to help

Pat Hartman

Super Moderator

deletedT

Guest

deletedT

Guest

Micron

AWF VIP

theDBguy

I’m here to help

theDBguy

I’m here to help

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread