By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,808 Members | 1,477 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,808 IT Pros & Developers. It's quick & easy.

AfterUpdate event not running, but works on manual change

P: 62
Hello -

I have many activities that are related to one company.

I have many contacts related to one activity via a simple join table called t_RelatedContacts that holds the Activity_ID and the Contact_ID.

If a user decides to change the the company that the activities are related to, we need to delete any related contacts that may have been added for that activity as only contacts from the related company would be valid.

I have the following Event Procedure to delete records in the t_RelatedContacts table when the Company ID field on my form changes.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Company_ID_AfterUpdate()
  3. Dim strSQL As String
  4. Dim Activity_ID As Integer
  6. DoCmd.SetWarnings False ' to prevent confirmation of record delete
  8.     Activity_ID = Me.Activity_ID.Value
  10.     strSQL = "DELETE t_RelatedContacts.* FROM t_RelatedContacts WHERE (((t_RelatedContacts.Activity_ID)= " & Activity_ID & "  ));"
  12.     DoCmd.RunSQL strSQL
  14.     DoCmd.SetWarnings True
  16.     DoCmd.RunCommand acCmdRefresh
  18. End Sub

The event does not run when the value in the Company_ID field is updated after a new company in selected. However, it runs perfectly if I manually change the value in the Company_ID field.

Any ideas greatly appreciated.
Many thanks

Attached Images
File Type: jpg 12-19-2011 4-44-55 PM.jpg (32.5 KB, 1785 views)
Dec 19 '11 #1
Share this Question
Share on Google+
9 Replies

Expert Mod 15k+
P: 31,489
How do you know it doesn't get triggered? Simply because the results are otherwise than expected?
Dec 20 '11 #2

P: 62
Hello NP -

I can tell because the event works correctly when I manually trigger it.

Lets say there are 3 related contacts when I trigger the event:

In the case of the OnUpdate event, when I go to the Related Contacts tab to view the form, the 3 records are still there but they are blank because the contacts are related to the Company_ID, which has now changed sothere is no match.

In the t_RelatedContacts table, the records are still showing, even after I do a manual Refresh.

When I trigger the event by manually changing the value in the Company_ID field, the event fires correctly - the 3 records in the form are gone, and the table shows #Deleted for the 3 contacts that were related to that activity.

Please let me know if there is other information I can give that might help shed light on this.

Thank you,
Dec 20 '11 #3

P: 759
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdSaveRecord
This will save current record ant trigger the after update event for all bounded controls.
Expand|Select|Wrap|Line Numbers
  1. Me.Refresh
Expand|Select|Wrap|Line Numbers
  1. If Me.Dirty Then Me.Dirty = False
Of course you must use one of this after you have a new data in Company_ID control.
Dec 20 '11 #4

P: 62
Thanks Mihail, but that did not help either.

I have spent too much time on this so instead I wrote a cascade delete for the entire activity. Probably a better data quality strategy anyway so no straggler records are left behind.

Dec 21 '11 #5

Expert Mod 15k+
P: 31,489
Hello Sandra. I suspected that may be the case. I'm afraid your logic is faulty. That only means that the code didn't behave as expected. To ensure that you know if the code has executed or not set a breakpoint in the procedure. You can find out more about that if you need to from Debugging in VBA.

PS. Cascade deleting is a good idea too. It helps to use different aspects of Access so that you understand more. I would certainly recommend following through with this question though, so you can get to the bottom of it and have that understanding also in your Access arsenal. It needn't be in critical time though. Just cut a copy to test with and proceed as normal with your main development.
Dec 21 '11 #6

P: 62
Thank you, NP - I rarely give up on something and I will continue to pursue this during non-work hours just to get to the bottom of it.

Thanks for the link to debugging VBA. I use "Step Into" extensively in Excel but have not done so in Access.

I will post back when I figure this out.

Dec 21 '11 #7

Expert Mod 15k+
P: 31,489
Not a problem Sandra. If you can confirm, by following the code with a breakpoint, that the code is not being executed (or is even), I'll be happy to see what else I can do to help. I suspect it actually is, but the effect is not as you'd expect, but the proof of the pudding is in the eating as they say, so let's see the actual situation and we can progress from there. We often have to make assumptions when dealing with people and their questions, but it's best to avoid them (assumptions) wherever possible, particularly when dealing with logic and program-flow.
Dec 21 '11 #8

P: 62
Hi again -

I turned on the watch window and put in breakpoints but this does not help me solve the issue since the code never runs in the first place.

I see the value in the Company_ID field changing on the form itself, but for some reason it is not triggering the OnUpdate Event. (I tried OnChange as well)

Saving the record by adding DoCmd.RunCommand acCmdSaveRecord before executing the SQL did not help either.

I am baffled.
Dec 21 '11 #9

Expert Mod 15k+
P: 31,489
This is what I expected as far back as post #2 Sandra. It is very helpful, as it stops you barking up the wrong tree for however long you might have, just to find yourself back exactly where you (should have) started. At least now you know where to look for the problem.

So, you have a control (Not a field!) on your form called [Company ID]. What type of control is it, and what exactly are you doing that you would expect to trigger the AfterUpdate event, but which seems not to be?
Dec 22 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.