Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

WithEvents Ms-Access Class Module Tutorial


Introduction

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:


Built-in and User-Defined Events

  1. Built-in Events invoked from Controls on Form/Report can be captured in stand-alone Class Module  and execute Code there, to do whatever you would like to do on 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 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.


WithEvents, Event, RaiseEvent Statements

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.


Demo Run of User-Defined Events

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 < 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
    
  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()
  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 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.

Form2 Class Module Code

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.



How it Works?

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, states that whatever action transmitted from Form2 (from frm Property) will be received in Form1 and executed the 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 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 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 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.


Demo Run

  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.

  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
Share:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...

Labels

Blog Archive

Recent Posts