If an address line or telephone is changed run a query?

[Number11]

Hi, So i am looking to see if its possible to run a query only when a user changes say an address line or telephone number, if they did the code would run a query that shows the full record details, so this can then be captured and email to a supplier as an amended order?

 

[Isaac]

One way is to put some code in the AfterUpdate event of the controls that hold that information on the form.

 

[Number11]

One way is to put some code in the AfterUpdate event of the controls that hold that information on the form.

Ok, so i would need to add this to each of the text boxes and if a change has been made i can then code, but what if say all 3 address lines and postcode were changes how would that work as i would only want to run 1 query not a query for each change?

 

[Isaac]

Ok, so i would need to add this to each of the text boxes and if a change has been made i can then code, but what if say all 3 address lines and postcode were changes how would that work as i would only want to run 1 query not a query for each change?

If that's the situation, then maybe instead put it in the BeforeUpdate event of the form.

If (me.txt1.value<>me.txt1.oldvalue) OR (me.txt2.value<>me.txt2.oldvalue) .......... (etc)  then
   if msgbox("would you like to notify of changes?")=vbyes then
      'notify code
  end if
end if

 

[Number11]

If that's the situation, then maybe instead put it in the BeforeUpdate event of the form.

If (me.txt1.value<>me.txt1.oldvalue) OR (me.txt2.value<>me.txt2.oldvalue) .......... (etc)  then
   if msgbox("would you like to notify of changes?")=vbyes then
      'notify code
  end if
end if

Dam so my fields have space like Account No Address Line 1

 

[Isaac]

As you type Me. the pop-ups will correctly put brackets around necessary control names (with spaces). Good reminder for the future, though...
And you want to be referencing form controls, not controlsource names, which of course you should have them different, so you can differentiate..

 

[Pat Hartman]

You have to account for nulls in your condition because otherwise you'll get invalid results.

 

[Isaac]

Pat makes a good point, thanks for the reminder, Pat.
You don't want your code to end up in a situation where you're asking it to evaluate: If Null = something
So if that's a possibility, code for it, perhaps using nz