Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Automatically fire After Update event on all records in form using existing data

Posted on 2013-06-13
Medium Priority
Last Modified: 2013-06-13
Here is my situation.  I have existing data in a field called ITP Date.  I have added additional fields which are calculated based on the data entered in ITP, (but again, the data is already there).  Is there any way to get the new fields to update without rekeying the data into ITP?

Question by:manndo
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 58
ID: 39244794
You'd want to use an update query against the table.

LVL 11

Expert Comment

ID: 39244803
Use DB trigger to further update other column upon the change of a specific column (in this case which is ITP date). Read

Author Comment

ID: 39244906
The AFter Update Event is on the form.  Re-entering the data into the table does not fire the After Update event, thus the other fields based on the After Update event are not calculated.  The After Update event placed on the ITP field is this......

Me.[1st Review] = DateAdd("d", 90, Me.ITP)
Me.Redevelopment = DateAdd("d", 365, Me.ITP)

Neither the 1st Review field or the Redevelopment fields are updated after running Update query on the Table.  Thanks again for your help.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Expert Comment

ID: 39244972
I have added additional fields which are calculated based on the data entered in ITP, (but again, the data is already there).
what are the default values these fields??? are they NULL???

1. About triggers, you can create them to catch INSERT, UPDATE and DELETE events, but not for ALTER TABLE...
2. About your new fields if thes are NULL you can recalculate then...
3. After that you need to create a trigger to calculate new records, or do this at program level.
4. If you cann't do #3 you have to recalculate your fields before each data request, but only them who are NULL, in this case an index can speed up the process.

LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 39244976
<<Neither the 1st Review field or the Redevelopment fields are updated after running Update query on the Table.  Thanks again for your help. >>

 You'll need to refresh the forms recordset after you run the update query.

 I'm assuming you wanted to do this as a one time only.

 In your update query, drag down the 1st Review and Redvelopment fields.

 On the update line under those columns, do:

DateAdd("d", 90, [ITP])


DateAdd("d", 365, [ITP])

 Execute the query.  Now check the table; are the fields updated?  

 If so, then either close/open the form or hit F5 to refresh the data.

From this point forward then, you form shoul dbe updating the fields/records with the AfterUpdate event of the ITP control.


Author Closing Comment

ID: 39245073
Thank you Jim.  Your solution is what I was looking for on this one.  It worked great.  I really appreciate it.  Yes.  It was a one-time thing and from now on when data is entered on the form, the After Update event will update the fields.  Thanks again.

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question