WithEvents Ms-Access Class Module Tutorial ~ LEARN MS-ACCESS TIPS AND TRICKS

Monday, April 15, 2019

WithEvents Ms-Access Class Module Tutorial

Introduction

There are Class Modules on Forms and Reports. Almost all Controls on Form, like Command Buttons, TextBoxes, Combo Boxes, List Boxes, and others have Events Coding features that make Ms-Access a powerful Database Management System.

The TextBox has GotFocus, LostFocus, BeforeUpdate, AfterUpdate, and other Events and we write VBA Code in them to do various tasks, like data entry control, validation checks, data change update, and so on.

The OnClick Event Procedure of Command Button launches Forms or Reports or runs programs or Macros to do various tasks. In all these predefined events, we write code directly on the Form’s or Report’s Class Module.

But, we are going to do it differently this time by redirecting the Event handling somewhere else other than the Event invoking Class Module.

As far as Ms-Access is concerned, we can divide these procedures into two sections:

Built-in and User-Defined Events.

Built-in Events invoked from Controls on Form/Report can be captured in a stand-alone Class Module and execute Code there, to do whatever you would like to do in that Event, instead of writing code directly on the Form/Report Module.
Besides that, we can define our own Custom Events on Form/Report Modules and capture the Event, either in any other Form’s Class Module or in an independent Class Module Object. Write the required code on the target module to handle whatever action is needed for the Form. This will need only a line of code in the built-in Event Procedure, to transmit the Event to the target location, where we can write code in action.

First, we will start, with the second option, defining Custom Events, and will learn how to invoke a Custom Event from one Form and capture it on another Form or in a Class Module Object, as it happens and run the required task there.

Why we take Custom Events first because it uses all the fundamental elements of this powerful programming feature. It uses a few basic statements (given below) and their placement on different Modules and the correct naming of Events on the Source and Target Modules are very important.

The WithEvents, Event, RaiseEvent Statements.

We will be trying out a simple example, but it is very important that you understand the placement of the key elements of Custom Event and how all of them are synchronized to work together.

--- 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 the built-in Events, like Button Clicks, Combo Box Clicks, etc., in the Class Module object are much simpler than defining Custom Event on one Class Module and capturing it from another Class Module.

Events and Event-trapping is strictly a business involving Class Modules only, and cannot be done on Standard Modules. But, you can call Sub-Routines/Functions from Standard Module from Class Module, if needed.

Demo Run of User-Defined Events.

Let us try an example with two simple Forms, with a TextBox and a Command Button. With this trial run, I am sure that you will know the basics of this procedure. The sample design of Form1 is given below:

Create a new Form with the name 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 Value also 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 Sub

Create 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 WithEvents capturing feature. Events originating from Form2 Class Module are captured here in 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 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.

What happens in form2 and in Form1, the chain of action path is depicted in a diagram given below. You may use it as a guide when you try out something on your own ideas.

How does it Work?

It works like a Radio Transmitter and Receiver, tuning to the frequency, like setup,.

The global declaration Public WithEvents frm as Form_Form2 on Form1’s VBA Module is like a Radio Receiver stating that whatever action transmitted from Form2 (from 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 TextBox as the parameter.

The transmission takes place only when you call the RaiseEvent Statement and it fires the Event declared at the Module level of Class or Form or Report, with parameter value (if defined), like RaiseEvent QtyUpdate(Me!Qty).

The same name QtyUpdate() is a Subroutine Name – not to declare as 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 Parameter and displays a message based on the validity of value passed from Qty textbox on Form2.

The Sub-routine name is always prefixed with Form2’s Class Module instance Property name: frm and the sub-routine header line is written as Private Sub frm_QtyUpdate(sQty As Single), to tune into the correct frequency of transmission.

It is very important that the Sub-Routine Name (QtyUpdate) on the Event declaration on Form2 matches with the Sub-Routine Name on Form1. The Sub-routine name on Form1 will be prefixed with Form2’s instance Variable and an underscore, like frm_QtyUpdate(sQty As Single), as stated 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 it works.

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 with the name Qty (Quantity), the valid value range acceptable in the TextBox 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 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.
You may try out this by entering different values into the TextBox and by pressing Tab Key.
When you are ready to close Form2 click on the Command Button.

The formClose() Event is fired, and a message is displayed. The Label control Caption on Form1 is updated with the same message stating that Form2 is closed.

I am sure you understood how the whole thing works and try something similar in your own way. When in doubt, use this page as a reference point.

LEARN MS-ACCESS TIPS AND TRICKS

Monday, April 15, 2019