Solved

Access vba form module won't use my global function

Posted on 2011-09-07
8
477 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
8 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 50 total points
Comment Utility
Make sure that your MODULE is not named "AgeInYears"

Call it something like modAgeInYears
0
 

Author Comment

by:oskar509
Comment Utility
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
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
shouldn't mbizup have gotten some points here for pointing this out first...?
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
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
Comment Utility
Thanks, Jeff....  :-)
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now