User-Defined Functions in SELECT Statements

[David Emme]

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

['69 Camaro]

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

[R.A.Balder]

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