Date Update on Field Change

[Kristi]

Hi

I have a field called UpdatedDate that I would like to calculate when a change is made to my Age field. In other words, if I change the number in my Age field, I want the UpdatedDate field to display today's date. How would I go about making that work

Thank you!

 

[Tom Wickerath]

Hi Kristi,

First, I question why you are storing an Age value. This sounds like something that should be
calculated on-the-fly, based on a birth date and the computer's system date (which, of course,
must be accurate to calculate the correct ages). Storing the results of any calculation, such as
age, violates third normal form in database design. It requires that someone, like you,
periodically update the data in an attempt to prevent inaccurate data. That being said, you can
update your UpdatedDate field by using the Before_Update event procedure of textbox that your age
is displayed in. For the purposes of this example, the age field is displayed in the textbox
named txtAge and the UpdatedDate field is displayed in a textbox named txtUpdatedDate. It
underlying datatype Date/Time. Use the following VBA procedure, in the form's code module, to
accomplish your update:

Option Compare Database
Option Explicit

Private Sub txtAge_BeforeUpdate(Cancel As Integer)
Me.txtUpdatedDate.Value = Date
End Sub

If you want the date and time of day stored, then use the Now function in place of the Date
function above. Other suggestions include setting the locked property for txtUpdatedDate to Yes,
and changing the backcolor property to something different from the default white. A light grey
works nice (12632256). The change in backcolor provides your users with a visual clue that the
data cannot be edited. Finally, I would remove the tab stop from the txtUpdatedDate textbox,
which is found on the Other tab of the properties view.

Tom
_____________________________________


Hi,

I have a field called UpdatedDate that I would like to calculate when a change is made to my Age
field. In other words, if I change the number in my Age field, I want the UpdatedDate field to
display today's date. How would I go about making that work?

Thank you!

 

[Kristi]

Thank you, Tom, that worked. By the way, regarding your suggestions on my Age field -- you will laugh when you realize how simple my database is. I am just creating a tiny personal database to log my list of potential babysitters. So I am typing in their age for my reference. I wanted the date field to update so that I will know how long ago I typed in that a certain babysitter was, for example, 15 years old (because it could be that I don't use her for a year and then she might be 16, etc.). I can totally see how your suggestions would have applied under more typical circumstances.

 

[John Vinson]

Hi,

I have a field called UpdatedDate that I would like to calculate when a change is made to my Age field. In other words, if I change the number in my Age field, I want the UpdatedDate field to display today's date. How would I go about making that work?

Thank you!

You'll need to do all your updating using a Form (invoking Access
security if need be to keep users out of the table or query
datasheets, since these have no usable events). In the AfterUpdate
event of the textbox containing Age, use code like

Private Sub txtAge_AfterUpdate()
Me!txtUpdatedDate = Date
End Sub

One question - is Age actually a person's age? If so, you might want
to consider storing the date of birth and calculating the age on the
fly in a query, so that you don't need to edit the age!

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

will always show the person's current age.