Solved

Access vba form module won't use my global function

Posted on 2011-09-07
8
481 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
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

770 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