Call public procedure by variable

  • Thread starter Thread starter Pendragon
  • Start date Start date
P

Pendragon

Access03/WinXP

I've trying to research a solution but nothing has worked. Undoubtedly it's
the way I have set up the code and am trying to call the procedure. Any help
is appreciated.

Module name is CommRun
In CommRun, I have:

Public Sub CR_Coding()
On Error GoTo Err_Coding

Dim QtrNo As Long
Dim strFilter As String, ssql As String

.....and then a whole series of queries to run....

End Sub

A temp table has records for groups, each of which has a specific public
procedure to run. The code bombs the Call command.

Dim strCRName as String
Dim db as Database
Dim rs as Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM zTemp_CommGrpsToRun")
rs.MoveFirst
Do While Not rs.EOF
With rs
strCRName = rs("CRName")
'MsgBox CRName
Call strCRName
.MoveNext
End With
Loop

Suggestions??

Thank you!!
 
Access.Run "procedure", "arg", "arg"

or

x = Eval("FunctionName()")


Run should work on both subs and functions, Eval should work on a function.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
To my knowledge you cannot use a FUNCTION or SUB name in a variable as a
means to execute that specific code. The name has to be explicity provided.
I would guess its tied to the compiling that Access does.
 
strCRName = rs("CRName")
'MsgBox CRName
Call strCRName
Click to expand...

In place of the call, you can use:

Application.Run strCRName

The above even works for runtime, and I often use a system "prompt" screen
to run code routines on client computers that are runtime only, but I need
to run some routine that updates or fixes data....
 
and if the procedure is from an object, you can use CallByName. Here, mostly
useless, but just for illustration:

CallByName Application, "Run", VbMethod, "PublicSubroutineNameHere"


since it is the same as

Application.Run "PublicSubroutineNameHere"


but it shows that you CAN supply the name, as string, of a procedure to be
executed on an existing object, here "Run" on the object Application. The
object can be any object (one created by Access, or one created by you, a
from, a report, or from a class).



Vanderghast, Access MVP
 
Thanks to all for the information. I used Albert's code to success, though I
would bet money (but not a lot!) that I had tried exactly that before.
thanks!
 
Back
Top