Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

WithEvents and Defining your own Events


Hope that you have gone through last week’s WithEvents, Event, and RaiseEvent introduction, tried out the sample Forms and understood how both Form-based VBA Code relates to each other.  We will try out the same example differently here, with one Form and a Class Module, and see what change is required in the Class Modules to capture Events from the Form Module.

From here on we will be using the Form, and Class Module combination for capturing built-in Events from Form and executing the required code in Class Module.

Our ultimate aim is to capture all the required built-in Events (frequently used) raised by all types of Objects (Command Button, Text Box, Combo Box, List Box, or Option Group Buttons, and several other Objects) on the Form and write the required VBA Code in Class Module, Class Module Arrays or in Collection Object. It takes only a few lines of code on the Form Module to transfer the Form Object Reference to the Class Module and the VBA can be written in the Class Module, rather than in the Form Module.

Between now and then it is a long way and I hope you will follow each week’s Posts and try out sample trial runs presented, to keep track of each week's progressive change on the Code, and understand their relevance at each stage.

If you are not familiar with Class Modules then please go through the earlier articles starting from Ms-Access Class Module and VBA.

So, last week’s example was only the demonstration of User-Defined Events and capturing them in the target module.  The RaiseEvent action was executed 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.

Example2:  In this Demo run, we will use one Form (Form_Form3Custom) and a Class Module: ClsCustomEvent. The event will be invoked from the Form and will capture in a Class Module and run the required Code from there.

The sample Form: Form3Custom image is given below:

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 there 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
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

  1. Open Form3Custom in Normal View.
  2. Enter a value greater than 5 into the TextBox and press Tab Key.  If everything went on well you will see an error message.
  3. Try entering a negative value (say –2) into the TextBox and press Tab-Key.  An error message will be displayed from the Class Module.
  4. If you enter any value in the range of 1 to 5 then no error message will appear.
  •  NoteTake 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_Form3CustomThe specific Form Module name is used from where the Custom-made Events are invoked.
  • In the Custom Event Procdures: QtyLess() and 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 how to capture the built-in Events, like AfterUpdate, Clicks from Form Controls into the Class Module, and run appropriate Codes for Validation Checks or Calculations, etc., instead of running them behind the Form’s Class Module.

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

But, the WithEvents definition is required in Class Module, where we capture the Events from Form Controls like Text Box, Command Button, Combobox, List Box, etc.

Built-in Event Capturing in Class Module.

With this background knowledge, we will use a Form, like Form2 used earlier, with few changes and a Class Module to capture the built-in Events.

An Image of the sample Form is given below:

A TextBox and two Command Buttons are there on the Form.  The Command Button at the bottom of the Form will close the Form. The Label at the top is for information purposes only.

The Control names are given below:

  1. Text Box Name: Text1
  2. Command Button: cmdRaise
  3. Command Button 2: cmdClose
  4. Top Label for information only

The Value entered into the TextBox is checked for validity, on the AfterUpdate built-in Event,  and a message is displayed.  The acceptable valid value range is 1 to 5 in the Text Box.  Any value outside this range displays 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 click informs that the Form is getting closed.

These actions are not happening in the Code Module of the Form, but in the Class Module where it captures the actions and executes the code 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, on the top, instantiates the Class Module Object 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 short, the current Form Object is passed to the Class Module Object instance m_obj.

Next, Text1_AfterUpdate(), cmdRaise_Click() and cmdClose_Click() Event Procedures are  place holders only and there is no VBA executable Code in them.  A Comment line is inserted within the procedure to prevent the Compiler from eliminating the empty Event Procedures. 

These empty Event Procedures must be present (at least for now) on the Form’s Module to trigger the Event and to capture the Event in the Class Module Object to run the Code there. But we can remove them with some changes in the Class Module. But for now one step at a time.

This is as good as the RaiseEvent action we have used in our User-Defined Event Procedure.

Likewise, the built-in AfterUpdate Event of the Text Box (enter a value in the text box and press Tab Key to trigger this event) is captured in Class Module, and VBA Code is executed there.

The TextBox has other Events too, like BeforeUpdate, LostFocus, GotFocus, and so on.  If these events are also to be captured in the Class Module then those Empty Procedures also must be enabled on the Form and write Code in the Class Module sub-routines to take care of these Events when triggered on the Form.

A question naturally pops up in one’s mind if the empty Event Procedure lines are mandatory (at this stage: Yes) on the Form Module then why can't write the rest of the Code also there.  If such questions pop up in your mind you are on the right track in learning this trick.  We will find a way to eliminate those empty procedures from the Form Module.

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 see 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 with the name frm.  This Object will be assigned with 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 set with the references of the Text Box and Command Button names on the Form.

The  Public Property Set mFrm() Procedure accepts the Form Object, assigned from the  Form Module.

The Class_Init() (this is not Class_Initialize()) subroutine is called from the Set Procedure and initializes the Text Box and Command Button Controls with these objects on the Form.

For example: the statement Set btn = frm.Controls("cmdRaise") sets a reference to the Command Button Control name ‘cmdRaise’ on the Form object frm.

The rest of the statements in the init() procedure also sets a reference to the other controls (WithEvents Text1, btnClose) on the Form too.

Try out the above Form and Class Module.

Next week we will see how to remove the Empty Event Procedures from the Form’s Code Module.

Links to WithEventsTutorials.

  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

1 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