Solved How to execute a variable that holds the name of a function? (1 Viewer)

[KitaYama]

I have a string variable that holds the name of a function.
I can run the function this way :

Sub test()
    Action = "fEmergencies"
    Run (Action)
End Sub

Function fEmergencies(Optional sit As Boolean = False, Optional Trigger As String = "frmSwitchBoard")
   
    ' Do something here

end Funcion

How can I run the function and pass a true for sit argument.
This line gives me Compile Error.

Sub test()
    Action = "fEmergencies"
    Run Action(True)
End Sub

Thanks for any kind of advice.

 

[June7]

Why do you want to do that as opposed to just calling the function?

 

[KitaYama]

Why do you want to do that as opposed to just calling the function?

Sorry, I can't understand what you mean?
If you mean why I save the name of the function in a variable, it's a long story.
To make it short, The string (Action) is the result of a DLookup to extract the function based on user privileges and is used for different items and buttons from the ribbon.

 

[theDBguy]

Check out the CallByName() function.

 

[KitaYama]

Check out the CallByName() function.

CallByName's first argument is object (Required: Variant (Object). The name of the object on which the function will be executed.)

What should I use for object? I only want to run a function in a module.

 

[plog]

Eval should do it:

Application.Eval method (Access)

Office VBA reference topic

docs.microsoft.com

Eval("fEmergencies(True)")

But fEmergencies is a function, which means it returns something. Where do you want that return value to go?

 

[KitaYama]

Eval("fEmergencies(True)")

I've already checked eval.
It should be the variable. not the name of the function.
Something like :
Eval(Action(True))
But I received compile error.

There's no return value for the function. It can be changed to sub.
During the evolution of the database, there's been a lot of changes. Previously there's been a return value. But not now.

 

[arnelgp]

for a test.
but Eval wont accept if there is second Parameter and no 1st parameter, like:

Action = "fEmergencies(, 'myswichBoard')"

Sub test()
    Dim Action As String
    Action = "fEmergencies()"
    Eval Action
    
    Action = "fEmergencies(True)"
    Eval Action
    
    Action = "fEmergencies(true, 'myswichBoard')"
    Eval Action
End Sub

Function fEmergencies(Optional sit As Boolean = False, Optional Trigger As String = "frmSwitchBoard")
  
    ' Do something here
    MsgBox "sit = " & sit & ", Trigger = " & Trigger
End Function

 

[theDBguy]

CallByName's first argument is object (Required: Variant (Object). The name of the object on which the function will be executed.)

What should I use for object? I only want to run a function in a module.

There's a sample here. See if it helps...

Click buttons in a random order

Having a brain fade here.... I have 6 buttons cmd1 to cmd6 that I want to click once each in random order. The standard code is Me("cmd" & I) It works fine for properties e.g. Me("cmd" & I).Visible but fails for events e.g. Me("cmd" & I).Click => error 438 - object doesn't support this...

www.access-programmers.co.uk

 

[KitaYama]

for a test.
but Eval wont accept if there is second Parameter and no 1st parameter, like:

Action = "fEmergencies(, 'myswichBoard')"

Sub test()
    Dim Action As String
    Action = "fEmergencies()"
    Eval Action
   
    Action = "fEmergencies(True)"
    Eval Action
   
    Action = "fEmergencies(true, 'myswichBoard')"
    Eval Action
End Sub

Function fEmergencies(Optional sit As Boolean = False, Optional Trigger As String = "frmSwitchBoard")
 
    ' Do something here
    MsgBox "sit = " & sit & ", Trigger = " & Trigger
End Function

@arnelgp You're genius.
Million thanks.

 

[KitaYama]

There's a sample here. See if it helps...

Click buttons in a random order

Having a brain fade here.... I have 6 buttons cmd1 to cmd6 that I want to click once each in random order. The standard code is Me("cmd" & I) It works fine for properties e.g. Me("cmd" & I).Visible but fails for events e.g. Me("cmd" & I).Click => error 438 - object doesn't support this...

www.access-programmers.co.uk

Thanks for the sample database.
But the sub is not running in the context of a form. Me (as a reference to the form) can not be used.

 

[gemma-the-husky]

Eval should work, but it's a question of getting the syntax correct.
The function that you are evaluating must know what it's source is. You don't have to pass the source to the eval, although I am sure you can.
If the source is always a certain query you can set up the parameters for that query before calling the "eval"

set query parameters
set query name "somequery"
decide which function
eval ("function name")

function name will then be able to operate on "somequery".

Something like that.

 

[KitaYama]

Eval should work, but it's a question of getting the syntax correct.
The function that you are evaluating must know what it's source is. You don't have to pass the source to the eval, although I am sure you can.
If the source is always a certain query you can set up the parameters for that query before calling the "eval"

set query parameters
set query name "somequery"
decide which function
eval ("function name")

function name will then be able to operate on "somequery".

Something like that.

@gemma-the-husky

I appreciate taking your time and trying to help, but unfortunately I can't understand what you mean. It's possibly because of my lack of knowledge in Access and English.
I don't have any query and I don't need to run the function by running a query.
In my case, Dlookup returns a string value (the name of a function) and I needed to run the function and pass a parameter.
For now @arnelgp 's method works perfectly.

Thanks again for your input.

 

[gemma-the-husky]

I meant that the function you are running might need to obtain some external information, and although you can pass in that information inside the eval, (and that's the best way) you could achieve the same result in other ways.