473,903 Members | 4,817 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to call a sub/function using a variable holding the sub/function name?

579 Contributor
Hi everybody,

[Access 2010]

I'm not sure if this is possible or not, but I wanted to check to be sure.

I'm working with a database that depends on running a bunch of tables through a number of different queries (including make-table queries). This happens a number of different times throughout the database as data is compiled for different sections.

Due to the inefficiency of this process, I'm trying to automate everything. Seeing as how the process depends heavily on the order of operations, I thought that creating a Main() subroutine that would call all the other subroutines/functions would be a good way to keep things organized.

Another thing I'm trying to do to debug is print a message when the next subroutine in the process is run with a little message about the status. I thought that I might be able to pass the subroutine/function name to another subroutine/function as a string, use the string in a "debug.prin t" to display a message, then use Call with the string variable to call the subroutine/function.

So, here's an example of what I'm trying to do:
Expand|Select|Wrap|Line Numbers
  1. 'Main subroutine
  2. '''''''''''''''''
  3. Public Sub Main()
  5.     StartSub "SelectProviders"
  7.     StartSub "RunProviderUpdate"
  9. End Sub
  11. 'StartSub subroutine
  12. '''''''''''''''''''''
  13. Public Sub StartSub(strSubroutine as String)
  15.     Debug.Print strSubroutine & " started..."
  16.     Call strSubroutine
  17.     Debug.Print strSubroutine & " complete."
  19. End Sub
I get a compile error at "Call strSubroutine". Is there anything I can use to replace this with so I can call the subroutine and still use the string in "Debug.Prin t"??

Nov 8 '12 #1
8 13825
32,584 Recognized Expert Moderator MVP
I don't believe this is supported in Access (Though Excel has an Eval() procedure that may be used for such I believe).
Nov 9 '12 #2
12,516 Recognized Expert Moderator MVP
I think Access has the same function but I don't have it at home to test.
Nov 9 '12 #3
32,584 Recognized Expert Moderator MVP
Indeed so Rabbit. In the Help page (2003) I found the following :
The following example assumes that you have a series of 50 functions defined as A1, A2, and so on. This example uses the Eval function to call each function in the series.

Expand|Select|Wrap|Line Numbers
  1. Sub CallSeries()
  2.     Dim intI As Integer
  4.     For intI = 1 To 50
  5.         Eval("A" & intI & "()")
  6.     Next intI
  7. End Sub
Nov 9 '12 #4
579 Contributor

Were you able to get it to work for my example? I've tried the Eval() function a couple of different ways, such as passing a string/variant to the subroutine and using that variable in the Eval() function with the Call statement (I get error 2482 - Microsoft Access cannot find the name 'SelectProvider s' you entered in the expression).

Expand|Select|Wrap|Line Numbers
  1. Public Sub StartSub(strSubroutine As Variant)
  3.     Debug.Print strSubroutine & " started..."
  4.     Call Eval(strSubroutine)
  5.     Debug.Print strSubroutine & " complete." & vbCrLf
  7. End Sub
Nov 9 '12 #5
2,322 Recognized Expert Moderator Top Contributor
I did a bit of testing and found:
I could only get it to work if the function call includes () and it must be a function not a sub. Example code shown below:

Expand|Select|Wrap|Line Numbers
  1. Public Sub RunProc(strProc As String)
  2.  Eval (strProc & "()")
  3. End Sub
  5. Public Function TestProc()
  6.    Debug.Print "It worked"
  7. End Function
Testing with
Expand|Select|Wrap|Line Numbers
  1. RunProc("testproc")
in the immediate pane worked fine.
Nov 9 '12 #6
32,584 Recognized Expert Moderator MVP
As Smiley highlights, your code is not similar to that in the Help page, in that it leaves out the parentheses at the end "()". Also, you don't include the code of your procedure at all, so it's hard to know if it's a Function procedure or a Sub-routine procedure. The parameter name of strSubroutine, which is Dimmed as a Variant, tells me not to rely on your naming as an indication of what it is, but it would certainly need to be a function if you want it to work, and probably a Publicly visible one too.

Here's something that looks like what you need (It will still only work if provided with the name of a valid Function procedure) :
Expand|Select|Wrap|Line Numbers
  1. Public Sub StartFunc(ByVal strFunction As String)
  3.     strFunction = strFunction & "()"
  4.     Debug.Print strFunction & " started..."
  5.     Call Eval(strFunction)
  6.     Debug.Print strFunction & " complete."
  8. End Sub
Nov 9 '12 #7
2,322 Recognized Expert Moderator Top Contributor
A quick tested indicated that any function to be called through the use of Eval() must be public (As expected).
Nov 9 '12 #8
32,584 Recognized Expert Moderator MVP
Thank you for that Smiley. I guessed that Eval() is similar in that respect to running SQL code, in that it doesn't have any concept of where it's called from and only has access to items that are available to it within the library it runs from (Access.Applica tion in the case of Eval()).
Nov 10 '12 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Max | last post by:
I'm new with Javascript and can't seem to figure out what I'm doing wrong here as I'm not able to pass a simple variable to a function. In the head of doc I have: <script type="text/javascript"> function radioenable(value) { document.forms.search.elements.value.disabled=false;
by: Tony Liu | last post by:
Hi, I want to get the name of the calling function of an executing function, I use the StackTrace class to do this and it seems working. However, does anyone think that there any side effect towards this approach such as how would it works in multi-thread. Thanks Tony
by: S?ren Gammelmark | last post by:
Hi I have been searching the web and comp.lang.c of a method of using variable-argument function pointers and the like. And some of the questions arising in this post are answered partly in these posts, but I ask mainly for a way to found a way to solve this problem. I'm a hobbyist so there might be an alltogether better solution to the problem, so please come with any solution you might think of. I'm open to ideas. --- And i'm not...
by: JasonKendall | last post by:
I have an object that I want to serialize to a string variable. I want to use the SoapFormatter, for readability. The serialization is working great and I'm pleased with the results. DeSerialization, however, is giving me an error that "The root element is missing." This happens even when I pass the return value from Serialize() directly back to the Deserialize() method. ============ Code Snippet =============== 'Seems to work great.
by: lorirobn | last post by:
Hi, I have a main menu that opens several different reports. I am adding an OrderByOn, and currently have it set up for one report with the following statements: !.OrderBy = strOrderBy !.OrderByOn = True I would like to set those statements up using a variable for report name. Is that possible? For instance, it would be something like:
by: gabba | last post by:
Hi, is it possible to call a subroutine (or a function) using variable name? Sub a() Response.write("sub a") End sub Sub b() Response.write("sub b") End sub
by: Larax | last post by:
Best explanation of my question will be an example, look below at this simple function: function SetEventHandler(element) { // some operations on element element.onclick = function(event) {
by: ambikadevi | last post by:
What is the function to call the Postgres stored procedures function using ADODB? PrepareSP() function is used to call the Oracle Stored Procedure. For PHP application. Waiting for the response!
by: roblenator | last post by:
I am trying to access files on a remote windows server using the following code; my $node = 'server5'; opendir(DIR, '\\$node\C$\Servers') or warn "open failed. reason: $!"; I get an error; open failed. reason: no such file or directory at ... line xx. If I place the node name in place of the $node, it works fine.
by: ManningFan | last post by:
I've got a field in a table that keeps the name of functions I need to run. I do a DLookup on the table to retrieve the name of the function I need to run (based on whatever criteria). How can I get VBA to run the desired function? I tried DoCmd.OpenFunction, I've tried using the Call command and I've tried just sticking the variable in the code, among other things. In
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.