Unknown Function Name when calling VBA function from a query

[valentine]

This is really strange...

I'm currently unable to call any custom VBA functions
from access queries. The message it comes up with
is "Unknown function name". There are no missing
references (kb275110) and it even happens in a brand new
database.

The VBA function definitely exists and I can browse for
it using "Build" tool.. If I copy my database onto
another computer it works perfectly. In built functions
like Format etc. work fine.

This is using Access XP on Win XP Professional box (SP
1). I have uninstalled and reinstalled Office XP - didn't
help. In-place upgrade of Win XP didn't help. Latest
Office Service pack didn't help.

I'm seriously lost now... If anyone has an idea of why
this is happening it would be greatly appreciated.

Thank you

 

[Susan]

I am also unable to use my custom VBA function. I created
a module with a function to determine the date of 2 weeks
from this Monday. I tried the function in the Immediate
Window, and it worked. If I try to enter the function
name, =GetMonDate() in the DefaultValue of the Payment
Date field of my table, I get the same Unkown Function
Name error you are getting. I am running Access 2000 on
Windows 2000. If anyone can help, it would be great. This
is driving me crazy.

 

[Allen Browne]

From a code window, choose Compile on the Debug menu. Any errors?

Does Access give you the name of the unknown function?

Does the function have the keyword "Private" before it?

Is the function in a standard module?
If it's in the module of a form, you would need a fully qualified reference,
e.g.:
Call Form_MyForm.MyFunction()

Does a compact fix the problem? (Tools | Database Utilities)

Could there be a naming clash between your function and something else in
the database?

If you create a new (blank) database and import the module, does it work
correctly there?
If yes, try importing all the other objects into this new "rescued" database
also.
If no, search your hard drive for duplicates of the library files you have
referenced. For the name and location of the libraries, open the Immediate
windows (ctrl+G) and enter:
? Application.References(1).FullPath
then 2, 3, etc.

 

[valentine]

I have two msado21.tlb on my machine.

C:\WINDOWS\ServicePackFiles\i386\msado21.tlb (version
2.71.9030.0)
C:\Program Files\Common Files\System\ado\msado21.tlb
(version 2.71.9030.0)

Not sure if i should un-register one or something. One
that's registered is C:\Program Files\Common
Files\System\ado\msado21.tlb i think...

As for the other options I have tried them all before
posting this...

Thank you ever so much Allen

 

[Allen Browne]

The copy in the service packs folder is not a problem.

You did not mention the name of the function Access says is an unknown
function.

And you did not say if a new blank database exhibits the same behaviour.

 

[Van T. Dinh]

IIRC, you can only use inbuilt functions in the Default Value of the Field
in the Table Design and NOT custom functions since JET Database Engine
doesn't know about your custom functions.

 

[valentine]

Thank you for your reply John. I have read Doug Steel's
article before and : There are no <MISSING> references. I
have removed and re-added as many Access references as I
could (some are in-use). I have even re-registered the
referenced libraries. No luck there, unfortunately.

Yes the problem does happen in a blank new database. No
custom functions are working e.g.

In standard module:

Function MyCustomFunction() as String
MyCustomFunction = "blah"
End Function

In the query:
Select field1 from table1 where field1 = MyCustomFunction
()

On my machine it goes "Unknown fuction 'MyCustomFunction'"

Any other machine works perfectly...

Looks like this is calling for a machine rebuild...

Thank you for your replies