WithEvents and Defining your own Events ~ LEARN MS-ACCESS TIPS AND TRICKS

Thursday, April 25, 2019

WithEvents and Defining your own Events

Introduction

I hope you have reviewed last week’s introduction to WithEvents, Event, and RaiseEvent, experimented with the sample forms, and understood how the VBA code in both forms interacts with each other. This time, we will try a similar example with one form and a class module, and see what changes are needed in the class module to capture events from the form module.

From this point onwards, we will use the form and class module combination to capture built-in events from the form and execute the required code within the class module.

Our ultimate goal is to capture all the commonly used built-in events raised by various controls on a form—such as Command Buttons, Text Boxes, Combo Boxes, List Boxes, Option Group buttons, and others—and handle them using VBA code in a class module, an array of class modules, or a collection object. This approach requires only a few lines of code in the form module to pass the form object reference to the class module, allowing the event-handling code to be written in the class module instead of directly in the form module.

There’s a long way to go from here, and I hope you will follow each week’s posts and try out the sample exercises provided to track the progressive changes in the code and understand their relevance at each stage.

If you are not yet familiar with class modules, I recommend starting with the earlier articles, beginning with “MS Access Class Module and VBA.”

Last week’s example was simply a demonstration of user-defined events and how to capture them in the target module. The RaiseEvent action was triggered from within a built-in event procedure: Qty_AfterUpdate().

A re-run of last week's Demo with a change.

We will run last week’s example here one more time, with some changes in the setup of related Objects.

Example 2: In this demo, we will use a single form (Form_Form3Custom) and a class module (ClsCustomEvent). The event will be raised from the form, captured in the class module, and the required code will be executed from there.

The sample Form: Form3Custom image is given below:

Right-click to open Large Image in New Tab/Window.

Create a Form with the name Form3Custom with the following Controls. Copy and Paste the VBA Code given below into the Code Module of the Form.

The following Controls are on the Form.

Text Box Name: Qty
Command Button Name: cmdClose
The label above the Text Box. – Form heading.

Form Module Code.

VBA Code behind Form3Custom is given below:

Option Compare Database
Option Explicit

Public ofrm As ClsCustomEvent

Public Event QtyLess(mQty As Single)
Public Event QtyMore(mQty As Single)
Public Event Closing()

Private Sub Form_Open(Cancel As Integer)
  Set ofrm = New ClsCustomEvent
  Set ofrm.mfrm = Me
End Sub

Private Sub Qty_AfterUpdate()
  If Qty < 1 Then RaiseEvent QtyLess(Qty)
  If Qty > 5 Then RaiseEvent QtyMore(Qty)
End Sub

Private Sub cmdClose_Click()
  RaiseEvent Closing
  DoCmd.Close
End Sub

Create a Class Module

Create a Class Module with the Name: ClsCustomEvent

Copy and paste the VBA Code given below into the Class Module, save, and compile the database to make sure that no errors are encountered during compilation.

Option Compare Database
Option Explicit

Private WithEvents frm As Form_Form3Custom

Public Property Get mfrm() As Form_Form3Custom
  Set mfrm = frm
End Property

Public Property Set mfrm(ByRef obj As Form_Form3Custom)
  Set frm = obj
End Property

Private Sub frm_QtyLess(Q As Single)
Dim msg As String
    msg = "Order Quantity < 1 is Invalid. " & Q
    MsgBox msg, vbInformation, "ClsCustomEvent"
End Sub

Private Sub frm_QtyMore(ByRef Q As Single)
Dim msg As String
    msg = "Quantity: [ " & Q & " ] is above Order Limit 5."
    MsgBox msg, vbInformation, "ClsCustomEvent"
End Sub

Private Sub frm_Closing()
  MsgBox "Form will be Closed Now!"
End Sub

Testing the User-Defined Events

Open Form3Custom in Normal View.
Enter a value greater than 5 into the TextBox and press the Tab Key. If everything went well, you will see an error message.
Try entering a negative value (say –2) into the TextBox and press Tab-Key. An error message will be displayed from the Class Module.
If you enter any value in the range of 1 to 5, then no error message will appear.
- Note: Take a closer Look at the ClsCustomEvent VBA Code.
- In the Global declaration area, the Form Object is declared as Private WithEvents frm as Form_Form3Custom (the Form’s specific Class Module Name) rather than the normal declaration Private WithEvents frm as 'Access.Form'.
- The Property Get and Set Property Procedures also use the same Object Type declarations as Form_Form3Custom. The specific form module name is used as the source from which the custom events are invoked.
- In the Custom Event Procedures: QtyLess(), QtyMore(), and the parameter type declaration is ByRef and not ByVal.

Important Points to Note.

When you try to do something of this kind in your own Project, keep these points in mind; otherwise, it will not work.

Now, we will try capturing built-in events—such as AfterUpdate or Click—from form controls in a class module, and execute the appropriate code for validation checks, calculations, and other tasks, instead of running them within the form’s class module.

For built-in Events on Form, we don't have to define Event and RaiseEvent statements on Form.

However, the WithEvents declaration is required in a class module to capture events from form controls such as text boxes, command buttons, combo boxes, list boxes, and others.

Built-in Event Capturing in Class Module.

With this background knowledge, we will now use a form—similar to Form2 from the earlier example—with a few modifications, along with a class module to capture the built-in events.

An Image of the sample Form is given below:

Right-click to open Large Image in New Tab/Window.

One TextBox and two Command Buttons are on the Form. The Command Button with the caption Exit closes the Form. The Label at the top is for information purposes only.

The Control names are given below:

Text Box Name: Text1
Command Button: cmdRaise
Command Button 2: cmdClose
Top Label for information only

The value entered in the TextBox is validated in the AfterUpdate built-in event, and a message is displayed. The acceptable range of valid values is from 1 to 5. Any value outside this range will trigger an error message.

The command button immediately below the text box, when clicked, displays the current value in the text box.
The bottom command button, when clicked, displays a message indicating that the form is closing.

These actions are not handled in the form’s code module; instead, they are captured and executed in the class module.

Form Module Code

The VBA Code behind the Form’s (ClassTestForm) Module is given below.

Option Compare Database
Option Explicit

Dim m_obj As New ClsEventTest

Private Sub Form_Load()
Set m_obj.mFrm = Me
End Sub

Private Sub Text1_AfterUpdate()
  'comment
End Sub

Private Sub cmdRaise_Click()
   'comment
End Sub

Private Sub cmdClose_Click()
  'comment
End Sub

The Dim statement at the top declares an instance of the class module ClsEventTest with the object name m_obj.

Within the Form_Load() event procedure, the current form object is passed to the class module object’s m_obj.mFrm property. In other words, the current form object is assigned to the class module object Instance m_obj.

The Text1_AfterUpdate(), cmdRaise_Click(), and cmdClose_Click() event procedures serve only as placeholders and contain no executable VBA code. A comment line is added inside each procedure to prevent the compiler from removing these empty event procedures.

These empty event procedures must currently exist in the form’s module to trigger the events and allow them to be captured in the class module object, where the actual code is executed. Although we can remove them later with some modifications in the class module, for now, we’ll proceed one step at a time.

This approach works in a way similar to the RaiseEvent action used in our earlier user-defined event procedure.

Likewise, the built-in AfterUpdate event of the TextBox (triggered when you enter a value and press the Tab key) is captured in the class module, and the corresponding VBA code is executed there.

The TextBox also supports other events, such as BeforeUpdate, LostFocus, GotFocus, and more. To capture these events in the class module as well, their corresponding empty event procedures must exist in the form’s module, while the actual handling code should be written in the class module’s subroutines.

Naturally, a question may arise: if these empty event procedures are mandatory (at this stage, yes) in the form’s module, why not just write the entire code there? If this thought crosses your mind, it means you’re on the right track to understanding this technique. We will soon explore a way to eliminate these empty procedures from the form module altogether.

**The Class Module: ClsEventTest Code**

Option Compare Database
Option Explicit

Private WithEvents frm As Access.Form
Private WithEvents txt As TextBox
Private WithEvents btn As CommandButton
Private WithEvents btnClose As CommandButton

Public Property Get mFrm() As Access.Form
  Set mFrm = frm
End Property

Public Property Set mFrm(ByRef vNewValue As Access.Form)
  Set frm = vNewValue
  Call class_init
 
End Property

Private Sub class_init()
'btn object in global declaration area
'is initialized with form Command Button cmdRaise
  Set btn = frm.Controls("cmdRaise")
  
'txt Object is initialized with Form Text1 TextBox
  Set txt = frm.Controls("Text1")

'like btn, btnClose Object is initialized
  Set btnClose = frm.Controls("cmdClose")

End Sub


’Event Handling section

Private Sub btn_Click()

   MsgBox "Current Value: " & Nz(txt.Value, 0), , "btn_Click()"

End Sub

Private Sub txt_AfterUpdate()
Dim lngVal As Long, msg As String
lngVal = Nz(txt.Value, 0) 'Text1 TextBox value

msg = "Order Qty [ " & lngVal & " ] Valid." ‘default message
'perform validation check
Select Case lngVal
    Case Is < 1
       msg = "Quantity <1 is Invalid: " & lngVal
    Case Is > 5
       msg = "Quantity [ “ & lngval & “ ] > Order Limit 5."
End Select
   MsgBox msg, vbInformation, "txt_AfterUpdate()"
End Sub

Private Sub btnclose_Click()
  MsgBox "Form: " & frm.Name & " will be closed."
  DoCmd.Close acForm, frm.Name
End Sub

Private Sub class_terminate()
  Set txt = Nothing
  Set btnClose = Nothing
  Set btn = Nothing
  Set frm = Nothing
End Sub

Class Module VBA Code Line by Line

Let us check what we have in the above Class Module.

In the Global declaration Area of the Module, four Object variables are declared with the WithEvents statement.

In the first line, a Form Object named frm. This Object will be assigned to the Form Object ClassTestForm (or any other form that uses this Class Module), on the Form_Load() Event of the Form.

One TextBox and two Command Button Controls are declared, with the WithEvents statement, in the Global Area of the Class Module.

These controls will be assigned references to the TextBox and Command Button controls on the form.

The Public Property Set mFrm() procedure accepts the form object passed from the form’s module.

The Class_Init() subroutine (not to be confused with Class_Initialize()) is called from within the Set procedure and initializes the TextBox and Command Button controls by linking them to the corresponding controls on the form.

For example, the statement:

Set btn = frm.Controls("cmdRaise")

sets a reference to the Command Button control named cmdRaise on the form object frm.

Similarly, the remaining statements in the Init() procedure set references to the other controls (declared with WithEvents), such as Text1 and btnClose, on the same form.

Try out the sample Form and Code.

In next week’s post, we will explore how to remove the empty event procedures from the form’s code module.

LEARN MS-ACCESS TIPS AND TRICKS

Thursday, April 25, 2019