Access VBA - 'CallByName' - Call routine on form?

[ironfelix717]

Hi,

I am struggling to call a function by it's name. The function resides in a form object, but CallByName() is struggling to locate the object.

Dim frm         As Form
Dim val          As String
Set frm = Form("frmOperations")

val = CallByName(frm, "ClearFormHandler", VbMethod)

The form "frmOperations" does exist, as well as the 'ClearFormHandler' function being present in that form's module.
The function is also public.

Thanks for any help.



EDIT:
So, if I specify the form as the internal keyword, it works.:

Dim frm As Object
Set frm = Form_frmOperations

MsgBox CallByName(frm, "ClearFormHandler", VbMethod)

But i need to dynamically assign this, so I don't know how to do that.
Using the Forms or Form collection did not work either.

 

[theDBguy]

Just a guess, but have you tried maybe the AllForms collection?

 

[ironfelix717]

Just a guess, but have you tried maybe the AllForms collection?

I just tried, it requires a form 'object' as the argument, which doesn't make sense to me (why return a form, by passing a form - makes no sense).

thanks

 

[theDBguy]

I just tried, it requires a form 'object' as the argument, which doesn't make sense to me (why return a form, by passing a form - makes no sense).

thanks

Sorry to hear that.

 

[ironfelix717]

So far i've tried...

Set frm = Forms(sfrmname)
set frm = Form(sfrmname)
set frm = Modules(sfrmname)


None work. But specifying the keyword does?

 

[theDBguy]

So far i've tried...

Set frm = Forms(sfrmname)
set frm = Form(sfrmname)
set frm = Modules(sfrmname)


None work. But specifying the keyword does?

Hmm, I thought you also tried AllForms? How did that look like?

 

[arnelgp]

the "sub" you are calling must be Public.

example:

Public Sub ClearFormHandler()
...
End Sub

 

[The_Doc_Man]

As arnelgp points out, you need a public entry point for the easiest possible call. At least in theory, that should be enough to get you started. However, there is a slight "gotcha" here. I don't know the syntax to get to a specific entry point in a module other than by this type of direct reference.

From a purely theoretical viewpoint, calling a specific entry point by qualifying references should have syntax like this... modulename.entrypoint for general modules and forms(formname).module.entrypoint for class modules. It is possible that the syntax of Access VBA would allow something like modulename(entrypointvariable) or the corresponding parenthetical syntax for class modules.

The entry points ARE visible in the Access Object Browser. But if they are not public then it might be very tough to get to them. The only problem is I'm not sure that the names visible in Object Browser are also visible to the VBA environment. I therefore MUST clarify this as SPECULATIVE, not something I've ever actually done.

If this sort of thing doesn't work, then CallByName (which normally is limited to properties and methods) will not work on user-defined entry points - which ARE kept in a different format than entry points for intrinsic properties and methods. The properties & methods have a .DLL symbol table for their values whereas I don't think VBA code possibly can have that.

You suggested you had tried a few things. But this is the part where you have to either think completely out of the box or give up. You are trying to select an entry point by its name in a variable from outside the entry point, but why wouldn't you just have a SELECT CASE to trigger the call? How many entry points can you have?

PUBLIC SUB EPDISPATCH( EPN AS STRING, ARG1 AS VARIANT, ARG2 AS VARIANT, ...,) AS VARIANT
SELECT CASE EPN
    CASE "SIN"
        EPDISPATCH = SIN( ARG1 )
    CASE "COS"
        EPDISPATCH = COS( ARG1 )
... (etc)
END SELECT

After all, you can't have "infinite" numbers of entry point names because the calling code has to know the name in order to call it. And for those who might ask, yes - that is a kind of dispatch table used by other languages that can build .DLL files.

 

[MajP]

CallByName is a completely useless function to do what can be done much cleaner in other ways. It is a cheap excuse for trying to do a Delegate in vba. And it does not do what you can do with a Delegate in other languages. You absolutely cannot pass a method as a variable like other languages. Why can you not simply call the method? Why do you need callbyname?

OK. Sorry for the rant but IMO CallByName ranks up with multivalue fields, lookups in tables, split forms, and other excuses for bad programming and database design.

If you really need it you need it. I would be happy if you prove me wrong where this has utility. I have never seen it. In 20+ years of writing Access code, I have never seen a viable case for this, that is not a band-aid on a poor database or code design.

 

[The_Doc_Man]

@MajP, though I approach it from a different direction, I think I agree with you. There is a certain questionable logic in the idea of deferring the name decision for a CALL (or function invocation). At best, it is "going the long way around" to identify a call target - and at worst, it is a leap into the unknown. As a conjecture, if MS were to show us the code equivalent for CallByName then perhaps I might understand what it actually does and why it exists. Your question is also my question: "Why can you not simply call the method?"

 

[MajP]

@MajP, though I approach it from a different direction, I think I agree with you. There is a certain questionable logic in the idea of deferring the name decision for a CALL (or function invocation). At best, it is "going the long way around" to identify a call target - and at worst, it is a leap into the unknown. As a conjecture, if MS were to show us the code equivalent for CallByName then perhaps I might understand what it actually does and why it exists. Your question is also my question: "Why can you not simply call the method?

I have never seen a realistic need for the CallByName function. It is a solution looking for a problem. I am still waiting for someone to show me a viable need. I have seen it used where a function name was stored in a table, and the whole thing made me want to cringe. It was a puzzle, wrapped in a mystery, wrapped in an enigma.
Again, I am willing to be proved wrong. Show me a legitimate need that is not a band-aid for poor programming.

 

[Minty]

So, if I specify the form as the internal keyword, it works.:

Dim frm As Object
Set frm = Form_frmOperations

MsgBox CallByName(frm, "ClearFormHandler", VbMethod)

But i need to dynamically assign this, so I don't know how to do that.
Using the Forms or Form collection did not work either.

Apart from agreeing this is a poor choice of method. I can explain why this works.
Setting an object to Form_frmOperations actually creates an instance of the form.
See this https://www.access-programmers.co.u...on-about-binding-controls.316219/post-1748000 post and thread about form reference and bindings.

 

[MajP]

Yeah I am not sure I understand the issue. I was assuming the form is already open so
set frm = forms("someFormName") would work
If not you have to open it. Maybe that was a misunderstading.
This will run a method on any form by passing the name and the procedure name. Open or closed. You can run it hidden and choose to close after.

Public Sub MyCallByName(FormName As String, ProcedureName As String, Optional HideForm As Boolean = True, Optional CloseForm As Boolean = True)
  Dim frm As Access.Form
  If Not CurrentProject.AllForms(FormName).IsLoaded Then
    If HideForm Then
       DoCmd.OpenForm "FormDemo", , , , , acHidden
    Else
       DoCmd.OpenForm "FormDemo"
    End If
  End If
  Set frm = Forms(FormName)
  frm.Visible = Not HideForm
  CallByName frm, ProcedureName, VbMethod
  If CloseForm Then DoCmd.Close acForm, FormName
End Sub

Public Sub TestIt()
  MyCallByName "formDemo", "Helloworld"
  MyCallByName "formDemo", "Goodnightworld", False
End Sub

My demo runs two methods. In first case the form remains hidden runs the method then it is and closes out the form. In the second case It is shown and form left open.

 

[LarryE]

1. Make sure the procedure "ClearFormHandler" is a Public Sub not a Private Sub.
2. You can then use the syntax:

Call ClearFormHandler

You can call it using a command button or using any event procedure on the form. You don't need to define anything or set anything.

 

[ironfelix717]

Hi all,

Good discussion... it was the classical mistake of the form(s) not being open so Access couldn't find the object... Whoops.

As for the "theological" aspect to this question....

CallByName is a completely useless function to do what can be done much cleaner in other ways. It is a cheap excuse for trying to do a Delegate in vba. And it does not do what you can do with a Delegate in other languages. You absolutely cannot pass a method as a variable like other languages. Why can you not simply call the method? Why do you need callbyname?

OK. Sorry for the rant but IMO CallByName ranks up with multivalue fields, lookups in tables, split forms, and other excuses for bad programming and database design.

If you really need it you need it. I would be happy if you prove me wrong where this has utility. I have never seen it. In 20+ years of writing Access code, I have never seen a viable case for this, that is not a band-aid on a poor database or code design.

The fact is: theres no reason we shouldn't be able to do it. Should a language not support this ability, then its a caveman language. Every language has the ability to do this, if VBA can't, well, then chalk another one up for the golden boys as Microsoft.

Second, While I agree it's a band-aid fix in probably most of it's cases, that doesn't render it useless. The application it's being implemented in now, CallByName is cutting down a convoluted Select Case function that takes time out of my day that I frankly don't need to waste. This is a question of how you compartmentalize your code. In this case, I needd CallByName to access these compartments.

Could the application be designed to negate the need for CallByName? Yeah, I can go back to hardcoding all the form names. What fun that would be.

 

[The_Doc_Man]

A convoluted variant of that SELECT CASE is EXACTLY how you implement .DLLs using relative addressing from a base dispatcher table in some very powerful operating systems and database systems. A .DLL wouldn't work correctly if you didn't use that relative reference method because the only other option would be to force the library to be in a "fixed" virtual location in memory every time, i.e. always starting at the same virtual address regardless of how big or small the app is, and regardless of how many and which other libraries are involved.

 

[MajP]

The fact is: theres no reason we shouldn't be able to do it. Should a language not support this ability, then its a caveman language.

If you have not figured out VBA is a caveman language then you might need to temper your expectations. It is pretty caveman. Even the VBE looks the same as it did 20+ years ago. Here is how it would be in something more modern like .net. Here you can truly pass a function as a variable.
Delegates in VB.NET - CodeProject