Object Event, Event Firing, Capturing.
MS Access Forms and Reports each have their own Class Modules. Nearly all controls on a form—such as CommandButtons, TextBoxes, ComboBoxes, ListBoxes, and others—support event-driven programming, which is one of the key features that make a Microsoft Access Application a powerful Database Management System.
For example, the TextBox control provides several events, such as GotFocus, LostFocus, BeforeUpdate, and others. We typically write VBA code in these event procedures to perform tasks controlling data entry, validating data, or updating modified data.
Similarly, the OnClick event of a Command Button can be used to open forms or reports, or to run procedures or macros that perform specific tasks. Normally, code for such predefined events is written directly within the form’s or report’s class module.
This time, however, we will take a different approach — by redirecting event handling to an external location, instead of the normal procedure of writing Code directly in the Form's Class Module.
From MS Access’s perspective, these event procedures can be categorized into two groups: Built-in and User-Defined Events.
Built-in events triggered by controls on a form or report can be captured in a stand-alone class module, allowing you to execute code there to perform any desired actions, rather than writing the code directly in the form or report’s class module.
In addition, we can define our own custom events within form or report modules and capture them either in another form’s class module or in an independent class module object. The required code to handle the event can then be written in the target module to perform the necessary actions for the form. This approach only requires a single line of code in the built-in event procedure to transmit the event to the target location, where the actual handling logic can be implemented.
First, we will explore the second option — defining custom events — and learn how to invoke a custom event from one form and capture it in another form or a class module object as it occurs, then execute the required task there.
We are starting with custom events because they demonstrate all the fundamental elements of this powerful programming feature. This approach involves a few basic statements (listed below), their correct placement in different modules, and the proper naming of events in both the source and target modules, which is crucial for the process to work correctly.
The WithEvents, Event, and RaiseEvent Statements.
We will start with a simple example, but it is essential to understand the placement of the key elements of a custom event and how they work together in a synchronized manner.
--- form1 ---
Private WithEvents obj as Form_Form2
Private Sub obj_eventName(parameter) ‘Capture the Event, from Form2 in obj
‘write Code here
End Sub
--- Form2 ---
Public Event eventName(parameter) ‘Declare Event
RaiseEvent eventName(parameter) ‘Invoke the Event
Capturing built-in events, such as button clicks or ComboBox selections, in a class module object is much simpler than defining a custom event in one class module and capturing it from another.
Events and event trapping are strictly handled through class modules and cannot be implemented in standard modules. However, you can still call subroutines or functions in Class Modules from a Standard module.
Demo Run of User-Defined Events.
Let’s try an example using two simple forms — each containing a TextBox and a Command Button. This trial run will help you understand the basic steps involved in this procedure. The sample design of Form1 is shown below:
Create a new Form named Form1 and open it in Design View.
Insert a Command Button on the Detail Section of the Form.
Display the Property Sheet (F4) and change the Name Property Value to cmdOpen.
Change the Caption Value to Open Form2.
Insert a Label Control above the Command Button and change the Name Property Value to Label1 and the Caption to Label1.
Change Form1’s Has Module Property value to Yes.
Display the Module of Form1.
Copy and paste the following Code into the Class Module of the Form and save the Form.
Option Compare Database Option Explicit Private WithEvents frm As Form_Form2 Private Sub cmdOpen_Click() Set frm = Form_Form2 frm.Visible = True End Sub Private Sub frm_QtyUpdate(sQty As Single) Dim Msg As String If sQty < 1 Then Msg = "Negative Quantity " & sQty & " Invalid." ElseIf sQty > 5 Then Msg = "Invalid Order Quantity: " & sQty Else Msg = "Order Quantity: " & sQty & " Approved." End If MsgBox Msg, vbInformation, "frm_QtyUpdate()" End Sub Private Sub frm_formClose(txt As String) MsgBox "Form2 Closed", vbInformation, "farewell()" Me.Label1.Caption = txt End SubCreate a second Form with the name Form2 and open it in Design View.
A sample image of Form2 is given below:
Create a Text Box on the Form and change its Name property value to Qty.
Change the child-label Caption to Order Qty (1-5):
Create a Command Button below the Text Box and change the Name Property value to cmdClose and change the Caption Property value to Close.
Display the Form’s VBA Module, Copy and Paste the following Code into the Module, and save the Form.
Option Compare Database
Option Explicit
Public Event QtyUpdate(mQty As Single)
Public Event formClose(txt As String)
Private Sub Qty_AfterUpdate()
RaiseEvent QtyUpdate(Me!Qty)
End Sub
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
Private Sub Form_Unload(Cancel As Integer)
RaiseEvent formClose("Form2 Closed")
End Sub
Form1 Class Module Code
Let us take a closer look at Form1’s Class Module Code.
The first line: Private WithEvents frm As 'Form_Form2' declares a Form Object Variable of Form2, enabled with the WithEvents capturing feature. Events originating from the Form2 Class Module are captured here in the Form1 VBA Module, and the corresponding Subroutine is executed, depending on the Event Raised.
On the cmdOpen_Click event procedure, the Form2 object is instantiated in the frm Object Variable and made Form2 visible in the Database Window.
The Private Sub frm_QtyUpdate() Subroutine is executed when the Qty TextBox on Form2 is updated with a value, and from within the AfterUpdate Event of the TextBox, the RaiseEvent QtyUpdate() is executed if an invalid quantity value is entered.
The Private Sub frm_formClose() is executed when the Command Button on Form2 is clicked to close Form2.
Form2 Class Module Code
Now, let us go through the Form2 Module’s Code.
In the global declaration area of the Form2 Class Module, two Public Event Procedure names are declared, with parameters.
When some value is entered into the TextBox and the Tab Key is pressed, the AfterUpdate Event Procedure is run, and the QtyUpdate() Event is raised with the Statement RaiseEvent QtyUpdate(Me!Qty). The Qty value is passed as a parameter to the QtyUpdate() Function.
The frm_QtyUpdate() Sub-routine runs from the Form1 Class Module and performs validation checks on the passed value and displays an appropriate message.
When the Command Button on Form2, with the Caption Close, is clicked, the formclose() Event is raised, a message is displayed, and the Label control on Form1 is updated with the same info.
The sequence of actions between Form1 and Form2 is illustrated in the diagram below. You can use this as a guide when experimenting with your own examples.
How does it work?
It works like a Radio Transmitter and Receiver, tuning to the frequency, like a setup.
The global declaration Public WithEvents frm as Form_Form2 on Form1’s VBA Module is, like a Radio-Receiver, states that whatever action is transmitted from Form2 (from the frm Property) will be received in Form1 and executed in the related Sub-routine.
In Form2’s Module at the global declaration section, you will find the statement Public Event QtyUpdate(mQty As Single). You may compare this statement with the transmission Frequency (or Event Name: QtyUpdate()) with data from the TextBox as the parameter.
The transmission starts only when you call the RaiseEvent Statement, and it fires the Event declared at the Class Module level of Form or Report, with parameter value (if defined).
Example: RaiseEvent QtyUpdate(Me!Qty).
The same name QtyUpdate() is a Subroutine Name – not to declare as a Function - (on Form1 Class Module, like we tune in to the same transmission frequency to receive the radio broadcast) where we write code to run a validation check on the passed data as a parameter and display a message based on the validity of the value passed from Qty textbox on Form2.
The subroutine name is always prefixed with Form2’s Class Module instance Property name: frm, and the subroutine header line is written as Private Sub frm_QtyUpdate(sQty As Single), to tune into the correct frequency of transmission.
The subroutine name (QtyUpdate) in the event declaration on Form2 must match the subroutine name on Form1. On Form1, the subroutine name will be prefixed with Form2’s instance variable and an underscore, for example: frm_QtyUpdate(sQty As Single), as described above.
Note: It means that the actual Code, for the Event Procedure declaration done on Form2 Class Module is written in Form1 Module, by addressing the subroutine directly with the object name (frm_) prefix.
Armed with the above background information, let us try out the Forms to see how they work.
The Demo Run.
Open Form1.
Click on the Command Button. Form2 is instantiated in memory and made visible.
If Form2 is overlapping Form1, then drag it to the right side so that both Forms remain side by side.
On Form2, there is a text box named Qty (Quantity); the valid value range is 1 to 5.
Any value outside this range is invalid, and an appropriate error message is displayed.
Enter a value in the text box and press the Tab Key.
The Text Box’s AfterUpdate Event is run, and within this Event, the RaiseEvent QtyUpdate(Me!Qty) statement fires the Custom Event and passes the TextBox Value as the parameter.
Public Sub frm_QtyUpdate(sQty as Single) Subroutine on Form1 Class Module runs and validates the parameter value and displays an appropriate message.
Try this out by entering different values into the TextBox and pressing the Tab Key.
When you are ready to close Form2, click on the Command Button.
I am sure you know how the whole thing works, and try something similar in your own way. When in doubt, use this page as a reference point.
More on this next week.
Links to WithEvents ...Tutorials.
- WithEvents MS-Access Class Module Tutorial
- WithEvents and Defining Your Own Events
- WithEvents Button Combo List TextBox Tab
- Access Form Control Arrays and Event Capturing
- Access Form Control Arrays and Event-2
- Access Form Control Arrays and Event-3
- WithEvents in Class Module for Sub-Form TextBox Events
- WithEvents in Class Module and Data Entry
- WithEvents and Access Report Event Sink
- WithEvents and Report Lines Hiding
- WithEvents and Report Lines Highlighting
- Withevents TextBox and Command Button Arrays
- Withevents TextBox CommandButton Dictionary
- Withevents and all Form Control Types
