Fire control's events with VBA

[NauticalGent]

The title is a little misleading...

I know you can simply fire an event by typing:
Me.MyControlName_AfterUpdate

But the question asked on a different forum was can you do this by looping through the form's controls. Below is what some of the suggestions were and what I tried on my test db:

Dim ctl as Control
For Each ctl in Me.Controls
    ctl.Name & "_AfterUpdate" ' Does not compile
    Call ctl.Name & "_AfterUpdate" 'Does not compile
    Run ctl.Name & "_AfterUpdate" 'Compiles but throws an error
    [ctl.Name].[AfterUpate] 'Compiles but throws an error
Next

I'm pretty sure this cannot be done but before I moved on I wanted to ask those whose Kung-Fu is greater than mine...

 

[MajP]

Your terminology is really incorrect. You are not FIRING an event. You are simply trying to call a procedure. That procedure just happens to be an event handler. Small thing but words have meaning. See discussion.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

You can raise a custom event, but you can only do the action that would trigger an event (such as entering a control, or moving to record)

 

[MajP]

If you want to call a method by name then you use the callbyname function

CallByName function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Application.Run method (Excel)

Office VBA reference topic

docs.microsoft.com

But neither of these work in a form to call the event handler, as far as I can tell.

 

[NauticalGent]

But neither of these work in a form to call the event handler, as far as I can tell.

Nope. Didn't think so either. I was just curious and had a minute to ask. Thanks for the response...

 

[MajP]

Can you link the thread. It sounds like such a bad design, I am sure there is a cleaner way. If you pull the code out of the event handler and make it a stand alone code in a standard module and have the event handler call these standalone procedures, you could do this using the Application run. But again that seems like a dumb amount of work on top of a dumb idea.

 

[NauticalGent]

It wasn't a forum per se and I really don't know exactly how I got there, but here it is...

Programatically call a controls afterupdate event

Hi I have a situation where I wish to call a option groups control’s afterupdate event where I have many of them each with its own specific after update event eg dim ctl for each ctl in me.controls select case ctl.controltype case acoptiongroup ctl.value =ctl.defaultvalue This is where I wish to...

community.spiceworks.com

 

[arnelgp]

CallByName FormObject, Replace$(ctl.Name, " ", "_") & "_AfterUpdate", VbMethod

FormObject is the Form where the event of the control is.
use Me if it is the same form where the event resides, example:

CallByName Me, Replace$(ctl.Name, " ", "_") & "_AfterUpdate", VbMethod

Note 1: you need to make the "AfterUpdate" event Public Sub.
Note 2: only controls that has Event (textbox, combo, etc.) can call vbMethod

 

[NauticalGent]

Arnel,

You never cease to amaze me! No idea how you figured it out but it works as advertised and your notes were spot-on. I really have no need for this at this time but it is always good to know what can be done.

Thanks again, you're a rock-star!

 

[arnelgp]

just a note why the Replace() function is there.
if your control name has space in them, access will build an event with an underscore
in replacement of the space.
the replace() will build the correct "event" name with underscore.