RE: Built in function errors.

Tech-Archive recommends: Fix windows errors by optimizing your registry

I have yet to work with 2007 and certainly not in a mixed environment.

I would suggest you repost your question as
Access Function Errors on Multiple Version computer

That way, someone with that specific experience can offer better answers.

I do remember reading somewhere that 2003 should be installed first, then
2007, but I have no way of knowing if it really makes a difference.

--
Dave Hargis, Microsoft Access MVP


"RobGMiller" wrote:

It’s hopeful to know that not everyone is having the same issues. I'm sure
there is something unique about the applications I am working on or the
environment I am developing on.

I have produced test databases using the same intrinsic functions in the
same way that do work on environments as MDB or MDE where the application
does not work. Yet, when those functions are removed or other functions are
used on the main application, there is no problem.

For instance, I've replaced UCase intrinsic function located in the Control
Source of a control as in =Ucase([control]) with a User defined function that
uses the same UCase intrinsic function and that seems to work. The test
database had no problem with =UCase([Control]).

I believe I did not start getting these types of problems until I began to
develop 2003 applications on an XPPro environment that has Office
2007(including Access 2007) installed. I develop the applications with Access
2003 to run on Access 2003 full or runtime environments generally. Of course,
that development environment change could be coincidental with regular
updates that most computer users are subjected to.

I've looked at many different helpful forums that suggest many different
ways of trying to match all the libraries but so far nothing has worked 100%.
Allan Brown’s article suggests that the objects for the Intrinsic functions
and control source issues encountered appear to be located in VBE6.DLL and
MSACC.OLB which are referenced libraries but cannot be unreferenced from
VBEditor because it holds the files open. It’s a bit confusing when he
states at the beginning that “Installing or uninstalling any software may
overwrite, remove, or de-register libraries. Then simple functions like
Date() or Trim() don't work.”

Does this mean that unregistering a library can screw up a different
library? If one application can handle the trim() function does that not
indicate that VBE6.DLL and MSACC.OLB are correctly registered?

In most case VBE6.DLL and MSACC.OLB are the same version as my development
environment.

In many cases, the application works even though VBE6.DLL and MSACC.OLB are
different.

When they were different and the app did not work, I've tried to change and
re-register VBE6.DLL and MSACC.OLB on the client computer to match the ones
on the development computer, even when they were newer versions, and that has
never helped.

It is probably that these issues are not related to references.

As you say, your applications work without having to screw with references
on client computers. I have to conclude that mine should as well.

--
RobGMiller


"Klatuu" wrote:

What concerns me, Rob, is the issues you are describing are very unusual and
you should not be having the problems to the extent you are having them. I
have never had a problem using any intrinsic functions, including Date()
either in a Control Source or in a Jet query.

I still think, based on your descriptions, that you may have library
reference problems. Here are a couple of links you might study to help you
resolve the issues:

http://allenbrowne.com/ser-38.html

http://support.microsoft.com/default.aspx?scid=kb;en-us;283115

Although the second link refers to AC2002, it is valid for 2003 as well.

To be specific, I have used all the functions you asked about in queries at
one time or another in Jet queries for years without a problem.

What windows versions are working or not working?
What version of Access are you using?
--
Dave Hargis, Microsoft Access MVP


"RobGMiller" wrote:

Thanks for the response Dave,

I did not intend to bash access. I'm just stating my experience with it as
clearly as I can.

As I stated earlier, I have found ways around all these issues but I would
like to find solutions that are less time consuming than what I've come up
with on my own.

I dont want to say too much more about access for fear that it might be
considered bashing but is it not fair to conclude that because all three
problematic techniques that I've isolated so far do work on many different
environments but not on all of them indicate that the library situation is
problematic.

The example was meant to be an indication of the types of things I am
dealing with and not the exact code in use. I believe that the syntax for the
Control reference (please forgive the typo and the missing .Form specifyer),
although quite complex because of the number of subforms and tabs, is correct
and does work correctly on many operating systems.

The exact syntax in use is in fact:
UserDefinedFunction([Forms]![MainForm]![SubFormContainer].Form![OtherSubFormContainer].[Form]![TabName].[Pages]("PageName").[Controls]("SubFormContainer").[Form]![ControlName])

To be honest, this is the first time I've used such a complex reference and
for all I know there is a limit to lenght or something. However, it does work
in most cases so I'm assuming it is ok.

The essential challenge here is why doesn't it work on all operating systems
and what type of techniques are more likely to work on more different
versions of windows and Office operating systems without having to update the
software on all client computers.

In fact, updating the software on the client computer and reselecting all
the references does not always resolve the issues.

Do you think there something inherently wrong with using =Date() in the
default property of a text box control? Or using the Format or Mid function
in an Access Query?


--
RobGMiller


"Klatuu" wrote:

Some of what you are saying regarding setting references may be correct. The
issue can also be created by the user's computer not having all the library
references installed; however, the large number of problems you are
experiecing leads me to suspect your object referencing is not correctly
qualified. The number of problems you are having is too unusual. It is more
likely you than Access.

As to yor invalid dot operator, I think you have a syntax/reference problem:


=CustomFunction-DevideByTwo([Forms]![MainForm]![SubForm]![OtherSubFormContrainer].[Form]![Tab].[Pages]("PageName").[Controls]("SubFormContainer").[Form]![ControlName])

appears to be incorreclty formed. When you are referencing a control on a
subform, the correct form is:
[Forms]![MainFormName]![SubformControlName].[Form]![ControlName]

SubformControlName is not the name of any form. It is the name of the
subform control on the main form. [Form] references the Form property of the
subform's property collection. The name of the form being used as a subform
is the subform control's Source Object property.

What is this part?

CustomFunction-DevideByTwo

It appears to be a User Defined Function.

This part should be removed:
![SubForm]

Check the spelling on this:
[OtherSubFormContrainer]
Is it Contrainer or should it be Container?

Is Tab the name of the Tab control? If so, you shouldn't use it as a name.

([Forms]![MainForm]![SubForm]![OtherSubFormContrainer].[Form]![Tab].[Pages]("PageName").[Controls]

I suggest you get your object references and syntax working before you start
bashing Access.
--
Dave Hargis, Microsoft Access MVP


"RobGMiller" wrote:

-Access 2003:
-Running an MDE on many different client computers

Errors Encountered:

"There is an invalid use of the .(dot) or ! operator or invalid parenthesis"
"Error 3075: Function is not available in expressions in query expression"


I've seen many posts on this issue but nothing has worked for me yet in all
circumstances. Both error messages relate to library problems. Essentially,
you need to remove and reselect refrences from an application database VB
editor to allow the application to connect with the current DLLs, OCXs and
TLBs on the client computer. There are alos many suggestions about trying to
ensure that all referenced library files are the same version.

My experience with this technique has been dismal to say the least. The idea
of running an MDB copy of an MDE application on every client computer to
reselect libraries simply makes the application not worth building using
Access.

In any case, it doesn't always work. I have replaced every library file with
the equivalent of what is on a computer where the application works. By the
way, in some cases this is impossible to do and in some cases they just get
overwritten with the original somehow. But even if it can be done in some
cases the referencing of the new files still doesn't help because the
functions that are in use are not located in the libraries that are
referenced apparently.

I've had instances where I can run the exact same code in a test database as
an MDB or MDB but cannot run the original application with exacly the same
reference setup after resetting the references on the same computer.

I've managed to resolve some of the issues by three general methods.
1 - Do not use builtin functions such as Instr, Left, Right, Ucase and
Format out of access Queries
2 - Do not reference controls to use a value in automatic calculations on
unbound controls.
3 - Do not use builtin functions like Date() and Now() in the default value
of controls.

In all three cases, errors occur some times but not all the time so it seems
to be better to stay away form these techniques to get the job done all the
time.

In the case of number 2 there is no easy way around it. Errors seem to occur
more frequently where tabs are involved. You simply have to use other more
complex ways of getting the result of a calculation in real time.

For instance: If you have a control on one tab showing the result of two
controls on a differnet tab in the same form.

controlx.ControlSource:

=CustomFunction-DevideByTwo([Forms]![MainForm]![SubForm]![OtherSubFormContrainer].[Form]![Tab].[Pages]("PageName").[Controls]("SubFormContainer").[Form]![ControlName])

This will result in "There is an invalid use of the .(dot) or ! operator or
invalid parenthesis". Not all the time but lots of the time.

The way around this issue is to send the DvideByTwo function, information
that allows you to lookup the value of the referenced control, perform the
calculation and return the result.

I am looking for more information on these issues generally to see if we can
categorize or define categorically the types of techniques to stay away from
or devevelop a general approach that will work well in all possible
instances.

Also, I'd like to pinpoint exactly which libraries are responsible for these
types of errors. I get the feeling that they are not Office library files but
probably Windows.

Are there libraries that are used by Access and come with Access but do not
need to be referenced specifically. In that case, if you are developing on a
machine that has Access 2007 installed, using these later versions of
libraries, the application migth not run on a machine which only has Access
2003 installed even thought the libraries are not referenced specifially.


--
RobGMiller
.

Relevant Pages

  • RE: Built in function errors.
    ... I've replaced UCase intrinsic function located in the Control ... ways of trying to match all the libraries but so far nothing has worked 100%. ... It is probably that these issues are not related to references. ... updating the software on the client computer and reselecting all ...
    (microsoft.public.access.modulesdaovba)
  • RE: Built in function errors.
    ... Control reference, ... updating the software on the client computer and reselecting all ... the references does not always resolve the issues. ... reselect libraries simply makes the application not worth building using ...
    (microsoft.public.access.modulesdaovba)
  • Re: Built in function errors.
    ... Source of a control as in =Ucasewith a User defined function ... MSACC.OLB which are referenced libraries but cannot be unreferenced from ... It is probably that these issues are not related to references. ... updating the software on the client computer and reselecting ...
    (microsoft.public.access.modulesdaovba)
  • Re: YASID: Gargoyles, but no rats
    ... There were some references to, I think, gargoyles that had ... libraries in town. ... or in the agents of a God. ...
    (rec.arts.sf.written)
  • Re: cycle through a table
    ... Doug Steele, Microsoft Access MVP ... "Phil" wrote in message ... >> program and object libraries that provide functionality that may be ... References to the specific library files, ...
    (microsoft.public.access.modulesdaovba)