Compute age from Date of Birth while filling out form

[DAZarlengo]

I have a user table that has separate fields for "Date of Birth" and "Age". When filling out the customer data form, the user would like the age to be calculated automatically in real-time when the Date of Birth is entered. (Not later, such as through an update query).

How can this be done? I'm guessing that an "on focus" event in the age box could do it. That way, when the user tabs from Date of Birth into the age box, code could be executed. But what code? And how to put that age result into the age field in the customer table.

I'd appreciate any solution, and know how to use VBA code. Thanks!

 

[bat17]

The best way depends on what you are using the age for. If you want the age to be current when you look at the form then don't store it but calculate it on the fly. If you want to store the age so you know their age when the record was created you could still calculate it on the fly if the date that the record was created is stored.
The easiest way to store the age would be from the update event of the "Date of Birth" field as this will recalculate the age if the DOB is eddited. the code to do it would look something like :-

Private Sub Date_of_Birth_BeforeUpdate(Cancel As Integer)
If DATE >= DateSerial(Year(DATE), Month(Me![Date of Birth]), Day(Me![Date of Birth])) Then
    Me![Age] = DateDiff("yyyy", Me![Date of Birth], DATE)
Else
    Me![Age] = DateDiff("yyyy", Me![Date of Birth], DATE) - 1
End If
End Sub

HTH

Peter

 

[DAZarlengo]

Thanks for your help! I'll give this a try.