By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,677 Members | 1,890 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,677 IT Pros & Developers. It's quick & easy.

User-Defined Functions in SELECT Statements

P: n/a
Access 97

I have a number of SELECT statements which contain references to
user-defined VBA functions. These typically work as expected, but
occasionally, on one user's machine or another, produce a "function not
defined" message when the SELECT statement is executed, even having
previously worked on that machine.

What can I do to correct this difficulty when it occurs? What do I need
to know about user-defined functions in SELECT statements?

TIA,
-Dave

--
"Sometimes what seems to be enough smoke to guarantee a robust
fire is actually just a cloud of dust from a passing bandwagon."
- Daniel Dennett
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi, Dave.

Pretty frustrating, huh? Sometimes Jet can find that user-defined function
and sometimes it just can't, even though you've defined it publicly in a
module. The reason it fails is that your user-defined function is not
already loaded into memory when the query runs, and Jet 3.5 isn't going to
go hunt for it beyond the libraries contained in the References Collection.

When you call a procedure that is defined in a module, all of the procedures
in that module are loaded into memory, not just that single procedure. On
the occasions that you didn't get the "Function not defined" error message
when you ran the query, your user-defined function was already loaded into
memory, due to the coincidence that some other procedure in that same module
had already been called.

To prevent this from happening in the future, remove the "coincidence"
factor before you run your query by explicitly calling one of the procedures
in the same module where the user-defined function is stored. You can even
create a "dummy" procedure in this same module that does nothing, except
have its name called just before the query runs so that all procedures in
that module are loaded into memory beforehand.

I suppose that you could also place these user-defined functions in a
library MDE file and reference the library, but that may be overkill for
your application.

HTH.
Gunny

Coming soon:
For your Microsoft Access, database development and maintenance needs, see:
http://www.softomagixly.com
"David Emme" <demme@ZERO_SPAMpobox.com> wrote in message
news:c2**********@216.39.176.203...
Access 97

I have a number of SELECT statements which contain references to
user-defined VBA functions. These typically work as expected, but
occasionally, on one user's machine or another, produce a "function not
defined" message when the SELECT statement is executed, even having
previously worked on that machine.

What can I do to correct this difficulty when it occurs? What do I need
to know about user-defined functions in SELECT statements?

TIA,
-Dave

--
"Sometimes what seems to be enough smoke to guarantee a robust
fire is actually just a cloud of dust from a passing bandwagon."
- Daniel Dennett

Nov 12 '05 #2

P: n/a
Also go to Tools->Options on the flip Module. Read the help text for the
check box: Compile on demand. I am not sure I understand it fully, but I
think the best thing is to have this box unchecked.

Brdgds
Rolfern

"'69 Camaro" <Ze*******@ZeroSpam.com> wrote in message
news:c2********@library1.airnews.net...
Hi, Dave.

Pretty frustrating, huh? Sometimes Jet can find that user-defined function and sometimes it just can't, even though you've defined it publicly in a
module. The reason it fails is that your user-defined function is not
already loaded into memory when the query runs, and Jet 3.5 isn't going to
go hunt for it beyond the libraries contained in the References Collection.
When you call a procedure that is defined in a module, all of the procedures in that module are loaded into memory, not just that single procedure. On
the occasions that you didn't get the "Function not defined" error message
when you ran the query, your user-defined function was already loaded into
memory, due to the coincidence that some other procedure in that same module had already been called.

To prevent this from happening in the future, remove the "coincidence"
factor before you run your query by explicitly calling one of the procedures in the same module where the user-defined function is stored. You can even create a "dummy" procedure in this same module that does nothing, except
have its name called just before the query runs so that all procedures in
that module are loaded into memory beforehand.

I suppose that you could also place these user-defined functions in a
library MDE file and reference the library, but that may be overkill for
your application.

HTH.
Gunny

Coming soon:
For your Microsoft Access, database development and maintenance needs, see: http://www.softomagixly.com
"David Emme" <demme@ZERO_SPAMpobox.com> wrote in message
news:c2**********@216.39.176.203...
Access 97

I have a number of SELECT statements which contain references to
user-defined VBA functions. These typically work as expected, but
occasionally, on one user's machine or another, produce a "function not
defined" message when the SELECT statement is executed, even having
previously worked on that machine.

What can I do to correct this difficulty when it occurs? What do I need
to know about user-defined functions in SELECT statements?

TIA,
-Dave

--
"Sometimes what seems to be enough smoke to guarantee a robust
fire is actually just a cloud of dust from a passing bandwagon."
- Daniel Dennett


Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.