Thread: OnChange insert Date
11-12-14, 19:49 #1Registered User
- Join Date
- Feb 2013
Unanswered: OnChange insert Date
Just a little help please...
I have created a very simple database to capture marketing data.
I have a form which is linked to a table. On the form are fields such as, company name, contact name and Email address. The Email Address field is always blank and needs to be populated when we identify what it is.
Another field is [Date Changed], which its default is also blank (format date/time).
Could you please advise what code I should add to the Event/OnChange to the [Email Address] field so that when the field is changed, the Now() data will be inserted in the [Date Changed] field?
11-13-14, 02:46 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
its down to putting some code that sets a value in one if the events provided as part of the form.
The actual code is pretty trivial. Lets say your column is called DateEmailSet, and tge control in the form is tbEmailChanged, then
tbEmailChanged = date()
The most obvious events would be the email control. And probably the CONTROL's after update event. However that event fires after the value in the control is changed, but before the change is committed to the db.
However im guessing you only want to set this when the value changes and is commited to the db. So the test should be if the value has changed and then new email is to be saved then record the date.
So id want to know what the old value was. There is a property whise name I forget that gas this. Or handke this yourself. To do that
declare a form variable, eg initialemailid
in the forms on current event set that form variable to the current email id
In the forms before update event (which fires immediately before the row is updated) test if tge email valye has changed, and if so set the date.
This approach will meet the requirement that the date only gets set when the email address actually changes in the table, not if the user just makes a temporary change to the form dataI'd rather be riding on the Tiger 800 or the Norton
11-13-14, 02:50 #3Moderator
Provided Answers: 14
- Join Date
- Mar 2009
First consider using the Date function instead of the Now function. If you use Now, the time (i.e. Hours, Minutes and seconds) will be stored with the date. This can become a problem if you intend to use queries having the concerned Date/Time column in the criteria (i.e. the WHERE... clause). e.g.
SELECT * FROM MyTable WHERE [date changed] = #10/31/2014#;
If the [date changed] value must only be changed when the [email address] value is updated, you could use:
Private Sub Email_Address_AfterUpdate() Me.Date.Changed_Value = Date End SubHave a nice day!