oskar509
asked on
Access vba form module won't use my global function
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 :-)
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 :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
From the screen shot, the module is called AgeInYears, as well as the function. Most likely that is the problem. Rename the module basUtilities, or some such, and see if the function works. Also, I would suggest using IsDate to check for valid dates in the controls, like this, since that is a more precise check than just checking that it is not null:
If IsDate(Me![txtStartDate].Value) = False Then
strTitle = "Invalid date"
strPrompt = "Please enter a valid start date"
GoTo ErrorHandlerExit
Else
dteStart = CDate(Me![txtStartDate].Value)
End If
post by mbizup< Make sure that your MODULE is not named "AgeInYears">
...However your screenshot clearly shows that the name of the module *is* AgeInYears
..so I am confused here...
In a general sense you should try not to duplicate any name of any object in Access...
...However your screenshot clearly shows that the name of the module *is* AgeInYears
..so I am confused here...
In a general sense you should try not to duplicate any name of any object in Access...
shouldn't mbizup have gotten some points here for pointing this out first...?
Okay... this issue is that somewhere, your function name is getting confused with your module name (the the same name is being used for both). If you have a form or other object named ageInYears, it might cause the same problem.
Have you tried both of these:
1. Try renaming your module to something different (it is showing as ageInYears in your image). This will not require any additiona code changes.
2. If that does not work, try renaming your ageInYears function to something different (functionAgeInYears, for example). If you do this, you will have to change calls to that function to reflect the new name.
3. When you placed the public function in your form's module did you comment out or otherwise remove the original, and vice versa? You should only have one copy of this function in your application at any time.
As an aside, if that function is in a regular module, you don't need the "Public" before the function name. Try removing it.
Have you tried both of these:
1. Try renaming your module to something different (it is showing as ageInYears in your image). This will not require any additiona code changes.
2. If that does not work, try renaming your ageInYears function to something different (functionAgeInYears, for example). If you do this, you will have to change calls to that function to reflect the new name.
3. When you placed the public function in your form's module did you comment out or otherwise remove the original, and vice versa? You should only have one copy of this function in your application at any time.
As an aside, if that function is in a regular module, you don't need the "Public" before the function name. Try removing it.
Thanks, Jeff.... :-)
ASKER
I've tried other naming things like:
- explicitly telling it where it is like database.AgeInYears
- I've named it "asparagus" so it's not interfering with some standard module I don't know about
The error message seems weird to me that it "expected variable or procedure, not module"
I dug deeper on this at one point and got a message that I, of course, didn't write down, but seemed to indicate that it was aware of the presence of a thing called "AgeInYears" and that it new it was a module, but that maybe it wasn't in the right place or something.
Here's where it lives:
I have some functions in excel like this that I can call from other modules, but now that I think about it, all of the calling modules are in the same folder "modules" with the functions. This may not be relevant.