MS-Access Class-Module Tutorial Index

We are planning a series of Tutorials on Microsoft Windows Tree View Control Programming in Microsoft Access and will publish the Series in the coming weeks.

In the meantime, I thought it was appropriate to organize the earlier Tutorial Links, on Class Module and Event Handling Programming Series, of Articles on a separate page, for easy access in published order.

This is helpful for those who would like to go through the Tutorials on a gradual progressive way of learning. Those tutorials started from the Basic-level and progressed through the more advanced stage of programming levels. The learning curve should start from the base level and go up to the top to understand the changes at each level. Once you are familiar with the Object or Control, you may continue to learn further by experimenting with them on your own Programs or Projects.

After all, there is more than one way to solve a problem in Computer Programming based on the programmer’s understanding of the Language, Skill, and Experience.  

CLASS MODULE TUTORIALS

1. MS-Access Class Module and VBA
2. MS-Access VBA Class Object and Arrays
3. MS-Access Base Class and Derived Objects
4. VBA-Base Class and Derived Object-2
5. Base Class and Derived Object Variants
6. MS-Access Recordset and Class Module
7. Access Class Module and Wrapper Classes
8. Wrapper Class Functionality

COLLECTION OBJECT

1. MS-Access and Collection Object Basics
2. MS-Access Class Module and Collection Objects
3. Table Records in Collection Object

DICTIONARY OBJECT

1. Dictionary Objects Basics
2. Dictionary Object Basics-2
3. Sorting Dictionary Object Keys and Items
4. Display Records from Dictionary
5. Add Class Objects as Dictionary Items
6. Update Class Object Dictionary Item

MS-ACCESS EVENT HANDLING TUTORIALS

1. Withevents MS-Access Class Module
2. Withevents and Defining Your Own Events
3. Withevents Combo List Textbox Tab
4. Access Form Control Arrays And Event
5. Access Form Control Arrays And Event-2
6. Access Form Control Arrays And Event-3
7. Withevents in Class Module for Sub-Form
8. Withevents in Class Module and Data
9. Withevents and Access Report Event Sink
10. Withevents and Report Line Hiding
11. Withevents and Report-line Highlighting
12. Withevents Texbox and Command Button
13. Withevents Textbox Command Button
14. Withevents and All Form Control Types

Download Android App MSA Guru Version of LEARN MS-ACCESS TIPS AND TRICKS, from the Google Play Store.

Download Link: MSA Guru  Size: 2.3MB

Read More

WithEvents Button Combo List Textbox Tab

Introduction

If you know how to write VBA Code for one control on the Form,  to capture the built-in Event in the Class Module, then you know how to write code for all controls.  Last week we have used one Text box and two Command Buttons for our introductory trial runs. 

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

• WithEvents Ms-Access Class Module Tutorial
• WithEvents and Defining Your Own Events

Now, we know how to create User-defined Events on a Form and what it takes to invoke the Event from the 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 the 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 the Event in the Class Module, when it happens on the Form. 

Now, we will include a 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 a normal view is given below.

Capturing Events from Different type of Controls

Following is 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 – TabCtl9 – Change 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 instantiated into myFrm Object Variable.

The next two lines in the Global declaration area define four User-Defined Events: QtyLess() and QtyMore(), TbPage0() and TbPage1().  The first two Events will be Raised based on the Value entered into the TextBox and the Other two Raised on the 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 Events will be 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 placeholders 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 Keyword to capture the built-in Event when invoked from the Form clsTestForm1 control.  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 a Parameter.

From within this Property Procedure, the Class_Init() subroutine is called to set the Form Controls references, with their Name Property Values, to the Control Objects declared with the keyword 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 Keyword WithEvents.

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

Check the sub-routine names and 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 has 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 need only the frm Object with the WithEvents declaration.  The specific Form Module Name -  Form_clsTestForm1 is 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 in 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 has several built-in Events; Click, MouseMove, MouseDown, MouseUp, etc., but here we will be capturing only the Click Event.

The 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 has several built-in Events, like BeforeUpdate, AfterUpdate, LostFocus, and 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 the 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 promised to show you how to eliminate the empty (Event Procedures without any executable code in them) procedures of the Form Module.  The above Form also kept those empty procedures to fire the built-in Event Procedures and capture them in the Class Module.

We can do this by adding a 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 are 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 read 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.

Download ClsEvent12007.zip

Download ClsEvent12003.zip

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

Read More

WithEvents Ms-Access Class Module Tutorial

Introduction

There are Class Modules on Forms and Reports.  Almost all Controls on the Form, like Command Buttons, text boxes, Combo Boxes, List Boxes, and others, have event 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 the 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. 

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 is very important.

The WithEvents, Event, and 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 is much simpler than defining a Custom Event on one Class Module and capturing it from another Class Module.

Events and Event-trapping are strictly a business involving Class Modules only, and cannot be done on Standard Modules.  But, you can call Sub-Routines/Functions from a Standard Module from a 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:

Right-click to open Large Image in New Tab/Window.

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

    

    Right-click to open Large Image in New Tab/Window.

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()
  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 Form1’s Class Module Code.

• The first line: Private WithEvents frm As Form_Form2 declares a Form Object Variable of Form2, enabled with the WithEvents capturing feature.  Events originating from the Form2 Class Module are captured here in the 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 the 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.

Right-click to open Large Image in New Tab/Window.

How does it work?

It works like a Radio Transmitter and Receiver, tuning to the frequency, like a 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 the 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 the 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 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 they work.

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 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 the 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 this out by entering different values into the TextBox and pressing the 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

Read More