473,324 Members | 1,856 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

AfterUpdate event not running, but works on manual change

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()
  2.  
  3. Dim strSQL As String
  4. Dim Activity_ID As Integer
  5.  
  6. DoCmd.SetWarnings False ' to prevent confirmation of record delete
  7.  
  8.     Activity_ID = Me.Activity_ID.Value
  9.  
  10.     strSQL = "DELETE t_RelatedContacts.* FROM t_RelatedContacts WHERE (((t_RelatedContacts.Activity_ID)= " & Activity_ID & "  ));"
  11.  
  12.     DoCmd.RunSQL strSQL
  13.  
  14.     DoCmd.SetWarnings True
  15.  
  16.     DoCmd.RunCommand acCmdRefresh
  17.  
  18. End Sub
  19.  
-------------

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

Attached Images
File Type: jpg 12-19-2011 4-44-55 PM.jpg (32.5 KB, 2147 views)
Dec 19 '11 #1
9 5475
NeoPa
32,556 Expert Mod 16PB
How do you know it doesn't get triggered? Simply because the results are otherwise than expected?
Dec 20 '11 #2
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,
Sandra
Dec 20 '11 #3
Mihail
759 512MB
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdSaveRecord
This will save current record ant trigger the after update event for all bounded controls.
Or
Expand|Select|Wrap|Line Numbers
  1. Me.Refresh
Or
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
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-
Dec 21 '11 #5
NeoPa
32,556 Expert Mod 16PB
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
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-
Dec 21 '11 #7
NeoPa
32,556 Expert Mod 16PB
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
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.
s
Dec 21 '11 #9
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

3
by: jdph40 | last post by:
In Access 2002, I designed a simple database for our Safety department to enter results of a survey. There are 41 true/false statements. I have a main form called frmSurvey with a subform called...
2
by: jv | last post by:
Hello, I have a Service Cancellation form that contain a CancelDate text box. The text box has an AfterUpdate event that perform a few calculations. This works fine. However, next to the text...
0
by: The Coolest Dolphin | last post by:
OnMouseOver & OnMouseOut event on tablerow to change backcolor of row Hi everyone, I'm looking for a way to use a mouseover/mouseout event (normally done via javascript) via Visual...
5
by: EManning | last post by:
I'm developing an unbound form with a series of questions on it. Each question is either a text box or an option group. I have coding in the AfterUpdate event of each control to highlight the...
4
by: Tom_F | last post by:
To comp.databases.ms.access -- I know that the recommended way to detect an update to a form, is to use the form's "Before Update" event, and putting in VBA code like: Private Sub...
7
by: technocraze | last post by:
Hi guys, I encountered this error while using the AfterUpdate event for my listbox. Error: Update or CancelUpdate without using AddNew or Edit. What i wanted to achieve is just to display the...
2
MatthewML
by: MatthewML | last post by:
I am attempting to insert a custom AfterUpdate Event Procedure into a text box on a form that I am designing in MS Access 2000. This text box contains the e-mail address of the referenced contact,...
4
by: bcallnan | last post by:
Hello All- I am trying to reference a subform's afterupdate event that is 3 deep and am having some trouble getting it to work. The control is a combo box and i cannot seem to trigger the...
4
kcdoell
by: kcdoell | last post by:
Hello: I have the following afterupdate event: Private Sub GWP_AfterUpdate() 'Updates the Total calculation in the control "SumGWP" on the quick reference 'table that is located on the form...
3
mshmyob
by: mshmyob | last post by:
I am scratching my head over this. I have a combo box control where the afterupdate event or even the onchange event keeps runnning twice. Below is some simple code to emulate the problem I am...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.