Solved

Execute Procedure using Variable name

Posted on 2011-09-14
9
299 Views
Last Modified: 2012-06-21
In MS Access is there a way to call procedure where the procedure name is referenced inside a variable name?

Example:  
 
Dim MyProcedureName as String
       MyProcedureName = "TheProcedure"

Call  MyProcedureName
0
Comment
Question by:leeroypitre
  • 5
  • 3
9 Comments
 
LVL 21
ID: 36538385
You must use a macro.  

This is one of the few instances where a Macro can do something that can;t be done in VBA.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 36538401
Try this:

Dim MyProcedureName as String
       MyProcedureName = "TheProcedure"

EVAL(MyProcedureName)

mx
0
 
LVL 75
ID: 36538421
Specifically:


Public Function aaaaQuickTest() As Boolean

    Dim Fx
    Fx = "aaaTest5()"
    Eval (Fx)
   
End Function


Public Function aaaTest5()

    MsgBox "aaaTest5"
 
End Function

In the Immediate Window:

?aaaaQuickTest()
results in the Message Box being displayed.

mx
0
 
LVL 21
ID: 36538439
The Procedure has to be a function that you call using the Macro command RunCode

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 75
ID: 36538450
Back to your example:


Dim MyProcedureName as String
       MyProcedureName = "TheProcedure()"  ' add parens

EVAL(MyProcedureName)

and "TheProcedure()" needs to be a Function, not a Sub.

mx
0
 
LVL 21
ID: 36538471
mx, I forget about Eval. I started using macro for this back with Access 2.
0
 
LVL 75
ID: 36538494
Yeah ... no need for Macros now ... Eval() does it ....

mx
0
 
LVL 33

Expert Comment

by:Norie
ID: 36538682
If it's a sub can't you use Application.Run?
0
 
LVL 75
ID: 36538715
Yes ... that is correct ... like so

Public Function aaaaQuickTest() As Boolean

    Dim Fx
    Fx = "aaaTest5"   ' no parens this time
    'Eval (Fx)
    Application.Run Fx
End Function

Public Sub aaaTest5()

    MsgBox "aaaTest5"
 
End Sub

Forgot about the the Run Method, even though I've used it before.  Good for running code in an external db.

mx
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now