If an address line or telephone is changed run a query? (1 Viewer)

Number11

Member
Local time
Today, 00:23
Joined
Jan 29, 2020
Messages
607
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

Lifelong Learner
Local time
Yesterday, 17:23
Joined
Mar 14, 2017
Messages
8,738
One way is to put some code in the AfterUpdate event of the controls that hold that information on the form.
 

Number11

Member
Local time
Today, 00:23
Joined
Jan 29, 2020
Messages
607
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

Lifelong Learner
Local time
Yesterday, 17:23
Joined
Mar 14, 2017
Messages
8,738
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.

Code:
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

Member
Local time
Today, 00:23
Joined
Jan 29, 2020
Messages
607
If that's the situation, then maybe instead put it in the BeforeUpdate event of the form.

Code:
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

Lifelong Learner
Local time
Yesterday, 17:23
Joined
Mar 14, 2017
Messages
8,738
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

Super Moderator
Staff member
Local time
Yesterday, 20:23
Joined
Feb 19, 2002
Messages
42,981
You have to account for nulls in your condition because otherwise you'll get invalid results.
 

Isaac

Lifelong Learner
Local time
Yesterday, 17:23
Joined
Mar 14, 2017
Messages
8,738
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
 

Users who are viewing this thread

Top Bottom