Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How To Execute a Method from a Variable? (A2k (9.0.3821) SR-1)

    How To Execute a Method from a Variable?
    A2k (9.0.3821) SR-1

    Private Sub dtmDate_DblClick(Cancel As Integer)

    Dim strFunction As String
    Dim FunctionExecute as Variant

    Dim frm As Form
    Dim ctl As Control
    Set frm = Me
    Set ctl = Screen.ActiveControl

    strFunction = "Form_" & frm.Name & "." & ctl.Name & "_AfterUpdate"

    Debug.Print "strFunction: " & strFunction

    FunctionExecute = Eval(strFunction)

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How To Execute a Method from a Variable? (A2k (9.0.3821) SR-1)

    Sorry, but I'm not sure what exactly you're trying to do. Why are you trying to indirectly call an event procedure as a method of the current form? <img src=/S/confused.gif border=0 alt=confused width=15 height=20> All you have to do is use this (And even the Call keyword is optional):

    Call dtmDate_AfterUpdate

    Since you appear to be doing this from inside the Click event of the control, there doesn't seem to be a good reason for going the long way around to call the AfterUpdate event of the same control.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Execute a Method from a Variable? (A2k (9.0.3821) SR-1)

    Hi Charlotte

    Your right!

    Just trying to develop a handful of standard routines that are variable driven so they can be copied and pasted into other apps with minimal hardcoding.

    John

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How To Use the CallByName Function in VBA6/A2k

    You may want to look into the CallByName function, which was introduced in VB/VBA 6.0, and thus available if using A2K or later. This function allows you to execute a method of an object, or to get or set an object's property, by using a string name for the procedure being called. Simple example:

    Create new class module (cleverly named "Class1") with one method:

    Sub TestSub(strMsg As String, strTitle As String)
    MsgBox strMsg, vbInformation, strTitle
    End Sub

    Create sub in a standard module:

    Sub TestCallByNameFunction(strProcName As String)

    Dim obj As New Class1
    CallByName obj, strProcName, VbMethod, "This is a test message.", "TEST MSG"
    Set obj = Nothing

    End Sub

    To test, enter in Immediate window:

    TestCallByNameFunction("TestSub")

    MsgBox with test message will appear. Note that the 1st three arguments for CallByName (object, procname, calltype) are all required; 4th argument, args(), is optional; it accepts a variant (array) that corresponds to the argument list for the procedure being called by the function. In above example the arguments being passed to function are the two strings for message text & title.

    A second example shows how to use CallByName to set a form control's property at runtime:

    Public Sub TestCallByName(strForm As String, strCtl As String, _
    strProcName As String, varValue As Variant)

    Dim frm As Form
    Dim ctl As Control

    DoCmd.OpenForm strForm
    Set frm = Forms(strForm)
    Set ctl = frm.Controls(strCtl)

    CallByName ctl, strProcName, VbLet, varValue

    Set frm = Nothing
    Set ctl = Nothing

    End Sub

    To test this (using Employees form in Northwind.mdb) enter this in Immediate window:

    TestCallByName "Employees","FirstName","ForeColor",vbred

    The Employees form will open, FirstName textbox will appear in red rather than black. Use Object Browser to assist in correctly identifying property names. For more details on CallByName function, check VB Help. I don't know how useful this would be in context of calling form event procedures by name, you may or may not find use for this. I've used CallByName mainly only for test purposes, but there may be situations where it may prove useful to automate or generalize some routine.

    HTH

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Use the CallByName Function in VBA6/A2k

    Hi Mark

    The reason you see me <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> with my hands in the air is to catch the stuff going over my head.

    I tried your samples and they work fine, I

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How To Use the CallByName Function in VBA6/A2k

    I'm not sure if CallByName will be useful for what you are doing - keep in mind that using this function will not be as efficient as calling a sub or function or setting or getting a property in a more direct, straightforward way, since CallByName by necessity uses late binding in accessing an object's methods or properties. But if you have a sample DB you want to post (if small enough) or send me, go ahead, but I can't guarantee I'll have any brainstorms!

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Try Custom Classes in VBA6/A2k

    John, you said
    <hr>Just trying to develop a handful of standard routines that are variable driven so they can be copied and pasted into other apps with minimal hardcoding.<hr>
    Sorry, but I don't see how you expect to apply that here. You're working with code inside a form and trying to reference specific routine for specific controls in that form. That doesn't appear to me to be something you can standardize.

    Trying to create universal, one size fits all, code like this in Access VBA is probably a waste of time. What you can do is learn to use classes and create custom classes for your forms and controls. Those clases are interchangeable between databases because they simply handle the behavior of each instance of the objects bound to them and don't care about their names. You bind an object like a textbox to a custom TextBox class, and that class can contain a DblClick event for that object that calls the AfterUpdate event of the same object. The code for both events would live inside the custom class rather than in the form that contains the controls. The beauty of it is that you can bind as many textboxes as you want on as many forms as you have open because you create a new instance of the class for each textbox using the WithEvents keyword. After that, the class object keeps track of the control it's bound to and behaves appropriately.
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Try Custom Classes in VBA6/A2k

    Charlotte

    You wrote
    <<What you can do is learn to use classes and create custom classes for your forms and controls.>>

    Can you give an example of what you mean here. As you are probably going to say I have been using it all along.
    I would still like to find out.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Try Custom Classes in VBA6/A2k

    Classes are too complex to cover in a single post, but they are covered quite effectively in many books on Access, including the Access Developers Handbook for each version. Forms have modules, which happen to be class modules, but I'm talking about a user-defined class module. Look up the WithEvents keyword in on-line help for more information. Here's a link to the Microsoft MSDN library on Program With Class Modules that might be of interest.

    You use a custom class the same way you do a prebuilt class. For instance, you declare an object as that class and then instantiate it:

    Dim objTxt As clsCustomTextBox

    Set objTxt = Me!Text1
    Charlotte

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Try Custom Classes in VBA6/A2k

    Thank you

  11. #11
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How To Execute a Method from a Variable? (A2K)

    If you are still pursuing this issue, the Access Application object Run method is another way to run a sub or function by using a string variable to reference the procedure. This will work within Access or when using Access as an Automation server in another database or application. Simple example (calling a sub procedure):

    Sub TestRun(strSubName As String, ParamArray varArgs())
    Application.Run strSubName, varArgs(0), varArgs(1)
    End Sub

    Public Sub TestSub(strText As String, strTitle As String)
    MsgBox strText, vbInformation, strTitle
    End Sub

    To test, enter in Immediate window:

    TestRun "TestSub", "This is a test message.", "TEST RUN METHOD"

    A MsgBox will appear. Example of calling a function:

    Function TestRunFunction(strFuncName As String, ParamArray varArgs())
    TestRunFunction = Application.Run(strFuncName, varArgs(0))
    End Function

    Public Function TestFunction(ByVal intVal As Integer) As Integer
    TestFunction = intVal + 1
    End Function

    Example of use in Immediate window:

    ? TestRunFunction("TestFunction",2)
    3

    Note use of ParamArray keyword (short for Parameter Array). This allows you to pass a variable number of arguments to a procedure. The Run method accepts up to 30 optional arguments (in addition to required first argument, Procedure, which is a string variable representing name of sub or function being called). The ParamArray keyword gives you a lot of flexibility, but to use properly you'd need to know in advance how many arguments the procedure being called requires, the data types, etc. Also, it is not the most efficient means of passing arguments, since each element in a Parameter Array is a Variant. So I am not necessarily advocating its use, but illustrating how it may be used in conjunction with Run method. Interestingly, if you pass excess arguments to procedure when using Run method, an error does not occur as expected, since Run method can accept up to 30 optional arguments (unless you try to pass 31 arguments). The excess arguments are ignored. Example:

    TestRun "TestSub", "This is a test message.", "TEST RUN METHOD",1

    and

    ? TestRunFunction("TestFunction",2,1)
    3

    both work correctly. Not that I'd recommend doing this in practice.

    The Run method seems to be intended for use in external applications where Access is serving as the server app, but you may find a use for it. But as Charlotte suggests, custom Classes may be best approach for creating generic routines.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •