AfterUpdate event not running, but works on manual change

[Sandra Walsh]

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_RelatedContac ts 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_RelatedContac ts table when the Company ID field on my form changes.

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

Private Sub Company_ID_AfterUpdate()

Dim strSQL As String
Dim Activity_ID As Integer
    
DoCmd.SetWarnings False ' to prevent confirmation of record delete

    Activity_ID = Me.Activity_ID.Value

    strSQL = "DELETE t_RelatedContacts.* FROM t_RelatedContacts WHERE (((t_RelatedContacts.Activity_ID)= " & Activity_ID & "  ));"

    DoCmd.RunSQL strSQL

    DoCmd.SetWarnings True
   
    DoCmd.RunCommand acCmdRefresh
   
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
Sandra

[imgnothumb]http://bytes.com/attachments/attachment/5840d1324331290/12-19-2011-4-44-55-pm.jpg[/imgnothumb]

[NeoPa]

How do you know it doesn't get triggered? Simply because the results are otherwise than expected?

[Sandra Walsh]

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_RelatedContac ts 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,
Sandra

[Mihail]

DoCmd.RunCommand acCmdSaveRecord

This will save current record ant trigger the after update event for all bounded controls.
Or

Me.Refresh

Or

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.

[Sandra Walsh]

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.

Thanks!
S-

[NeoPa]

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.

[Sandra Walsh]

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.

S-

[NeoPa]

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.

[Sandra Walsh]

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.RunComman d acCmdSaveRecord before executing the SQL did not help either.

I am baffled.
s

[NeoPa]

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?