Solved

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

Posted on 2013-06-13
6
264 Views
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?

Thanks.
0
Comment
Question by:manndo
6 Comments
 
LVL 57
ID: 39244794
You'd want to use an update query against the table.

Jim.
0
 
LVL 10

Expert Comment

by:HuaMinChen
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
CREATE TRIGGER
0
 

Author Comment

by:manndo
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:BAKADY
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.

regards
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 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])

and

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.

Jim.
0
 

Author Closing Comment

by:manndo
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Using TSQL to Return Column Data as Rows 3 42
Oracle Pivot 2 35
Access Query concatenation 12 46
sQL pivot 9 39
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now