Is it possible to cancel an event procedure from another one?

[Ali Edwards]

I have an event procedure attached to the After Update of the 'Surname' field in a form. I also have a macro that, amongst other things, deletes the data in the 'Surname' field but of course as soon as the focus leaves the field, the After Update event procedure runs and prevents the macro from finishing it's task (as well as giving me plenty of error messages....
Is it possible to cancel the After Update event procedure until data has been 'reinput' into the Surname field?
Be gentle with me - I'm new to this code stuff!

Many thanks for any help.

 

[Pat Hartman]

Move the code to the BeforeUpdate event and the loop will disapear. The AfterUpdate event fires AFTER the change is committed NOT before. Therefore, when you change the contents of a field in its own AfterUpdate event, you put Access into an infinite loop. The user changes the field - the BeforeUpdate event fires - the AfterUpdate event fires - your code changes the field dirtying the record again - the BeforeUpdate event fires - the AfterUpdate event fires - your code changes the field dirtying the record again ......

You can use me.YourField.undo to back out changes and use cancel = True to cancel the update.

 

[JCollins]

You can also assign a global variable "gblnCancel".

Assign it to True or False in the macro and then in your After Update Event do the following:

If gblnCancel =False then
.....
Else
.....
End If

Good Luck

 

[Ali Edwards]

Wow! That was speedy! - I have tried as you suggested but as the procedure requests a 'Goto Control' I get a message that tells me that I must save the field before the 'Goto control' can be acrried out..

This is my code - it basically copies and pastes important data into a text box (temporarily) when the 'Last Name' field is changed. When the data input is complete the user clicks on the 'UPDATE' label (which becomes visible when the 'Last Name' field data is changed). Clicking on the 'UPDATE' label runs a macro which cuts the data from the text box and pastes it into a form, inputting a date etc. so it's a kind of chronological record of changes made to the database...Phew!!
----------------------------------
Private Sub LastName_BeforeUpdate(Cancel As Integer)

' Attached to After Update property of Last Name field

DoCmd.GoToControl "SN"
' Go to the beginning of the field
SendKeys "{Left 10}", True
' Select up to 30 characters
SendKeys "+{Right 10}", True
' Copy
SendKeys "^c", True
' ClipboardForm
DoCmd.GoToControl "ClipboardForm"
' Arrow right 200
SendKeys "{RIGHT 200}", True
' Paste the SN
SendKeys "SN ^v ", True

DoCmd.GoToControl "FirstName"


(blah blah blah, collecting data from several fields as above)

[Forms]![Master Input Edit]![Cover1].Visible = True
[Forms]![Master Input Edit]![Cover2].Visible = True
[Forms]![Master Input Edit]![Update Label].Visible = True
[Forms]![Master Input Edit]![New Data Label].Visible = True

End Sub

---------------------------------------

This works OK on it's own but then when I want to copy a record with similar details I use a macro that first duplicates the record and then deletes the 'First Name' and 'Last Name' fields so that the new names can be input. As soon as the Last Name is deleted though, the above event procedure runs and causes an error. What I really need to do is to have something cancel the above event procedure whenever the 'Copy Record' macro is called and reinstate it when data is typed into the fields.....Confused??

Is this at all possible?
Thanks again for your response!!

 

[Ali Edwards]

Pat and JC - thanks.

JC - I'm sorry - brain of an amoeba - how do I do what your suggesting? I've looked up 'gblnCancel' in Help but there is nothing about it.

Your patience is appreciated guys!

 

[JCollins]

sorry....gblnCancel is just the nomenclature...A Global Boolean variable

Public gblnCancel as boolean 'Dimension this as a public variable

'Set the variable where you determine whether or not to use the after update event

gblnCancel=True

...After_Update

if gblnCancel=True then
'Run the procedure
Else
'Do not run the procedure
end if

I hope that helps...

 

[Ali Edwards]

Thanks JC. Really sorry to come back again but I feel that we're close! I'm obviously going wrong somewhere....

I've inserted the code in the After Update property as follows:

Private Sub LastName_AfterUpdate()
Dim gblnCancel As Boolean
If gblnCancel = True Then

DoCmd.CancelEvent

' Attached to After Update property of Last Name field

Else

'Run the procedure
DoCmd.GoToControl "SN"
' Go to the beginning of the field
SendKeys "{Left 10}", True
' Select up to 30 characters
SendKeys "+{Right 30}", True
' Copy
SendKeys "^c", True
' ClipboardForm
DoCmd.GoToControl "ClipboardForm"
' Arrow right 200
SendKeys "{RIGHT 200}", True
' Paste the SN
SendKeys "SN ^v ", True


I need to cancel the above event whenever a macro runs so I've inserted the gblnCancel=True in the Condition of the macro like this:

gblnCancel=True
...

then the Actions of the macro commence.

Please help if you can and by now you've realised that I have no understanding of code (though I'm learning fast!)

Thanks again.

 

[Pat Hartman]

There is a MUCH easier and less troublesome way to "copy" the contents of one control into another -

Me.Control2 = Me.Control1

There is also an easy way to obtain the value of a control prior to the most recent change -

Me.Control1.OldValue

Macros should only be used as a last resort. If there is ANY other way to do something DO NOT use a macro.

 

[Ali Edwards]

Thanks for the advice Pat. This sounds interesting and I'm going to try it right now.....

 

[Ali Edwards]

Great success with the Me.Control2 = M.Control1 and
Me.Control1.OldValue advice - thanks a lot!
Couldn't get the gblnCancel thing to work but managed to do what I wanted by making a box visible when Field.After_Update and then stating to cancel the After_Update event whenever the box is visible in the After_Insert property....so when a new record is added the After_Update event is cancelled. Maybe clumsy to you geniuses but it works!

Thanks again guys.