Solved

Howto Call [Event Procedure] from vba code in msaccess

Posted on 2011-03-20
14
895 Views
Last Modified: 2012-05-11
How can I call an [Event Procedure] of a Button in another form ?

Example:

I have frm1
with on Button: btn1
this btn1 has one event OnClick
the code is
Private Sub btn1_Click()
 msgbox("Clicked")
End Sub

Open in new window



Now I have to call this btn1 from another form: frm2

The call should check if there is an [Event Procedure]
and if so Call it
Something like this (This code is on frm2)

Sub CheckIfWeHaveEventToRun()
 dim frm as form, ctl as control
 set frm=frm1
 set ctl = frm("btn1") 'I need it this way because I don't know the name of the buttons, the real code will loop on all the controls on frm1

 if ctl.OnClick > "" then
  msgbox("We Have Event")
  'How can I run the event ?????
  'Call btn1[Event Procedure] ???????
 else
  msgbox("We Don't Have Event")
end if
end sub

Open in new window

0
Comment
Question by:DoronAviad
  • 5
  • 3
  • 2
  • +3
14 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35175617
Sure, but another approach would be to make the code you want to call should be "Public", so you can call it from anywhere.

Although I don't totally understand your logic, ...I'm sure this will get you started...

Private Sub btn1_Click()
    Call SomeCode
End Sub

Put something like this in a Module:
Public Sub SomeCode()
    'Your Code goes here
end sub


Sub CheckIfWeHaveEventToRun()
 dim frm as form, ctl as control
 set frm=frm1
 set ctl = frm("btn1") 'I need it this way because I don't know the name of the buttons, the real code will loop on all the controls on frm1

 if ctl.OnClick > "" then
  msgbox("We Have Event")
  'How can I run the event ?????
  Call SomeCode
 else
  msgbox("We Don't Have Event")
end if
end sub

JeffCoachman
0
 

Author Comment

by:DoronAviad
ID: 35175710
JeffCoachman

The Code Is Part Of a Bigger Code when I try To Add Support for Keyboard Shortcut

I have forms with Labels and Buttons On them

Each may have OnClick or OnMouseDown Events
I need to Catch a Combination of Alt + SomeChar (For Exmaple Alt+M)
The Char is the Control Caption With _ Using the & Sign(Form Example: &MotherBirthDay)

I have created a global KeyPressed Function to handel all forms KeyPressed Events
In the code that gets the Keyboard Pressed Keys I only know the form name and the control I need to call the Click event
So I Can't hardcode it like you suggested

I need a way to call Function in a form Like:
CallFunctionInAFrom(frm as Form,sFunctionName as String)

Do you know How to write this Code ?

Keep In Mind that the [Event Procedure] itself can be cange programticaly (in another part of the code) to some thing like =MyNewFunction()
In that case I will have to call MyNewFunction() and not the Click Event
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35175739
Then sorry, I misunderstood your question...

I am sure another Expert will be along to help you further...

JeffCoachman
0
 

Author Comment

by:DoronAviad
ID: 35175771
JeffCoachman

Thank you for trying



0
 
LVL 75
ID: 35175964

On Form1 change

Private Sub btn1_Click()
 msgbox("Clicked")
End Sub

To

Public Sub btn1_Click()
 msgbox("Clicked")
End Sub

Then on Form2 ... the idea is this:

Private Sub bntTest_Click()
    If Form_Form1.btn1.OnClick = "[Event Procedure]" Then
        Form_Form1.btn1_Click
    End If
End Sub

This works ...

mx
0
 
LVL 85

Accepted Solution

by:
Mike Tomlinson earned 500 total points
ID: 35176045
*Haven't really used Access in YEARS*

If the click handler is PUBLIC then you can use CallByName() generically like this to execute the handler given only the name of the form and the control:
' ... in Form1 ...

Public Sub Command1_Click()
    MsgBox "Form1 --> Command1"
End Sub

Private Sub Foo()
    Dim frmName As String
    Dim ctlName As String

    Dim frm As Form
    Dim ctl As Control
    Dim proc As String

    frmName= "Form1"
    ctlName = "Command1"    
    Set frm = Forms(frmName)
    Set ctl = frm(ctlName)
    If ctl.OnClick = "[Event Procedure]" Then
        proc = ctl.Name & "_Click"
        CallByName frm, proc, VbMethod
    End If
End Sub

Open in new window

0
 
LVL 75
ID: 35176058
Seems like a lot of extra code however ...?

mx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 35176065
Just depends on what the user is really doing I suppose!...

The form name and control in my example are hard-coded, but theoretically they could come from somewhere else programmatically.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35176267
I don't know if this is a duplicate:

In form1 f1cmd1 button Click event enter this code:

Public Sub f1cmd1_Click()
  MsgBox "Click f1cmd1 on Form1"
End Sub

In form2 f1cmd1 button Click event enter this code:

Private Sub f2cmd1_Click()
  Forms.Form1.f1cmd1_Click
End Sub

Save both forms, open both forms, and click f1cmd1 on form1 and you will get the message box

Click f2cmd1 on form2 and you will get the same message box.

0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 35176424
Hi,

if you want to create a global key handler, the easiest thing to use is a class module where you add a form reference with the "WithEvents" keyword.

Intantiate the class in the form which should react on a key and initialize it's form reference, then the first step is done: All forms which uses this method react on the same events. The Init procedure in the class module not only sets the reference but also sets the needed events. The class module contains the event function and can now do anything it wants with the form because the form reference is in the same form. So if you have any public sub/function/property you could now access it in the class module.

Problem is: You can forget to implement the function into the target form, or have a typing error in the name or the parameters and so on. So the easiest thing is to use an interface class. Create another class module which contains the name of the sub which should be available in the target form and its parameters. In the target form you now can add the interface with "Implements MyInterfaceName". If you try to compile the code now, the compiler will throw an error and say that the interface is not implemented completely. Now you can simply select the name of the interface class in the left combobox at the top of the VBA editor and select the interface function at the right combobox (if it is only one it is already done now). You have the correct function (sub/property..) with the correct parameters. If you now must change anything in the function (name, parameters) the you only need to change the interface class and compile it, the compiler will throw an error at any form code which implemented the interface. You can now change them all easily.
But that's not the only advantage: You can also implement different things in different forms. Form1 can open another form, Form2 can delete a record, Form3 can close the form - whatever you want. Because the interface only makes sure that all the defined functions and parameters are available, not what's in there. If you don't need them, leave them empty, otherwise fill them with the code you want to execute inside this special form - the external code doesn't need to know what's in there and what happens with calling the interface function.
For easy access simply instantiate the form reference in the first class module as the name of the interface, now IntelliSense only shows the interface functions available.

Another advantage of the interface is that you can run through the Forms collection with a "for each" loop, instantiate them as interface and then call one (or more) interface functions on all of them. For example, this is useful if you want to refresh data in different forms in one loop and any of these forms uses different methods to refresh them.

In the attached file you'll find a simple demo how to achieve this.

Cheers,

Christian
GlobalEvents.zip
0
 
LVL 75
ID: 35176497
Let me simply this to one line of code:


Private Sub bntTest_Click()
    If Form_Form1.btn1.OnClick = "[Event Procedure]" Then Form_Form1.btn1_Click
End Sub

mx
0
 

Author Closing Comment

by:DoronAviad
ID: 35176741
Thanks

Greate Solution
0
 
LVL 75
ID: 35176890
:-)
0
 
LVL 75
ID: 35176896
DoronAviad
I am just curious as to why you picked the solution you did, when it can all be done with one line of code?

mx
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now