Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access vba form module won't use my global function

Posted on 2011-09-07
8
Medium Priority
?
487 Views
Last Modified: 2012-05-12
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 :-)

0
Comment
Question by:oskar509
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 200 total points
ID: 36496530
Make sure that your MODULE is not named "AgeInYears"

Call it something like modAgeInYears
0
 

Author Comment

by:oskar509
ID: 36496818
ok. I tried that, but I still get the same error message.

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: db tree

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.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36496863
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

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36496885
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...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36496941
shouldn't mbizup have gotten some points here for pointing this out first...?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36496949
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36496963
Thanks, Jeff....  :-)
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question