I need to change which subroutine I call based on the user's input. Can I
save the name of the Sub in a variable? So far I've tried:

Dim MySub as string
MySub = "sLoadSNData"
Call MySub

No surprise this complains that "expected procedure, not variable". So
what's the syntax or best way to dynamically vary the subroutine called?
Thanks so much!
--
sam

If you implement your subs as methods of a class, you can use CallByName ...

In a class module (in this example called 'MyClass') ...

Public Sub FirstSub()

MsgBox "This is first sub"

End Sub

Public Sub SecondSub()

MsgBox "This is second sub"

End Sub

In another module ...

Public Sub CallMethod(MethodName As String)

Dim cls As MyClass
Set cls = New MyClass
CallByName cls, MethodName, VbMethod

End Sub

Public Sub TestCallMethod()

CallMethod "FirstSub"
CallMethod "SecondSub"

End Sub

--
Brendan Reynolds (MVP)


"smk23" <(E-Mail Removed)> wrote in message
news:FF93B2AC-9B83-4E27-B980-(E-Mail Removed)...
>I need to change which subroutine I call based on the user's input. Can I
> save the name of the Sub in a variable? So far I've tried:
>
> Dim MySub as string
> MySub = "sLoadSNData"
> Call MySub
>
> No surprise this complains that "expected procedure, not variable". So
> what's the syntax or best way to dynamically vary the subroutine called?
> Thanks so much!
> --
> sam

<(E-Mail Removed)> wrote:

> I need to change which subroutine I call based on the user's input.
> Can I save the name of the Sub in a variable? So far I've tried:
>
> Dim MySub as string
> MySub = "sLoadSNData"
> Call MySub
>
> No surprise this complains that "expected procedure, not variable". So
> what's the syntax or best way to dynamically vary the subroutine
> called? Thanks so much!

Maybe this URL will help.

http://www.mvps.org/access/modules/mdl0002.htm

--
RuralGuy

Please reply to the newsgroup so all may benefit.

Great tip. But the Eval function works for functions, not subs (requires a
return value).
--
sam


"RuralGuy" wrote:

> <(E-Mail Removed)> wrote:
>
> > I need to change which subroutine I call based on the user's input.
> > Can I save the name of the Sub in a variable? So far I've tried:
> >
> > Dim MySub as string
> > MySub = "sLoadSNData"
> > Call MySub
> >
> > No surprise this complains that "expected procedure, not variable". So
> > what's the syntax or best way to dynamically vary the subroutine
> > called? Thanks so much!
>
> Maybe this URL will help.
>
> http://www.mvps.org/access/modules/mdl0002.htm
>
> --
> RuralGuy
>
> Please reply to the newsgroup so all may benefit.
>

Brendan,
Thanks. I followed your example and got CallByName to work. But what I am
doing is loading controls on an unbound form, different controls depending on
user input. So when I moved that code to a module, now it doesn't understand
what control I mean.

So do I put minimal code in the class module method to point back to the
form sub? It seems like it's getting convoluted when all I really want to do
is load these or those controls depending on what the user selects.

Appreciate the help.
--
sam


"Brendan Reynolds" wrote:

> If you implement your subs as methods of a class, you can use CallByName ...
>
> In a class module (in this example called 'MyClass') ...
>
> Public Sub FirstSub()
>
> MsgBox "This is first sub"
>
> End Sub
>
> Public Sub SecondSub()
>
> MsgBox "This is second sub"
>
> End Sub
>
> In another module ...
>
> Public Sub CallMethod(MethodName As String)
>
> Dim cls As MyClass
> Set cls = New MyClass
> CallByName cls, MethodName, VbMethod
>
> End Sub
>
> Public Sub TestCallMethod()
>
> CallMethod "FirstSub"
> CallMethod "SecondSub"
>
> End Sub
>
> --
> Brendan Reynolds (MVP)
>
>
> "smk23" <(E-Mail Removed)> wrote in message
> news:FF93B2AC-9B83-4E27-B980-(E-Mail Removed)...
> >I need to change which subroutine I call based on the user's input. Can I
> > save the name of the Sub in a variable? So far I've tried:
> >
> > Dim MySub as string
> > MySub = "sLoadSNData"
> > Call MySub
> >
> > No surprise this complains that "expected procedure, not variable". So
> > what's the syntax or best way to dynamically vary the subroutine called?
> > Thanks so much!
> > --
> > sam
>
>
>

"smk23" <(E-Mail Removed)> wrote in message
news:FF93B2AC-9B83-4E27-B980-(E-Mail Removed)
> I need to change which subroutine I call based on the user's input.
> Can I save the name of the Sub in a variable? So far I've tried:
>
> Dim MySub as string
> MySub = "sLoadSNData"
> Call MySub
>
> No surprise this complains that "expected procedure, not variable". So
> what's the syntax or best way to dynamically vary the subroutine
> called? Thanks so much!

Application.Run MySub

ought to do it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Dirk, it says it can't find the procedure, which is in the the same form
module that I used Application.Run MySub in. I tried changing it (MySub) to
Public which didn't help.
Thanks!
--
sam


"Dirk Goldgar" wrote:

> "smk23" <(E-Mail Removed)> wrote in message
> news:FF93B2AC-9B83-4E27-B980-(E-Mail Removed)
> > I need to change which subroutine I call based on the user's input.
> > Can I save the name of the Sub in a variable? So far I've tried:
> >
> > Dim MySub as string
> > MySub = "sLoadSNData"
> > Call MySub
> >
> > No surprise this complains that "expected procedure, not variable". So
> > what's the syntax or best way to dynamically vary the subroutine
> > called? Thanks so much!
>
> Application.Run MySub
>
> ought to do it.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

I'm pretty sure it's down to a minor syntactical error now. I have:

Dim strSubToCall as String

strSubToCall = Me.SN1Load

Application.Run strSubToCall

Returns error: can't find the procedure '.'
When I tried:

Application.Run Me.SN1Load

that works.
--
sam


"Dirk Goldgar" wrote:

> "smk23" <(E-Mail Removed)> wrote in message
> news:FF93B2AC-9B83-4E27-B980-(E-Mail Removed)
> > I need to change which subroutine I call based on the user's input.
> > Can I save the name of the Sub in a variable? So far I've tried:
> >
> > Dim MySub as string
> > MySub = "sLoadSNData"
> > Call MySub
> >
> > No surprise this complains that "expected procedure, not variable". So
> > what's the syntax or best way to dynamically vary the subroutine
> > called? Thanks so much!
>
> Application.Run MySub
>
> ought to do it.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

"smk23" <(E-Mail Removed)> wrote in message
news:146F73CA-543E-42A1-9CBD-(E-Mail Removed)
> Dirk, it says it can't find the procedure, which is in the the same
> form module that I used Application.Run MySub in. I tried changing it
> (MySub) to Public which didn't help.
> Thanks!

If you mentioned before that this procedure is in a form module, I
didn't see it. I assumed it was in a standard module. I don't think
Application.Run will work with a procedure in a form module, even if
that procedure it Public. If you can't move the procedure to a standard
module, then I think Brendan's idea of CallByName is probably the way to
go.

If you want to move the proc to a standard module, but are having
trouble with references to "Me", consider using Screen.ActiveForm to get
a reference to the form in question. Screen.ActiveControl will give you
the name of the control with the focus, too.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Pass the control as a parameter to the method ...

'Class Module

Public Sub FirstSub(ctl As Control)

MsgBox "This is first sub"
MsgBox "Name of control is: " & ctl.Name

End Sub

Public Sub SecondSub(ctl As Control)

MsgBox "This is second sub"

'Don't pass this method a control that
'doesn't have a Value property!
MsgBox "Value of control is: " & ctl.Value

End Sub

'Form Module

Option Compare Database
Option Explicit

Private Sub Command2_Click()

Dim cls As MyClass

Set cls = New MyClass
CallByName cls, "FirstSub", VbMethod, Me.Text0
CallByName cls, "SecondSub", VbMethod, Me.Text0

End Sub

--
Brendan Reynolds (MVP)

"smk23" <(E-Mail Removed)> wrote in message
news:04FA8BEB-34BD-4592-8279-(E-Mail Removed)...
> Brendan,
> Thanks. I followed your example and got CallByName to work. But what I am
> doing is loading controls on an unbound form, different controls depending
> on
> user input. So when I moved that code to a module, now it doesn't
> understand
> what control I mean.
>
> So do I put minimal code in the class module method to point back to the
> form sub? It seems like it's getting convoluted when all I really want to
> do
> is load these or those controls depending on what the user selects.
>
> Appreciate the help.
> --
> sam
>
>
> "Brendan Reynolds" wrote:
>
>> If you implement your subs as methods of a class, you can use CallByName
>> ...
>>
>> In a class module (in this example called 'MyClass') ...
>>
>> Public Sub FirstSub()
>>
>> MsgBox "This is first sub"
>>
>> End Sub
>>
>> Public Sub SecondSub()
>>
>> MsgBox "This is second sub"
>>
>> End Sub
>>
>> In another module ...
>>
>> Public Sub CallMethod(MethodName As String)
>>
>> Dim cls As MyClass
>> Set cls = New MyClass
>> CallByName cls, MethodName, VbMethod
>>
>> End Sub
>>
>> Public Sub TestCallMethod()
>>
>> CallMethod "FirstSub"
>> CallMethod "SecondSub"
>>
>> End Sub
>>
>> --
>> Brendan Reynolds (MVP)
>>
>>
>> "smk23" <(E-Mail Removed)> wrote in message
>> news:FF93B2AC-9B83-4E27-B980-(E-Mail Removed)...
>> >I need to change which subroutine I call based on the user's input. Can
>> >I
>> > save the name of the Sub in a variable? So far I've tried:
>> >
>> > Dim MySub as string
>> > MySub = "sLoadSNData"
>> > Call MySub
>> >
>> > No surprise this complains that "expected procedure, not variable". So
>> > what's the syntax or best way to dynamically vary the subroutine
>> > called?
>> > Thanks so much!
>> > --
>> > sam
>>
>>
>>