Run query only if values of either of 2 fields have been updated/changed

[ephi]

I have an append query which is tied to the OnClick event of a button on my form. I want the query to run only if the values of either of 2 fields is updated on the form; the 'Location' field or the 'Assigned To' field.

i.e. if the value of the 'Location' field is changed, or if the value of the 'AssignedTo' field is changed, then the query should run.
The query should also run if both fields have been updated.

The query code is below:

INSERT INTO AssetMovement ( ID, Location, Condition, AssignedTo, AssetTag )
SELECT Assets.ID, Assets.Location, Assets.Condition, Assets.AssignedTo, Assets.AssetTag
FROM Assets
WHERE ID = Forms!AssetsDesktop!txtID;
 

Please help. Thanks.

[Seth Schrock]

I have done something similar to this before and while it does work, it might not be the best way. Here is what I do. In the form's module, right under the Option Explicit, I declare a variable as an integer, like this:

Option Compare Database
Option Explicit
 
Dim intEdited as Integer

Now in the form's OnLoad event, I put

intEdited = 0

In your Location and AssignedTo controls' AfterUpdate events, put

intEdited = 1

Now, in your Save button's OnClick event, you need to test for the value of intEdited and only run your query if it equals 1. Like this:

If intEdited = 1 Then
    DoCmd.SetWarnings = False
    DoCmd.OpenQuery "YourQueryNameHere"
    Docmd.SetWarnings = True
 
    intEdited = 0
End If

This makes it so that if either or both of your controls gets edited, then your query will run when you click your Save button and won't run if they weren't edited.

[Rabbit]

Use the On Current event of the form to store the old values. Then put in a check to compare the old value to the current value before you run the query.

[Rabbit]

Looks like I cross posted with Seth. I would actually store the value because they can the edit variable to be set if they change the value and then change it back.

[Seth Schrock]

That would be true. I hadn't thought of that possibility.

[ephi]

Hi @Seth and @Rabbit, thanks for the replies. How can I implement Rabbit's suggestion of storing the values and then comparing old and current values?
Thanks again. I'm really learning a lot here.

[zmbd]

In the before update event you have access to the control's oldvalue property. Save that to a temp variable.

[Rabbit]

The same way Seth recommended. Except you would store the actual value instead of an indicator.

[NeoPa]

While Seth's solution may well work, Z's idea of using the .OldValue properties from the two controls in the Form_BeforeUpdate() event procedure makes better sense.

Set a module-level flag with the logic in the Form_BeforeUpdate() event, then test this flag in the Form_AfterUpdate() event procedure and execute the SQL if it is True. Don't forget to set the variable back to False when done.

[Rabbit]

I think they only want to insert when they click the button.

Otherwise, if they change it twice, say they made a typo, realized it, and went back to fix it, that would insert two records.

[Seth Schrock]

I think that a combination of the ideas here would probably be best. In the form's BeforeUpdate event, compare both fields old values and new values. If in either field the old and new values don't match, then set the flag to 1. Then the button can test for the flag value and run the Append Query if the flag is 1 and then set it back to 0.

[Rabbit]

What if they change it, realize it wasn't supposed to be changed, then change it back? That would trigger an append.

[Seth Schrock]

They could change it and change it back without saving it and then the append query wouldn't run because the form's BeforeUpdate wouldn't see the change. If they change it, save it, and then change it back, I don't think that there is a way to protect against that.

For example, say that the criteria field 1 had the focus and a value of "Tom", and the user leaned on the keyboard adding an "a" so that the field then read "Toma". As long as the user doesn't hit save before he/she removes the "a", then the control's .OldValue would equal .Value ("Tom") and the flag wouldn't be set to 1 in the form's BeforeUpdate event, which would be triggered by the button's saving the record. However, like I said before if the user accidentally clicks save before the "a" is removed, then Access would have no way to know that one edit was made accidentally while another was made on purpose. I suppose a prompt could be made to confirm the changes to the fields, but again the user could accidentally click "Yes" instead of "No". I don't think that you can fool proof this beyond only checking in the form's BeforeUpdate event.

[zmbd]

[{z1}] query which is tied to the OnClick event of a button
[{z2}] query to run only if the values of either of 2 fields is updated

So Let me restate:
Ephi has only two fields to check and wants to fire an update query if and only if two conditions are true:
[{z1}]: the user has clicked the command button
[{z2}]: at least one of the fields of interest is not the same value as upon entry to the current record.

I have a question that needs to be answered: what happens if there is a change and the user hasn't clicked on the update button and the user changes records ( z1 = false, z2 = true )? As we are, the update query will not run.

[NeoPa]

Rabbit:
I think they only want to insert when they click the button.

Unfortunate. That seems to me to indicate they don't have a very clear idea of the logic involved. I can't imagine a scenario (at least remotely similar to what has been described here) that would make sense to trigger that way.

Rabbit:
Otherwise, if they change it twice, say they made a typo, realized it, and went back to fix it, that would insert two records.

This I don't see as a problem. Logging changes rarely requires aggregates to be logged. If there are two changes that cancel each other out then they are both still logged.

I'm thinking it's very hard to arrive at sensible advice for this question due to the lack of clear thinking behind the question itself. I'm not criticising Ephi for that, they seem to have a good attitude generally, I just think they need to give this aspect some thought before it makes sense to proceed. It is possible for there to be a scenario where this question makes sense, but then more details are necessary in the question to explain the scenario fully. As it is there are more questions than answers for it - which is not a good place to work from.