Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

WithEvents Button Combo List Textbox Tab

Introduction

If you know how to program one control on the Form to capture the built-in Event in  Class Module then you know how to do it for all of them.  Last week we have used one Text box and two Command Buttons for our introductory  trial runs. 

Links of Last two Articles on this subject is given below, in case if you would like to refresh your memory:

Now, we know how to create User-Defined Events on Form and what it takes to invoke the Event from Form Module and capture it in Class Module based Program. 

We had sample trial runs on capturing built-in Events of Command Button Clicks and Text Box’s AfterUpdate Events in Class Module.  Even though we don’t write any Code within the Event Procedure on the Form Module we had to keep the empty Procedure lines intact on the Form Module in order to capture Event in the Class Module, when it happens on the Form. 

Now, we will include few other commonly used Form Controls on our sample form to try out to see how it works.  We will include the user-defined Events, which we tried out one week back, also in this trial run.

The image of the Form in normal view is given below.


Capturing Events from Different type of Controls

Following are the list of Controls on the Form: ClsTestForm1

  1. Two Command Buttons
    • cmdRaise:  Click Event - Runs a Mind-Reading Game from Standard Module
    • cmdClose: Click Event – Closes the Form
  2. Combo Box – cboWeek: Click Event – Displays selected Day of Week
  3. List Box – lstMonth:  Click Event – Displays selected Month’s current date’s Day
  4. Text Box – Text1:
    •   AfterUpdate Event – Raises User Defined Events: QtyLess() & QtyMore()
    •   LostFocus Event – If TextBox is empty it displays a message.
  5. Tab Control – TabCtl9Change Event – TabControl Page Change Event.

The Form Class Module Code

ClsTestForm1 Class Module Code is given below:

Option Compare Database
Option Explicit

Private myFrm As New ClsEvent1
Public Event QtyLess(X As Long)
Public Event QtyMore(X As Long)
Public Event TbPage0(ByVal pageName As String)
Public Event TbPage1(ByVal pageName As String)

'Keep the comment line within the Event Procedures
'Otherwise compiler will clear the Empty Event Procedures

Private Sub cboWeek_Click()
  'comment
End Sub

Private Sub cmdClose_Click()
'comment
End Sub

Private Sub cmdRaise_Click()
    'comment
End Sub

Private Sub Form_Load()
   Set myFrm.frmMain = Me
End Sub

Private Sub lstMonth_Click()
  'comment
End Sub

Private Sub TabCtl9_Change()
Dim strName As String

If TabCtl9.Value = 0 Then
   strName = TabCtl9.Pages(0).Name
   RaiseEvent TbPage0(strName)
Else
   strName = TabCtl9.Pages(1).Name
   RaiseEvent TbPage1(strName)
End If

End Sub

Private Sub Text1_AfterUpdate()
'Userdefined Events
Dim q As Long
  q = Nz(Me!Text1, 0)
  If q < 1 Then
     RaiseEvent QtyLess(q)
  End If
  If q > 5 Then
     RaiseEvent QtyMore(q)
  End If
End Sub

Private Sub Text1_LostFocus()
'cmnt
End Sub

VBA Code Line by Line

The Class Module Object clsEvent1 is instantiated in myFrm Object Variable.

Next two lines in the Global declaration area defines four User-Defined Events: QtyLess() and QtyMore(), TbPage0() and TbPage1().  The first two Events will be Raised based on the Value entered into the Text Box and the Other two Raised on TabCtl9_Change() Event.

On the Form_Load() Event Procedure the current Form Object is passed to the Property Procedure by the statement Set myFrm.frmMain = Me.

The Text1_AfterUpdate() Event Procedure tests the entered Value in the Text1 Text Box,  validated and if the value doesn't fall within the valid range then one of the User-Defined Event is raised.

For the Tab Control we have defined two User-Defined Events to capture the change of Tab Control Pages.  When you make a page active one of the Event related to that Page is Raised.  For example: when you make the first TabPage active the user-defined Event TbPage0() is Raised and captured in the Class Module.

Other blank Event Procedures are place holders for invoking the respective built-in Events and capturing them in the Class Module Object to take appropriate action.


The Class Module ClsEvent1

The Class Module ClsEvent1 VBA Code is given below:

Option Compare Database Option Explicit Private WithEvents frm As Form_clsTestForm1 Private WithEvents btn1 As commandbutton Private WithEvents btn2 As commandbutton Private WithEvents txt As TextBox Private WithEvents cbo As ComboBox Private WithEvents lst As ListBox Public Property Get frmMain() As Form_clsTestForm1 Set frmMain = frm End Property Public Property Set frmMain(ByRef mfrm As Form_clsTestForm1) Set frm = mfrm Call class_init End Property Private Sub class_init() 'Set control Form Control references 'to trap Events from Form Set btn1 = frm.Controls("cmdRaise") Set btn2 = frm.Controls("cmdClose") Set txt = frm.Controls("Text1") Set cbo = frm.Controls("cboWeek") Set lst = frm.Controls("lstMonth") End Sub Private Sub btn1_Click() Call MindGame 'this program is on Standard Module End Sub Private Sub btn2_Click() MsgBox "Form will be Closed now!", , "btn2_Click()" DoCmd.Close acForm, frm.Name End Sub Private Sub txt_LostFocus() Dim txtval As Variant txtval = Nz(txt.Value, 0) If txtval = 0 Then MsgBox "Enter some number in this field: " & txtval End If End Sub Private Sub frm_QtyLess(V As Long) MsgBox "Order Quantity cannot be less than 1" End Sub Private Sub frm_QtyMore(V As Long) MsgBox "Order Qty [ " & V & " ] exceeds Maximum Allowed Qty: 5" End Sub Private Sub cbo_Click() MsgBox "You have selected: " & UCase(cbo.Value) End Sub Private Sub frm_TbPage0(ByVal tbPage As String) MsgBox "TabCtl9 " & tbPage & " is Active." End Sub Private Sub frm_TbPage1(ByVal tbPage As String) MsgBox "TabCtl9 " & tbPage & " is Active." End Sub Private Sub lst_Click() Dim m As String, t As String Dim S As String m = lst.Value t = Day(Date) & "-" & m & "-" & Year(Date) S = Choose(Weekday(DateValue(t)), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") MsgBox "Date: " & t & vbCr & " Day: " & UCase(S) End Sub Private Sub Class_Terminate() Set frm = Nothing Set btn1 = Nothing

Set btn2 = Nothing Set txt = Nothing Set cbo = Nothing Set lst = Nothing End Sub


Class Module ClsEvent1 Code Line by Line

In the Class Module Global Declaration area the Form Object frm and other Controls on the Form are declared with the WithEvents Key word to capture the built-in Event when invoked from the Form clsTestForm1 control.  The Events can be either built-in or User-Defined.

In the Form_Load() Event Procedure the Form Object is passed to the class Module clsEvent1 Property Procedure frmMain(), as Parameter.

From within this Property Procedure the Class_Init() sub-routine is called to set the Form Controls references, with their Name Property Values, to the Control Objects declared with the key word WithEvents in the Global declaration area.

Note: For the User-Defined Events on the Form needs only the Form object frm in the Class Module clsEvent1 to capture the Events Raised on the Form. Hence, we have not declared the Tab Control Object in the declaration area with the Key word WithEvents.


User Defined Event's Sub-Routine in Class Module.

Check the sub-routine names how they are declared.

In the Private Sub btn1_Click() sub-routine name btn1 object holds the reference to the Command Button Name cmdRaise and the Event Click when invoked on the clsTestForm1 Form it will be captured in the sub-routine and runs the Code within the procedure. The Event Procedure name have two parts, the object name (declared object name btn1 in Class Module) and Event Name(Click invoked on Form) both separated with an underscore character(_).

For all User-Defined Events, like QtyLess(), Raised on the Form needs only the frm Object with the WithEvents declaration.  The specific Form Module Name -  Form_clsTestForm1  required rather than the general type declaration Access.Form, to capture them in the Class Module clsEvent1, with the sub-routine name like frm_QtyLess().  If Property Procedure exists for the frm (if it is declared as Private) then the Property Procedure Parameter type also must be the specific Form’s Class Module Name Form_clsTestForm1.

Command Button cmdRaise have several built-in Events; Click, MouseMove, MouseDown, MouseUp etc. but here we will be capturing only the Click Event.

Same way btn2 (cmdClose) – Command Button2, cbo (cboWeek) - Combo Box, and lst (lstMonth) - List Box Click Event Procedures are captured in Class Module.

The TextBox Text1 have several built-in Events, like BeforeUpdate, AfterUpdate, LostFocus, GotFocus, but here we are capturing the LostFocus and two User-defined Events invoked from the AfterUpdate Event.

The Tab Control Page doesn’t fire the Click Event. That doesn’t mean that we cannot click on the Tab Page and use that event to run the required Code to do what we intend to do.


VBA Code for the Tab Control Page Click Event

Remember the following two points if you are trying to write Code For Tab Page Click Event:

1.  Clicking on the Active Page (the page that is visible) doesn’t fire any Event.

2. Clicking on the inactive Page Changes that page active and the Change Event fires.

It simply means that instead of insisting on Click Event to fire use the TabCtl_Change() Event to write your Code.  You can read the TabCtl.Value to know the active Tab Page’s index number.  The TabCtl.Pages(Index).Name will give you the active page’s visible Name.

We have two User-Defined Events: TbPage0() and TbPage1() on the Form.  These will be invoked from within the TabControl Change Event on the Form.


Removing Empty Event Procedure from Form Module

Last Week I have promised to show you as how to eliminate the empty (Event Procedures without any executable code in it) procedures from the Form Module.  In the above Form also we kept those empty procedure to fire the built-in Event Procedures and capture them in Class Module.

We can do this by adding few lines of Code in the Class_Init() Sub-Routine.  That Sub-routine with the required changes is given below.  These lines will invoke the built-in Event Procedure from the Form.

Private Sub class_init()
  'Set control Form Control references
  'Set up Event Procedures to invoke

  Set btn1 = frm.Controls("cmdRaise")
      btn1.OnClick = "[Event Procedure]"
  Set btn2 = frm.Controls("cmdClose")
      btn2.OnClick = "[Event Procedure]"
  Set txt = frm.Controls("Text1")
      txt.OnLostFocus = "[Event Procedure]"
  Set cbo = frm.Controls("cboWeek")
      cbo.OnClick = "[Event Procedure]"
  Set lst = frm.Controls("lstMonth")
      lst.OnClick = "[Event Procedure]"
End Sub

You may download the Demo Database with all the objects from the link given at the end of this page.

A New Form, clsTestForm1_New and Class Module clsEvent1_New with changed Code is given in the Demo Database.

Revised Form Module Code

The changed VBA Code in Form’s Class Module is given below:

Option Compare Database
Option Explicit

Private myFrm As New ClsEvent1_New
Public Event QtyLess(X As Long)
Public Event QtyMore(X As Long)
Public Event TbPage0(ByVal pageName As String)
Public Event TbPage1(ByVal pageName As String)

'Keep the comment line within the Event Procedures
'Otherwise compiler will clear the Empty Event Procedures
Private Sub Form_Load()
   Set myFrm.frmMain = Me
End Sub

Private Sub TabCtl9_Change()
Dim strName As String

If TabCtl9.Value = 0 Then
   strName = TabCtl9.Pages(0).Name
   RaiseEvent TbPage0(strName)
Else
   strName = TabCtl9.Pages(1).Name
   RaiseEvent TbPage1(strName)
End If

End Sub

Private Sub Text1_AfterUpdate()
'Userdefined Events
Dim q As Long
  q = Nz(Me!Text1, 0)
  If q < 1 Then
     RaiseEvent QtyLess(q)
  End If
  If q > 5 Then
     RaiseEvent QtyMore(q)
  End If
End Sub

Revised Class Module ClsEvent1

The changed Class Module (ClsEvent1_New) Code is given below:

Option Compare Database
Option Explicit

Private WithEvents frm As Form_clsTestForm1_New
Private WithEvents btn1 As commandbutton
Private WithEvents btn2 As commandbutton
Private WithEvents txt As TextBox
Private WithEvents cbo As ComboBox
Private WithEvents lst As ListBox

Public Property Get frmMain() As Form_clsTestForm1_New
    Set frmMain = frm
End Property

Public Property Set frmMain(ByRef mfrm As Form_clsTestForm1_New)
  Set frm = mfrm
  Call class_init
End Property

Private Sub class_init()

'Set control Form Control references
'to trap Events from Form
  Set btn1 = frm.Controls("cmdRaise")
      btn1.OnClick = "[Event Procedure]"
  Set btn2 = frm.Controls("cmdClose")
      btn2.OnClick = "[Event Procedure]"
  Set txt = frm.Controls("Text1")
      txt.OnLostFocus = "[Event Procedure]"
  Set cbo = frm.Controls("cboWeek")
      cbo.OnClick = "[Event Procedure]"
  Set lst = frm.Controls("lstMonth")
      lst.OnClick = "[Event Procedure]"
End Sub

Private Sub btn1_Click()
  Call MindGame 'this program is on Standard Module
End Sub

Private Sub btn2_Click()
    MsgBox "Form will be Closed now!", , "btn2_Click()"
    DoCmd.Close acForm, frm.Name
End Sub

Private Sub txt_LostFocus()
Dim txtval As Variant
txtval = Nz(txt.Value, 0)
If txtval = 0 Then
  MsgBox "Enter some number in this field: " & txtval
End If
End Sub

Private Sub frm_QtyLess(V As Long)
   MsgBox "Order Quantity cannot be less than 1"
End Sub

Private Sub frm_QtyMore(V As Long)
   MsgBox "Order Qty [ " & V & " ] exceeds Maximum Allowed Qty: 5"
End Sub

Private Sub cbo_Click()
   MsgBox "You have selected: " & UCase(cbo.Value)
End Sub

Private Sub frm_TbPage0(ByVal tbPage As String)
   MsgBox "TabCtl9 " & tbPage & " is Active."
End Sub

Private Sub frm_TbPage1(ByVal tbPage As String)
   MsgBox "TabCtl9 " & tbPage & " is Active."
End Sub

Private Sub lst_Click()
Dim m As String, t As String
Dim S As String

m = lst.Value
t = Day(Date) & "-" & m & "-" & Year(Date)
S = Choose(Weekday(DateValue(t)), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
MsgBox "Date: " & t & vbCr & "  Day: " & UCase(S)

End Sub

Private Sub Class_Terminate()
  Set frm = Nothing
  Set btn = Nothing
  Set txt = Nothing
  Set cbo = Nothing
  Set lst = Nothing
End Sub

Check the Code closely and look for statements that reads Form Control Values into the Class Module.


Downloads

Download the sample demo database given below and try out the Forms and study the Code.

More exciting Events will take place 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:

WithEvents and Defining your own Events


Introduction

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

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

Our ultimate aim is to capture all the required built-in Events (frequently used) raised by all type of Objects (Command Button, Text Box, Combo Box, List Box or Option Group Buttons and several Objects of the same type) on the Form and write the required VBA Code in Class Module or Class Module Arrays or in Collection Object. It takes only few lines of Code on the Form Module to transfer the Form Object Reference to the Class Module and rest of the Code goes into the Class Module itself.

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 weeks progressive changes on 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 for the demonstration of User-Defined Events and capturing it in the target module.  The RaiseEvent action was executed from within a built-in Event Procedure: Qty_AfterUpdate().


Re-run of last week's Demo with change

We will run last week’s example here one more time, with some change in the set up 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 it 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.

  • TextBox Name : Qty
  • Command Button Name : cmdClose
  • Label above the Text Box. – for information purpose only.

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
  DoCmd.Close
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 Text Box 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 Text Box and press Tab Key.  An error message will be displayed from Class Module.
  4. If you enter any value in the range 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 normal declaration Private WithEvents frm as Access.Form
  • In the Property Get and Set Property Procedures are also uses 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() 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 Class Module and run appropriate Code for Validation Checks or Calculations etc., instead of running them behind the Form’s Class Module itself.

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.

Image of the sample Form is given below:


A Text Box  and two Command Buttons are there on the Form.  The Command Button at the bottom of the Form is for closing the Form. The Label at the top is for information purpose 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 Text Box 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 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 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 are 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 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 our RaiseEvent action we have used on 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 executed there.

The Text Box have 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 are 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 that if the empty Event Procedure lines are mandatory (at this stage:Yes) on the Form Module then why cann’t write the rest of the Code also there.  If such questions pops-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. 

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

Rest of the statements in this 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.


  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:

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:

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 How Tos Functions 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 Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database 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 Excel 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 function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...

Labels

Blog Archive

Recent Posts