The function you entered can't be used in this expression

  • Thread starter Thread starter RobGMiller
  • Start date Start date
R

RobGMiller

Windows Server 2003 running full Access 2003. SP3 with all the latest Office
updates.

There is a bunch of vba code in a Calendar form that we've used for years.
This Calendar form is handy because it doesn't required a Reference to an
external library. It's not an Active X Control its simly a form with simple
access controls on it. It has worked well until I tried to use it in an MDE
on a Windows 2003 Server accessed from terminal services.

The odd thing is that the exact same Calendar form with the same code works
from one MDE but not another on the same server. It works in an MDB and on
all other systems that its been used on for 10 years.

The full error it generates when a day lable is clicked is: The expression
On Click you entered as the event property setting produced the following
error: The function you entered can't be used in this expression.

The on click event reads as follows: =HandleSelected("lbl25").

The HandleSelected function calls HandleIndent strName and hides the form
(CloseForm HideIt:=True)

The HandleIndent function sets Me(lable_control_name).SpecialEffect =
acSunken and assigns a global variable the value of the caption of the lable
that was clicked as in: (Me!Day = Me(lable_control_name).Caption)

When the form, which is a dialog, is closed(Hiden) the next line gets its
value and places it in a Date text box control.

The error occurs at the time the day lable is clicked. A message box at the
start of HandleSelected function is never reached.

The "=HandleSelected("lbl25")" was change to an [Event Procedure] to call
the function from within the OnClick event. This did not help.


Any thoughts would be greatly appreciated.
 
It does compile with all the references in place. Although, the app is
developed on a different station, I've assigned the references to the MDB
from the Server where it doesn't work and created an MDE from that to ensure
that all references will be detected properly.

There are 3 references that are not in the working MDE. MSOutlook-11,
MSExcel--11 and MSWord-11. I guess I have to be desparate to believe that any
of these three references might affect a form that doesn't use anything from
those libraries.

In any case, in a test MDE that uses this form but no object from any of the
three references so it would compile, the problem dissapears if the
Outlook-11 reference is removed. However, in a different application that
does not reference Outlook-11 but does use Excel and Word the problem
persits.

I think isolaring the offending reference may not be the right direction to
trouble shoot this problem because it only happens on this server and those
references are needed to make these applications work.

This server has an Office Install issue which is described in a different
MSForum post.

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Although it seems unlikely that two different MDEs reacting differently to
the same code might be causes by an environment problem that is common to
both, I cannot help thinking that its got something do to with it.




--
RobGMiller


Alex Dybenko said:
Hi,
check that you can compile the project and all references in place

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

RobGMiller said:
Windows Server 2003 running full Access 2003. SP3 with all the latest
Office
updates.

There is a bunch of vba code in a Calendar form that we've used for years.
This Calendar form is handy because it doesn't required a Reference to an
external library. It's not an Active X Control its simly a form with
simple
access controls on it. It has worked well until I tried to use it in an
MDE
on a Windows 2003 Server accessed from terminal services.

The odd thing is that the exact same Calendar form with the same code
works
from one MDE but not another on the same server. It works in an MDB and on
all other systems that its been used on for 10 years.

The full error it generates when a day lable is clicked is: The expression
On Click you entered as the event property setting produced the following
error: The function you entered can't be used in this expression.

The on click event reads as follows: =HandleSelected("lbl25").

The HandleSelected function calls HandleIndent strName and hides the form
(CloseForm HideIt:=True)

The HandleIndent function sets Me(lable_control_name).SpecialEffect =
acSunken and assigns a global variable the value of the caption of the
lable
that was clicked as in: (Me!Day = Me(lable_control_name).Caption)

When the form, which is a dialog, is closed(Hiden) the next line gets its
value and places it in a Date text box control.

The error occurs at the time the day lable is clicked. A message box at
the
start of HandleSelected function is never reached.

The "=HandleSelected("lbl25")" was change to an [Event Procedure] to
call
the function from within the OnClick event. This did not help.


Any thoughts would be greatly appreciated.
Click to expand...
Click to expand...
 
There are 3 references that are not in the working MDE. MSOutlook-11,
MSExcel--11 and MSWord-11. I guess I have to be desparate to believe that any
of these three references might affect a form that doesn't use anything from
those libraries.
Click to expand...

Not desparate, instead perfectly accurate!

In my experience *ANY* MISSING reference messes up all sorts of other
references, even those which have nothing to do with the missing one. If you
don't need the Outlook, Excel or Word references, just remove them; if you DO
need them elsewhere in your code, consider "late binding" to bring up a
reference to the user's current installation rather than assuming that
everyone has 2002 installed.
 
Thanks for the replies to both Alex and John,

Your suggestions have pushed my investigation beyond reasonnably logical
conclusions.

The issue was that although the refrences were assigned and the mdb compiled
on the computer that didn't work which only has Office 2003 installed, the
MDE was generated on a computer that has Office 2007 and 2003 Installed.
Unfortunately the Outlook component of 2003 was not installed.

The MDE conversion process must recompile and use the Outlook 12 objects
code on the local computer instead of the binary from the original compile
process. The application reacted as if Outlook-12 was referenced on an Office
2003 system.

It is surprising that it made a difference to the Access 11 objects which I
presume are used to implement access forms and controls.

Now, if only I can get Access to run without trying to reinstall itself all
the time, I can move on.

Thanks for your time.
 
It is surprising that it made a difference to the Access 11 objects which I
presume are used to implement access forms and controls.
Click to expand...

Access forms and controls do NOT require the Outlook, Word or Excel libraries.
My guess is that they were added for some specific reason (which may no longer
apply). Try making a copy of the database, removing those references,
recompiling - and see if anything breaks.
 
Back
Top