Application.Run with variable gives error

Question

trying to run a sub/function based on a name I read out of a table. But when I try to have application.run work with the variable "s" i get an error message. 

Runtime error 2517, cannot find the procedure 'park3."

makes sense as I don't have a "park3." procedure but I am entering the string without the period. If I substituted the "s" in Temp2() with "park3" I have an execution of park3, only to be followed by another runtime error cannot find procedure "."

Please help or advise me of a different way to achieve calling a procedure via a variable which gets its value from a table or any other input.

Thanks!

Matthias

Sample Code

Function park3()

MsgBox " and this is Park3"

End Function

Sub temp2()

Dim s As String

s = "park3"

Application.Run s

End Sub

Answer 1

Keep in mind that your code has to be in a standard public module, and not a forms module.

So you can’t call code routines “inside” of a forms code module using application.run. Application.Run ONLY works for functions/subs in a standard code module. 

You certainly could call a routine in a standard module that in turns calls the code in the form. You have to declare such functions or subs in the forms module as public for this to work.

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

Thank you for your reply, Albert. I didn't not know about the requirement for Application.Run to work in standard public module only, I must have overlooked that too many times. With that information, it works!

Answer 3

Thanks, but somehow it doesn't work for me.  I still have VBA looking for a procedure called "park3." with the pesky period at the end.  At least that what I assume as the error message reads can find procedure 'park3.'

Answer 4

I even tested the following which worked fine as well:

Function park3()

    MsgBox " and this is Park3"

End Function

Sub temp2()

' Dim s As String

' s = "park3"

    Dim db                    As DAO.Database

    Dim rs                    As DAO.Recordset

    Dim s As String

    Set db = CurrentDb

    Set rs = db.OpenRecordset("Table6") 'Table6 - 2 Columns

                                        '   EntryID AutoNumber PK

                                        '   EntryVal Text = park3 for testing purposes

    With rs

        If .RecordCount <> 0 Then

            s = ![EntryVal]

            Application.Run s

        End If

    End With

    rs.Close

    Set rs = Nothing

    Set db = Nothing

End Sub

Answer 5

The above works fine for me.