Built in function errors.

[RobGMiller]

-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.

 

[Klatuu]

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

-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]

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

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

-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.

 

[Klatuu]

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

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

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

-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]

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

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

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

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


:

-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.

 

[Klatuu]

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

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

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

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


:

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


:

-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.

 

[George Nicholson]

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?

Whenever you get a message telling you there's a problem with a function in
the VBA library, you need to look beyond the VBA library to find the problem
because that error message is misleading.

When the VBE tells you it has a problem with Trim(), UCase(), Right(),
Left(), Date() or any of the other built-in VBA functions then its really
telling you there is a problem *somewhere* within your library references.
In my experience, the problem is pretty much guaranteed to NEVER be the VBA
library. Afaik, if the VBA library was the true problem, the VBE wouldn't
be available, much less capable of generating error messages.

--
HTH,
George

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

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

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


:

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


:

-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.

 

[Douglas J. Steele]

Whenever you get a message telling you there's a problem with a function
in the VBA library, you need to look beyond the VBA library to find the
problem because that error message is misleading.

When the VBE tells you it has a problem with Trim(), UCase(), Right(),
Left(), Date() or any of the other built-in VBA functions then its really
telling you there is a problem *somewhere* within your library references.
In my experience, the problem is pretty much guaranteed to NEVER be the
VBA library. Afaik, if the VBA library was the true problem, the VBE
wouldn't be available, much less capable of generating error messages.

That's my understanding too, George.

I believe that when searching through the references, VBA is always the last
library checked (thereby allowing you to replace VBA functions with your own
functions with the same names), therefore if Access encounters any problems,
it never gets to the VBA library.

It's possible to have Access go directly to the VBA library by
disambiguating the function calls: using VBA.Trim(...) or VBA.Date(), rather
than simply Trim(...) or Date()

 

[RobGMiller]

Thanks for your input Fenix,

Thanks to Dave, George and Doug as well.

It looks like I have to consider many different types of techniques to
address all the issues discussed here.

As far as the syntax of the reference listed above, it took me a while to
come up with that. After many attempts, this was the first syntax that
actually worked for all circumstances on the development environment. I know
its a bit convoluted and I suppose I could try to search further but it
seemed ok until I started getting the "There is an invalid use of the .(dot)
or ! operator or invalid parenthesis" error on some environments because of
it.

In any case, I am out of time for a bit and most of what I've heard in the
last few days from this discussion is pretty time consuming to try out so
I'll have to get back to you on this discussion as soon as I can.

Thanks again.




--
RobGMiller

-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.

Hello Rob...

You are having the same problems I used to have; to the point of
switching to Alpha Five ! LOL

After many trial an errors ( and THANKS to all the good people in the
newsgroups) I discovered that 99.9% of the times it was my error. The
most common error is when you reference something like what you
mentioned above. The use of the "[" and "]" characters and the mix of
all the others are very confusing and Access is what I call "picky".

I haven't test your example above but by looking at it, you're using
the parenthesis too? I've never seen references like that. Try
checking the syntax and see if that helps.

Related to the Now(), Left(), etc, functions; I've never had any
problem with them. They've worked flawlessly.

Also, "References", my rule is "use only the default references of the
mdb when created AND the ones I can control"; I only use DAO so the
ADO reference is disabled on all my dbs and of course, the "Visual
Basic for Applications Extensibility". All the others are custom dll
that I have total control when installing/upgrading the FE.

Believe me, this might sound stupid but I've hit the wall so many
times that I've learned.

Lastly, let's say you're exporting a query to excel thru vba. It's
lovely to have all the excel constant when programming but... late
binding my friend. I know I'm going to get flame but believe me, I
don't have to worry about what version of excel they have ! or Word
for that matter. I've done some pretty nice stuff with excel and word
using late binding with the Users having different versions of office.

I've have several multi-User applications at work using Access 2003
and so far so good.

Take your time, have a beer if you drink, and revise all the coding,
try to "normalize" it. You'll find out latter the benefits.

Anyway's..my two cents.

 

[RobGMiller]

After some trial and error I've managed to build a test environment that will
bring up at least one of the errors and allow tracing through the code. I'm
not entirely certain how it came about. The error may occur because of the
way access security, involving sandbox mode, is setup. However, this is
pretty raw speculation because I could not find a setup that consistently
produced the error.

The error I've focused on is:

"There is an invalid use of the .(dot) or ! operator or invalid parenthesis"

I've come to a couple of conclusions about it. First, it is probably related
to the long reference format used to access data in controls on other forms
located on other tabs in a tab control. Since, I could not find any other way
of referencing such controls, I had to find a way to use the reference.

By trancing through the code I was able to determine that two conditions
cause the error.

1 - If a User defined function uses the reference to a control that contains
a reference to a different control.

2 - If a User defined function used in a control source passes the reference
through the function's paramenters.

I found that the way around it is to call a user defined funtion from the
controls control source. The function can reference the form or the control
on the form using the problematic format.

The exact reference format as mentioned earlier is:

Control.ControlSource =
[Forms]![MainForm]![SubFormContainer].[Form]![OtherSubFormContainer].[Form]![TabName].[Pages]("PageName").[Controls]("SubFormContainer").[Form]![ControlName]

(To display the content of another control on a tab control)

Or

Control.ControlSource =
UserDefinedFunction([Forms]![MainForm]![SubFormContainer].[Form]![OtherSubFormContainer].[Form]![TabName].[Pages]("PageName").[Controls]("SubFormContainer").[Form])

(To pass a reference to a form).


As I mentioned earlier I have found that the same sort of technique works
with the other error : "Error 3075: Function is not available in expressions
in query expression".

Using a user defined function to avoid using a built in function in a
control's control source or in an access query will avoid the error on
certain computers.



--
RobGMiller

-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.