Assignin dynamic value to a text field via VBA

[Levi92]

Hello!

I am currently building a form in Access which includes a text field "Age" which, based on the date of birth, calculates the correct age. Unfortunately the DateDiff function does not work 100% of the time, which is


why I cannot rely on using the Expression Builder via theControl Source for the "Age" text field (wish Access had Excel's Yearfrac function...)

Anyway, so I wrote the code that accurately calculates the age:

Private Sub txtDayofBirth_Click()

Dim Birthdate As Date
Dim Age As Byte


Birthdate = [Day of Birth]


Age = DateDiff("yyyy", Birthdate, Date)


If Month(Birthdate) >= Month(Date) Then
    
    If Month(Birthdate) > Month(Date) Then Age = Age - 1
    If Month(Birthdate) = Month(Date) Then
        
       If Day(Birthdate) > Day(Date) Then Age = Age - 1
    
    End If
End If


Me.txtAge.Value = Age


End Sub

The issue I encounter is that, when I trigger this event, the age of the currently selected person is assigned to all other employees in my list. That is probably due to the code I highlighted red.

Is there any way I can I can solve this?

Edit:

Here is a picture of a sample table illustrating the issue:

[pbaldy]

Create a public function in a standard module that takes the birth date as an input and returns age. Then have this as the control source of the textbox

=FunctionName([day of birth])

if needed:

http://www.baldyweb.com/Function.htm

[Levi92]

Thanks very much! Works like a charm. This is also the very first time I created a Function. Always wondered what the difference was between a Sub and a Function, I think I am getting an idea now. Thanks!

[Levi92]

Ok, there is one minor problem that I can't seem to solve. I get a "#Type!" error if I leave the "Day of Birth" field empty. This error also shows up in the row below the latest record (see picture).

Here is my code:

Function ReturnAge(Birthdate As Date) As Byte

Birthdate = [Form_Employees].txtDayofBirth.Value


If IsEmpty(Birthdate) Then ReturnAge = "": Exit Function


ReturnAge = DateDiff("yyyy", Birthdate, Date)


If Month(Birthdate) >= Month(Date) Then
    
    If Month(Birthdate) > Month(Date) Then ReturnAge = ReturnAge - 1
    If Month(Birthdate) = Month(Date) Then
        
       If Day(Birthdate) > Day(Date) Then ReturnAge = ReturnAge - 1
    
    End If
End If


End Function

What do I have to change?

[pbaldy]

Declare the input variable as Variant rather than Date, and test for it being Null instead of empty. More here on sub vs function if you're interested.

http://www.baldyweb.com/SubFunction.htm

[Levi92]

Works like a charm! Thank you!

[pbaldy]

Happy to help!