Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013

    Unanswered: OnChange insert Date

    Hi all

    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?


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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()
    will put the current system date in that control. If yoy need tge current system date AND time replace Date() with Now()

    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 data
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    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#;
    This query will return an empty data set, unless all email addresses where changed at midnight.

    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 Sub
    Where Text_Email_Address and Text_Date.Changed are the names of the controls bound to the columns [email address] and [date changed].
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts