troubleshooting Question

Access vba form module won't use my global function

Avatar of oskar509
oskar509 asked on
Microsoft AccessWindows Server 2008
7 Comments1 Solution514 ViewsLast Modified:
Hello,

I'm using Access 2007 (.accdb) to create a front-end to my SQL Server 2008 database. I've got a vba module in a form that needs to calculate a person's age based on the data entered on the form. I have what I think is a global function for calculating the age - it lives under the modules section under that database, and it's declared as a Public Function. But when the function gets called from the form's module, it gives me this error:
"Expected variable or procedure, not module"

If I put the function in the form's code - still as a separate function from the sub, rather than just the code in the sub, if you know what I mean - it works just fine. But it's such a basic function that I will probably need to call from several different places, I really would like to getting it working as a global function.

Here's the function:

Public Function AgeInYears(startDate As Date, endDate As Date) As Integer

    AgeInYears = DateDiff("yyyy", startDate, endDate)
        If DatePart("y", startDate) >= DatePart("y", endDate) Then
            AgeInYears = AgeInYears - 1
        End If
   
End Function


And then the sub attached to the form:

Private Sub CollectionDate_AfterUpdate()

    Dim calculatedAge As Integer
   
    If Not IsNull(Me.CollectionDate) And Not IsNull(Me.DOB) And Not IsNull(Me.CollectionAge) Then
        calculatedAge = AgeInYears(Me.DOB, Me.CollectionDate)
        If calculatedAge <> Me.CollectionAge Then
            MsgBox ("Collection Age is " & Me.CollectionAge & "," & vbCrLf & "But calculatedAge is " & calculatedAge & "!")
         End If
    End If
   
End Sub


Thanks in advance,
oskar :-)

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 7 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros