Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

WithEvents Ms-Access Class Module Tutorial


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.

  1. 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.

  2. 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:

  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 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 < 1 Then
         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. A sample image of Form2 is given below:

  11. Create a Text Box on the Form and change its 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

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.

  1. Open Form1.

  2. Click on the Command Button.  Form2 is instantiated in memory and made 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 TextBox 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 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.

  5. 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.

More on this next week.

Links to WithEvents ...Tutorials.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. WithEvents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink
  10. WithEvents and Report Lines Hiding
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types

No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code