Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

WithEvents Ms-Access Class Module Tutorial

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

Text Box have GotFocus, LostFocus, BeforeUpdate, AfterUpdate  and other Events and we write VBA Code in them to do various tasks, like data entry control, validation checks or calculations on entered data and so on. 

The OnClick Event Procedure of Command Button launches Forms or Reports or Run 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 this procedures into two sections:

  1. Built-in Events invoked from Controls on Form/Report Class Module can be captured in other Class Module  and execute Code there, to do whatever you would like to do on that Event, instead of writing Code directly on the source Form/Report Module.
  2. Besides that, we can define our own Custom Events on Form/Report Modules and capture the event on other Form’s Class Module or in a Class Module Object.  Write required code on the target module to handle whatever action needed on 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 for action.

First we will start, with the second option, defining Custom Events and will learn as 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 the Custom Events first, because it uses all the fundamental elements of this  powerful programming feature.  It uses 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.

We will be trying out a simple example, but it is very important that you understand the key elements placement 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 coming from 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 Class Module object is much more simpler than to define Custom Event on one Class Module and capture it from another Class Module.

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

Let us try an example with two simple Forms, with a Text Box 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:

  1. Create a new Form with the name Form1 and open it in Design View.
  2. Insert a Command Button on the Detail Section of the Form.
  3. Display the Property Sheet (F4) and change the Name Property Value to cmdOpen.
  4. Change the Caption Value to Open Form2.
  5. Insert a Label Control above the Command Button and change the Name Property Value to Label1 and the Caption Value also Label1.
  6. Change the Form1’s Has Module Property value to Yes.
  7. Display the Module of Form1.
  8. 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 Msg = "Negative Quantity " & sQty & " Invalid."
    ElseIf sQty > 5 Then
    Msg = "Invalid Order Quantity: " & sQty
    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
  9. Create a second Form with the name Form2 and open it in Design View.
  10. Sample image of Form2 is given below:

  11. Create a Text Box on the Form and change it’s name property value to Qty.
  12. Change the child-label Caption to Order Qty (1 – 5):
  13. Create a Command Button below the Text Box and change the Name Property value to cmdClose and change the Caption Property value to Close.
  14. 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()
End Sub

Private Sub Form_Unload(Cancel As Integer)
RaiseEvent formClose("Form2 Closed")
End Sub

Let us take a closer look at the Form1’s Class Module Code. 

  • The first line: Private WithEvents frm Form_Form2 declares a Form Object Variable frm of  Form2, enabled with WithEvents trapping feature.  Events originating from Form2 Class Module is captured here 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 on the Database Window.
  • The Private Sub frm_QtyUpdate() Subroutine is executed when the Qty Text Box on Form2 is updated with a value, and from within the the AfterUpdate Event of the Text Box the RaiseEvent QtyUpdate() is executed.
  • The Private Sub frm_formClose() is executed when the Command Button on Form2 is Clicked to close Form2.

Now let us go through the Form2 Module’s Code.

  • In the global declaration area of the Module two Public Event Procedure names are declared with parameters.
  • When some value is entered into the Text Box 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 Text Box value is passed as parameter.
  • The frm_QtyUpdate() Sub-routine runs, from Form1 Class Module and performs validation checks on the passed value and displays an appropriate message.
  • When the Command Button, with the Caption Close, is clicked the formclose() Event is Raised and a message displayed, and the Label control on Form1 is updated with the same info.

What happens on form2 and 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.

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

The global declaration Public WithEvents frm as Form_Form2 on Form1’s VBA Module is like a Radio Receiver, states that whatever action transmitted from Form2 (from instance frm Variable) will be received in Form1 and executed. 

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 Text Box as 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 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 variable 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 on Event declaration on Form2 match with Sub-Routine Name on Form1.  The Sub-routine name on Form1 will be prefixed with the Form2’s instance Variable and an underscore frm_.

Note: It means that the actual Code, for the Event Procedure declaration done on Form2 Class Module, is written on Form1 Module, by addressing the subroutine directly with object name (frm_) prefix.

Armed with the above background information let us try out the Forms to see how it works.

  1. Open Form1.
  2. Click on the Command Button.  Form2 is instantiated in memory and made it visible.
  3. 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 Text Box is 1 to 5. 

    Any value outside this range is invalid and an appropriate error message is displayed.

  4. 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 Text Box Value as 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 Text Box and by pressing Tab Key.

  5. When you are ready to close Form2 click on the Command Button.
The formClose() Event is fired, 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 as how the whole thing works and try something similar on your own way.  When on doubt use this as a reference point.

More on this next week.

  • Dictionary Object Basics
  • Dictionary Object Basics-2
  • Sorting Dictionary Object Keys and Items
  • Display Records from Dictionary to Form
  • Add Class Objects as Dictionary Items
  • Update Class Object Dictionary Item on Form

This post first appeared on LEARN MS-ACCESS TIPS AND TRICKS, please read the originial post: here

Share the post

WithEvents Ms-Access Class Module Tutorial


Subscribe to Learn Ms-access Tips And Tricks

Get updates delivered right to your inbox!

Thank you for your subscription