'Function not available in Expression' Error in Queries

[cwhite23]

Ran into this just a few minutes ago and thought someone out there could use it when they run into this error.

I am in the process of moving a large database from my old system to my new one. The file moved just fine, but all of a sudden, I couldnt run any of my reports that used the Date() function. I finally tracked it down to a missing library module. The strange thing is...the module was for web components. I re-installed the reference to this module, and suddenly everything worked! The only thing that I can figure is that the system saw that this reference was missing, and simply didn't know how to proceed. If anybody has a better explanation, I'd love to hear it!

 

[ZmrAbdulla]

I had a similar one long ago.. finally found the date format set in "regional options" was different in the new machine.


________________________________________________________
Zameer Abdulla
Help to find Missing people

 

[jsteph]

I've found that if *any* reference is missing, you can get this error in the first line of code (or first compile) that runs and uses any vba function. It won't necessarily error-out on the line or function where the .dll is missing--but usually on the first function called or that the compiler comes across.
--Jim

 

[JoeAtWork]

One of Access's weaknesses is that if a library reference is missing, you will tend to get these type of errors that have nothing to do with the real problem.

 

[cwhite23]

Aggrivating to say the least! I would get this error even when running a simple select query! Leave it to Microsoft...

 

[jrbarnett]

The next time one of you comes up with one of these, can you open the immediate window, then run

? currentproject.Application.References.Count

This will show the number of references in the library

Then loop through each of them in turn from 1 to the number returned :

? Currentproject.Application.References.Item.Name
will give you the name of the reference
? Currentproject.Application.References.Item.IsBroken
will return a true/false value as to whether the reference is broken.

The question is, if there is a broken reference, would a loop going through that to look for broken references actually run?...

I haven't really got time at the moment to deliberately break an app to test this out, but if anybody else does, feel free to use the details above to figure out if this can be used to display an appropriate message at application startup.

John

 

[cwhite23]

It works! I've set up a module to run at application startup which goes through and checks the references as outlined above. Having intentionally 'broken' one of the references, I re-started the app and Boom! It worked! 5 stars, John! From now on, this mod will be incorporated into every app I create.

 

[jsteph]

jrbarnett,
There was some code, I believe posted here somewhere, that attempted just that--looping through references and trying to fix them.

I deliberately broke references in order to try to get this thing to work, but there's an inherent catch-22. I was difficult to get it fix the references reliably first time, every time--if the code itself that was to fix the reference was bombing because the referece it was trying to fix was broke...

There are all sorts of ways to work around it but they all seemed like too much. If it was a simple, unsecured db, it was easier to post a how-to on our intranet and have the user go to tools/references and fix the damn thing himself.
--Jim

 

[jrbarnett]

cwhite - Good that it worked for you. However, jsteph has found the problem: what if the code that is needed for the error checking to run is one of the referencea that has broken.
My idea was to do a "Prevention is better than cure" option by looping through the references and capture a list of all the broken ones; then display this onscreen to the user with a "Please log a call with <IT helpdesk contact information> and give this information."

John

 

[cwhite23]

A 'Catch-22', I believe. In other words, if the link was broken, you couldn't run the code to find the broken link.

But if the code was set up to run at start-up, wouldn't this keep the app from running in the first place? At which point any messages would never show, and would therefore be academic.

Anyone have any ideas on a workaround?

 

[jrbarnett]

Update:

My code sample above will not work in Access 97; I've just found a way of doing the same thing that works in 97. I haven't got any earlier versions to see if this still works before that.

If instead of using

CurrentProject.Application.References

you just use

Application.References

then it will work back to 97 format.

The issue of being able to fix a problem relating to the code needs to run the database still exists, but I don't really think we'll find a way of doing this as its the same chicken/egg and catch 22 situations as mentioned earlier.

John