[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1256
  • Last Modified:

I'm getting the error "the expression you entered has a function name that microsoft access can't find" in Access 2000

I've started getting this error on one PC - I've started testing this on a new access 2000 database on the PC. The function I'm trying to run is in a normal module called Module1:
Option Compare Database

Public Function tctest()
     Msgbox "Hello"
end Function

I create a macro called Macro1 which only has one line
RunCode

the function name which I've retrieved using the function wizard is tctest ()

When I run the macro I get the error "the expression you entered has a function name that microsoft access can't find"

I'm assuming that the problem is to do with the references. I've disconnected and connected all the references that I can but to no avail. The references are

Visual Basic For Applications
Microsoft Access 9.0 Object Library
OLE Automation
utility
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft VBScript Regular Expressions 5.5
Microsoft VBScript Globals
Microsoft VBScript Regular Expressions 1.0
Microsoft Agent Control 2.0

Any ideas or theories?
0
tony_colgan
Asked:
tony_colgan
  • 4
  • 3
  • 2
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Public Function tctest() as YouNeedSomethingHere    '<--  Functions return a value, which you need to specify here, such as Boolean, String, Integer, etc.  Subs do not return a value.
     Msgbox "Hello"
end Function
0
 
tony_colganAuthor Commented:
Yes that's valid for VB but this is in access (it's not required) - you need to use a function in order to use the RunCode command in a macro - the exact same database works on a different PC
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I create a macro called Macro1 which only has one line
Just as a wild off-topic remark, what is requiring you to use a macro, instead of just calling the VBA function?  Afaik, the only thing macros currently can do that VBA code cannot is handle menus.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
tony_colganAuthor Commented:
The initial database is linked to a SCADA application which calls a macro in the database - this is hardcoded in and can't be changed. The macro then calls a function using RunCode - the problem is that the error now occurs when the macro hits the RunCode line. I created this simple macro/function listed above on a new database to check that the problems where with the register/dlls on the pc and not that the initial database was corrupted. I believe that the problem is going to be down to a corrupt dll or registry listing. I'm hoping that someone will know which dll is used for function recognition in Access.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
A **DeCompile** may help here ...

But first, if you have not already:
Check for any **Missing References via the VBA Editor>>Tools>>References ....

Then, follow this procedure:

0) **Backup your MDB**
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

6) Close the mdb
7) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an example of the command line syntax  (be SURE to adjust your path and file name accordingly) for executing the decompile:

Run this from Start>>Run, enter the following command line - all on one line:

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\AzDoc\Pgrm\AzDocPgrm2K3.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Public Function tctest() as YouNeedSomethingHere    '<--  Functions return a value, which you need to specify here, such as Boolean, String, Integer, etc.  Subs do not return a value."

jHo ... this IS a Function ... so, if you don't have 'As' ... Variant is the default - which is what will be returned.

mx
0
 
tony_colganAuthor Commented:
Thanks,
   I'll try this tomorrow morning.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
MX - Thanks for that.  Some of us geniuses don't have a world of experience in not explicitly stating variables and function return values.  My bad.
0
 
tony_colganAuthor Commented:
We decided to rebuild the pc and that has sorted out the problem
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now