Need correct syntax for procedure name to use with Application.Run (procedurename)

I have a routine in a stand-alone module that handles some standard operations with controls on forms.  I need it to invoke the AfterUpdate event for the control it is handling.  I can't get either Eval or Application.Run to recognize the event procedure name constructed as control.name & "_AfterUpdate" no matter what I try.  

Are these (Eval and Run) limited to running code in stand-alone modules?  I'm thinking I just need to get the proc name syntax correct.  After all, a proc in a form is just another class module, right?  

I've check all the usual suspects (proc is declared Public, etc.)  I've tried:

"Form_" & form.Name & "." & control.name & "_AfterUpdate"  (e.g., "Form_frm_Test.txtTo_AfterUpdate")
"Form_" & form.Name & "_" & control.name & "_AfterUpdate"  (e.g., "Form_frm_Test_txtTo_AfterUpdate")

Any ideas?
skeptic23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Try this syntax:

Form_YourFormName.AfterUpdate

"After all, a proc in a form is just another class module, right?  "

A Form Module is a Class Module - yes

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
For control:

Form_YourFormName.YourControlName.AfterUpdate

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here is the syntax ... I just double checked:

 Call Form_frmTest.Text33_AfterUpdate

mx
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Also ... you need to make those events Public

EGL

Change

Private Sub Text33_AfterUpdate()

to

Public Sub Text33_AfterUpdate()

then this will work:

Call Form_frmTest.Text33_AfterUpdate
mx
0
skeptic23Author Commented:
mx

One thing I didn't stress is that I need to use something like Eval or Run, because I need to call the event procedure using a VARIABLE for the proc name.  As I mentioned, when I assign "Form_frm_Test.txtTo_AfterUpdate" (my actual test of your "Form_frmTest.Text33_AfterUpdate" format for the proc name), I get a run-time error 2517:  

Microsoft Office Access can't find the procedure 'Form_frm_test.txtTo_AfterUpdate.'

Here's the line of code that fails:

Application.Run "Form_frm_test.txtTo_AfterUpdate"

The form name is "frm_Test" and the control name I'm trying to run the AfterUpdate event proc for is "txtTo".

You can understand why I'd want a utility function that handles all sorts of controls on many forms to be able to construct the desired event proc name given just the control name.  Just a few lines of code would handle any and all controls, rather than a LONG Select Case structure that needs an entry added for each possible control/even proc combination.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
ok ... I see.  

See my post right above your last post ... about making those Event Public ... very Important - if you want to call them externally.  Did you try that ?

mx
0
skeptic23Author Commented:
mx, yes, already did that.

What's more, I tried putting the Application.Run code in a form to call a proc in the same form, which would make the Private/Public declaration a non-issue.  I get the same error.

This has all led me to think about the difference between regular and class modules.  What I'm up against is trying to invoke a class method using a variable that contains the method name.  Is that possible?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"the difference between regular and class modules."

But again, a form module is a class module.

"I need to call the event procedure using a VARIABLE for the proc name."

You mean ... like just the AfterUpdate part ?  Or the entire Test.txtTo_AfterUpdate  in a Variable ?

In the distant past, I know I had some code that would more or do this, because I used to read control names etc, from a table and use Eval() to make stuff happen.  And that was back in Access 2.0.

Let me see if I can point Leigh Purvis to this Q ... if anyone knows, it's him.

mx
0
skeptic23Author Commented:
mx, the answer is "both/and".  For this situation, I would concatenate the control name (ctl.name" with a string representing the event proc I want to call, e.g., "_AfterUpdate".  However, if we find a way to do it at all, there should be nothing to prevent me from, say, storing procedure names in a database table, reading them into a variable, and calling the routine using Application.Run(variablename)

A form module is a class module, and I'm suspecting that this is why I'm having trouble.  The normal way to invoke a class method (whether function or sub) is object.methodname.  Of course, the method name gets hard-coded this way.  What I'm looking for is a way to specify the method name in a variable.  

For example,

  Call Me.txtTo_AfterUpdate

would become something like (if I knew the correct syntax)

  Dim vx
  vx="txtTo_AfterUpdate"
  Call Me(vx)     '  (or something like that.)

0
skeptic23Author Commented:
Hooray!  Couldn't let this one go, so I finally found the answer:

CallByName(object, procname, calltype,[args()])

This allows the procname to be contained in a variable.

So, Application.Run for procs in non-class modules, and CallByName for those in class modules.

How do I designate my own post as a solution?  Do I get a little green button too?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Leigh PurvisDatabase DeveloperCommented:
Ah!  I see you got there (CallByName) just now before I could post.  (EE problems).
I was just going to look to see if I had an AfterUpdate example using it.

Can't be sure though offhand without checking first - but the called procedure name *might* need to be publicly defined (and of course by default event procedures aren't - though they can be - it'd perhaps be another effort to implement).

Depends I suppose - how are you calling your procedures?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
DAMM !!!!  damm !  I totally forgot about CallByName !!!  *%&*#&@*(&@*(

Good job!!!

"ut the called procedure name *might* need to be publicly defined "

Yes ... actually, I've mentioned that above ...

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So Leigh ...

ok ... actually, how exactly DOES one use the function?

Lets say I have form called frmTest
And I have a button with this Click event:

Public Sub cmdSave_Click()
     MsgBox "Groovy - called from external module"
End Sub

And lets say I have the Event name  (Click in this case) in a variable - and I want invoke the cmdSave_Click event above from an external module.... what is the syntax?

Public Function mCallFrmControlEvent() As Boolean

    Dim sEvent As String
    sEvent = <some event name ?? >
    Call CallByName(?? ,  ??  , ??)  

End Function

Further, lets say the Form Name and the control and the event need to be in variables ... what would the syntax be ?

Public Function mCallFrmControlEvent() As Boolean
     Dim sFrm As String '    or As Form or ??
     Dim sCtl  As String  '   or As Control  or ??
     Dim sEvent As String ' or ??

    Call CallByName(?? ,  ??  , ??)  

End Function

Of course, probably one would be reading the form name, control and event out of a table ...

mx
0
Leigh PurvisDatabase DeveloperCommented:
Morning!  <yawn>
I dunno - getting wussy these days.

The three (required) parameters that CallByName accepts are (as defined in the documentation)
Object, Procedure Name, Call Type.
So fundamentally you need to pass to it the *object* which contains the procedure.
So if you have in a table (and hence recordset) the names of the form, control and event...

CallByName Forms(rst("FormName")), rst("ControlName") & "_" & rst("ProcedureName"), VbMethod

Note - you'd build a valid form object for the first parameter and a string to represent the procedure name in the second.  So perhaps more clearly using a couple of variables

Dim frm as Access.Form
Dim strProc as String

Set frm = Forms(rst("FormName"))
strProc = rst("ControlName") & "_" & " rst("ProcedureName")
CallByName frm, strProc, VbMethod


The reason I mentioned "might" about the procedures being public is because the privacy might be gotten around using the class syntax Form_frmFormName.ProcedureName (as a fully explicit syntax exposes private procedures in standard modules too).
However I suspect that in CallByName - that would once again need to be public.
Whether we called
CallByName Forms!FormName, "Control_Afterupdate", VbMethod
or
CallByName Form_FormName, "Control_Afterupdate", VbMethod
regardless.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Thank you Leigh ... that seems to clear it up. I will experiment with this tonight.  Too bad the the M$ documentation does not give THESE kind of examples.

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Testing Results (sorry for the delay):

Public Sub cmdSave_Click()
    MsgBox "Groovy - called from external module"
End Sub

Public Function mCallFrmControlEvent() As Boolean
    Dim frm As Access.Form
    Dim strProc As String
   
    Set frm = Forms("frmTest")
    strProc = "cmdSave_Click"
    CallByName frm, strProc, VbMethod
End Function

Result 1
This works, message box pops up
*******************************

I tried all other combinations of calling syntax such as

CallByName Forms!FormName, "Control_Afterupdate", VbMethod
and
CallByName Form_FormName, "Control_Afterupdate", VbMethod

but unless the Command button Click event is Public (as opposed to Private) ... you get the expected

"Run-Time error 2465 ... Application-defined or object-defined error"  
(even with Break on All Errors set)

Nonetheless ... we clearly have a way to call procedures using variables.  As I mentioned, in the distant past, I  used to do this all the time - reading proc and control names out of a table and using EVAL() to make it happen. But, I cannot find any of that code, so not sure what I was doing.  Definitely was not using CallByName - because only more recently did I discover that ... and had already forgotten about, LOL.

thanks again.

mx
0
Leigh PurvisDatabase DeveloperCommented:
Yep, as I'd suspected.  
I suppose it's not too shocking that CallByName doesn't exactly duplicate the direct object calling syntax.
It just isn't reproducing the behaviour of class referenced code.  The method itself might actually *look* for public procedures first rather than just trying to execute it.

There is the potential (when using Eval and/or Run to execute methods of objects) that the called method will actually execute more than once!
This could range from being utterly harmless, through slightly wasteful to downright terrible.
AFAICR CallByName performs more as would be expected.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
** Leigh **
Index Q - right up your roadway - give us the insight:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23167354.html

mx
0
NetminderCommented:
Closed, 500 points refunded.
Netminder
Site Admin
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Ahhh ... really now.  Leigh made quite a contribution here ... at my request in fact, and certainly deserves some points.  

mx
0
NetminderCommented:
Hmmm...

I don't doubt that, but I saw no objection to the recommendation. However, I can reopen this, and perhaps we will get some input from the Asker.

Netminder
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sorry, I missed the recommendation email.

mx
0
Leigh PurvisDatabase DeveloperCommented:
Cheers for the concern - but I dare say that I didn't get here soon enough to directly help the questioner.
Hopefullly what's been posted overall will be of use to future readers anyway though.

No worries in any case.

>> "but I saw no objection to the recommendation"
And nor are you ever likely to from I NM.
I have an overabundance of apathy for such things.  :-)
Unless a poster is going to delete a PAQ worthy question... I do actually pipe up then I suppose.

Cheers all.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"Hopefullly what's been posted overall will be of use to future readers "
It already has :-)

Thank you Leigh.

mx
0
Leigh PurvisDatabase DeveloperCommented:
Cheers for the concern - but I dare say that I didn't get here soon enough to directly help the questioner.
Hopefully what's been posted overall will be of use to future readers anyway though.

No worries in any case.

>> "but I saw no objection to the recommendation"
And nor are you ever likely to from I NM.
I have an overabundance of apathy for such things.  :-)
Unless a poster is going to delete a PAQ worthy question... I do actually pipe up then I suppose.

Cheers all.
0
Leigh PurvisDatabase DeveloperCommented:
Sorry - the re-open cause the duplicate post.
I guess "Discussion" makes odd things happen. (And the fact that I have too many web browser windows open :-)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Leigh .... can you email me an email address for you?  I will keep it personal and give it to no one.

thx.mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I guess back to the way it was ...

mx
0
Leigh PurvisDatabase DeveloperCommented:
The way it was is fine for me certainly.

(Email address is in profile - always has been :-)

Cheers.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I'm ordering glasses right now :-)

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.