Application Error: Undefined Function 'Right' in Expression (1 Viewer)

[jco23]

I've been developing apps in MS Access for a few years now, but have not come across this error message.

I took an app that I've been developing for quite some time, made some changes (nothing to the tools/references), performed a compact/repair, and saved the file to the network for a co-worker to review. Upon opening the app (which auto-launches a specific form as normal), they get greeted with the error message above. We reviewed the tools->references as researched, but nothing was "missing".
I believe the issue is stemming from the underlying query which has the 'right' function within it.
However, I did not make any changes to this query/form, and on the prior version of the app, the logic is exactly the same.
Even when they "shift" into the app, and open the form/query, they get the same error message.
The user looked into the VBA and found the 'Right' function defined.
Why would it work in the VBA but not in a query?
Is there some sort of other setting within MS Access that needs to be the same for both users?
It just seems to me that the text functions (right, left, mid, etc.) are standard for MS Access.

thanks!

 

[Ranman256]

This is a false error.
I get it too when a reference is wrong. (Access is confused w that reference)
If a known function gives an error, this is the problem.
The reference is not missing but something is wrong with it.
Sometimes removing it, then adding it back with the correct path helps.

If not reference problem, maybe a declare. Make sure all Declares use PTRSAFE for both OS's.

 

[jco23]

This is a false error.
I get it too when a reference is wrong. (Access is confused w that reference)
If a known function gives an error, this is the problem.
The reference is not missing but something is wrong with it.
Sometimes removing it, then adding it back with the correct path helps.

If not reference problem, maybe a declare. Make sure all Declares use PTRSAFE for both OS's.

i'll look, thx

 

[The_Doc_Man]

Part of the problem MAY be that in the list of references, if there is something shown above the VBA reference, that is the reference that may be in error.

It is not well appreciated, but the reference list is searched in the order that it was listed. Which oddly enough is why you can change the order of appearance of a reference with Up and Down arrow-buttons if you select a reference. If this turns out to be correct (i.e. previous reference is bollixed), then RIGHT was just the first external reference in your code AFTER the thing with the messed up reference.

To force the issue if you don't see a missing or broken reference, look up DECOMPILE and do that, then do a compile and see if something else pops up with an error.

 

[jco23]

Part of the problem MAY be that in the list of references, if there is something shown above the VBA reference, that is the reference that may be in error.

It is not well appreciated, but the reference list is searched in the order that it was listed. Which oddly enough is why you can change the order of appearance of a reference with Up and Down arrow-buttons if you select a reference. If this turns out to be correct (i.e. previous reference is bollixed), then RIGHT was just the first external reference in your code AFTER the thing with the messed up reference.

To force the issue if you don't see a missing or broken reference, look up DECOMPILE and do that, then do a compile and see if something else pops up with an error.

yeah, we reviewed the order of the references, and they are exactly the same. we tried replacing newer references with older ones, but to no avail.

 

[MajP]

Can you verify if this works in code on that machine? As everyone stated this is normally a broken reference in the "chain" and not necessarily a problem with the reference to where the code resides. I actually believe it goes through the references alphabetically. It is looking for the right function which resides in the VBA reference library which is alphabetically at the bottom of the list. So it searches through the libraries, but if it hits a broken reference it never gets to the VBA library containing the Right (or other string functions).

 

[jco23]

Can you verify if this works in code on that machine? As everyone stated this is normally a broken reference in the "chain" and not necessarily a problem with the reference to where the code resides. I actually believe it goes through the references alphabetically. It is looking for the right function which resides in the VBA reference library which is alphabetically at the bottom of the list. So it searches through the libraries, but if it hits a broken reference it never gets to the VBA library containing the Right (or other string functions).

i think we may be on to something.
we removed one reference, added in another, and then moved it as high as we could go (third from the top). then, we noticed on one of the modules that it read:
Option Explicit
Option Compare Database

when those two are normally reversed. after adjusting this order (and the order of the references), it worked!

thanks for your suggestions and help!

 

[MajP]

Forget what I said about alphabetically it has to go in the priority order. maybe that was in the old days.
The reason the priority is important is because you may have the same function or class in two different libraries.
One common one is DAO and ADO references which have changed over the years. They have many of the same classes.
So if you want a DAO recordset but the ADO reference is listed first it would pick the ADO reference unless you fully declared your variable. The code would error if you need a dao recordset and not an ADO recordset. But again as it moves through the library looking for the correct class or method if it hits an error in a broken reference (not your code) then it never gets to the function you are calling.
I doubt reversing the options had any impact. No requirement for them to be in any order.

 

[The_Doc_Man]

i think we may be on to something.
we removed one reference, added in another, and then moved it as high as we could go (third from the top). then, we noticed on one of the modules that it read:
Option Explicit
Option Compare Database

when those two are normally reversed. after adjusting this order (and the order of the references), it worked!

thanks for your suggestions and help!

This action probably didn't actually make a difference since both would be fully evaluated long before any executable code was seen. But what DID happen is that you changed the text of the module, thus forcing THAT module to be recompiled on its next execution attempt. Which may have thus forced a change to whatever was bollixed up. The DECOMPILE/re-COMPILE sequence would have done this wholesale, but you did it piecemeal - and hit the right module.

 

[jco23]

unchecking "Microsoft ActiveX Data Objects 6.1 Library" appears to be a more permanent solution.

hoping this doesn't break something else.