VBA Functions and Modules don't work in queries

[Pongetti]

Hello,

I have one user having a problem with access. Many functions don't seem to
work on his computer. I have about 100 users and I have gotten no complaints
from other people.

(BTW, I posted about this before, sort of, but the problem's a bit different
than I thought it was back then. Maybe somebody can find a solution with
these new clues)

When he goes to some forms and reports they give him errors, I've spent a
while trying to figure out what's causing the problems. I can't figure out
what's causing it. Anyways, I have noticed this behaviour:

-DLookup, DFirst, etc. will give me the error "unknown function name" when
called from a query. They work fine if called from the module of a form.

-If I call DLookup, DFirst, etc. from a query and use an incorrect number of
arguments, it will tell me that I don't have the correct number of arguments
rather than telling me it's an unknown function name, so it knows the
definition in some way, it just can't find them for actual use.

-If I have a public function that is in a Module and I call it from a query,
it will also give me the error "unknown function name". Like DLookup, they
work fine when called from forms and other modules, also. However, if I use
the incorrect number of arguments when I call it from a query, it will still
give me "unknown function name", unlike the VBA functions where it told me I
had an incorrect number of arguments.

-The functions format() and nz() work in queries, according to msdn these
are from VBA, just like DLookup. Now() works also.

Now, I know you're all thinking, check your references! But I already have.
There are no references missing. I have also tried the trick of unchecking
all references possible, backing out, and reselecting them. I have also
tried unselecting as many as I can, backing out, and using it. Still the
same problem.

It is not a security issue either. I have tried using my username and it
still doesn't work.

I even tried re-installing office. Still doesn't work.
I downloaded vbe6.dll and replaced it. Still doesn't work.

The computer has Access 2002 like every other person in the building and is
using Windows 2000 like a lot of others.

Any ideas?

 

[Rick Brandt]

Hello,

I have one user having a problem with access. Many functions don't
seem to work on his computer. I have about 100 users and I have
gotten no complaints from other people.

(BTW, I posted about this before, sort of, but the problem's a bit
different than I thought it was back then. Maybe somebody can find a
solution with these new clues)

When he goes to some forms and reports they give him errors, I've
spent a while trying to figure out what's causing the problems. I
can't figure out what's causing it. Anyways, I have noticed this
behaviour:

-DLookup, DFirst, etc. will give me the error "unknown function name"
when called from a query. They work fine if called from the module
of a form.

-If I call DLookup, DFirst, etc. from a query and use an incorrect
number of arguments, it will tell me that I don't have the correct
number of arguments rather than telling me it's an unknown function
name, so it knows the definition in some way, it just can't find them
for actual use.

-If I have a public function that is in a Module and I call it from a
query, it will also give me the error "unknown function name". Like
DLookup, they work fine when called from forms and other modules,
also. However, if I use the incorrect number of arguments when I
call it from a query, it will still give me "unknown function name",
unlike the VBA functions where it told me I had an incorrect number
of arguments.

-The functions format() and nz() work in queries, according to msdn
these are from VBA, just like DLookup. Now() works also.

Now, I know you're all thinking, check your references! But I
already have. There are no references missing. I have also tried the
trick of unchecking all references possible, backing out, and
reselecting them. I have also tried unselecting as many as I can,
backing out, and using it. Still the same problem.

It is not a security issue either. I have tried using my username
and it still doesn't work.

I even tried re-installing office. Still doesn't work.
I downloaded vbe6.dll and replaced it. Still doesn't work.

The computer has Access 2002 like every other person in the building
and is using Windows 2000 like a lot of others.

Any ideas?

On his computer open Access and create a brand new file. In that file create a
table and then a query that uses one of the functions in question. If it works
okay in that file then there is *something* about your file that is broken.
Either references or corruption. If it's references it could be that one of the
required libraries is broken on that PC and the fault does not lie in your file
per-se. If you have any non-default libraries then those would be the ones to
add one at a time to the test file to see if the same errors start occurring.

 

[Pongetti]

On his computer open Access and create a brand new file. In that file create a
table and then a query that uses one of the functions in question. If it works
okay in that file then there is *something* about your file that is broken.
Either references or corruption. If it's references it could be that one of the
required libraries is broken on that PC and the fault does not lie in your file
per-se. If you have any non-default libraries then those would be the ones to
add one at a time to the test file to see if the same errors start occurring.

Thanks for the reply, I had tried creating new databases from scratch in
attempting to diagnose the problem, I had tried converting the same file to
and from different versions of Access, compact and repairs, etc. They still
have the same problems.
I'm pretty sure this is related to his computer and not the file itself, and
this file is on the network, it's the exact same file as everybody else uses.
(I know, I know, this is often considered bad practice, but it wouldn't
create this problem)

I think this problem has probably existed for a while, but it simply never
came up before as this user only uses Access for his timesheets, and the
simplicity of the database never called for any of these functions. This all
started happening when I had to make a modification that made the timesheet
database a little more complex than it was and I had to start using some
functions.

I am not using any extra libraries. I unchecked all that I could and was
left with two, VBA and another pretty standard one (I'm not at the office and
can't remember what it was). Maybe I can get the IT guys to re-install the
libraries tommorow, but I would have expected the re-install of office to fix
those.

 

[aaron.kempf]

the dude probably has a different security setting under tools, macros,
security






P

 

[Rli]

Just an idea...
did you try to change the macro security on the users machine?
<options><Macro><security> set to low...

 

[Rick Brandt]

Thanks for the reply, I had tried creating new databases from scratch
in attempting to diagnose the problem, I had tried converting the
same file to and from different versions of Access, compact and
repairs, etc. They still have the same problems. [snip]

Well, if a brand new file created on that machine has the same issues then
clearly the installation of Office on that machine is buggered up. I would
start with a re-install of that.

 

[aaron.kempf]

why don't you just start with a re-install of the service pack?

everyone has the latest office service-pack right?

http://officeupdate.microsoft.com

Thanks for the reply, I had tried creating new databases from scratch
in attempting to diagnose the problem, I had tried converting the
same file to and from different versions of Access, compact and
repairs, etc. They still have the same problems. [snip]

Well, if a brand new file created on that machine has the same issues then
clearly the installation of Office on that machine is buggered up. I would
start with a re-install of that.