WithEvents Button Combo List Textbox Tab ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

If you know how to write VBA code to capture a built-in event for one control on a form in a class module, you can apply the same approach to all controls. Last week, we used one TextBox 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:

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 a Class Module-based Program.

We conducted sample trial runs to capture built-in events, such as Command Button Click and TextBox AfterUpdate, in the class module. Although we did not write any code within the event procedures in the form module, we still had to keep the empty procedure definitions intact there to allow the events triggered on the form to be captured in the class module.

Now, we will add a few other commonly used form controls to our sample form to see how they work. We will also include the user-defined events we experimented with a week ago as part of this trial run.

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

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

Capturing Events from Different Types of Controls

Following is the list of Controls on the Form: ClsTestForm1

Two Command Buttons

cmdRaise: Click Event - Runs a Mind-Reading Game from Standard Module
cmdClose: Click Event – Closes the Form.

Combo Box – cboWeek: Click Event – Displays selected Day of Week.
List Box – lstMonth: Click Event – Displays the selected Month’s current date’s Day.
Text Box – Text1:

AfterUpdate Event – Raises User-Defined Events: QtyLess() & QtyMore()
LostFocus Event – If the TextBox is empty, it displays a message.

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 was 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, validates, 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 events 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: The User-Defined Events on the Form need 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() subroutine, the object btn1 holds a reference to the Command Button named cmdRaise. When the Click event is triggered on the clsTestForm1 form, it is captured by this subroutine, and the code within the procedure is executed. The event procedure name consists of two parts: the object name (the declared object btn1 in the class module) and the event name (Click, triggered on the form), separated by an underscore (_).

For all user-defined events, such as QtyLess() raised on the form, only the frm object with a WithEvents declaration is required. The specific form module name (Form_clsTestForm1) must be used instead of the general type declaration (Access.Form) to capture the event in the class module (clsEvent1) with a subroutine name like frm_QtyLess(). If a property procedure exists in the form and is declared as Private, the property procedure’s parameter type must also be the specific form’s class module name, Form_clsTestForm1.

The command button cmdRaise has several built-in events, including Click, MouseMove, MouseDown, MouseUp, and more. In this example, we will capture only the Click event.

Similarly, btn2 (cmdClose), cbo (cboWeek) — a combo box, and lst (lstMonth) — a list box, have their Click event procedures captured in the class module.

The TextBox Text1 also has multiple built-in events, such as BeforeUpdate, AfterUpdate, LostFocus, and GotFocus. Here, we are capturing LostFocus and two user-defined events invoked from the AfterUpdate event.

Although a tab control page does not fire a Click event by default, this does not prevent us from capturing clicks on the tab page and executing the required code to achieve our intended functionality.

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 visible page) doesn’t fire any event.

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

This means that instead of relying on the Click event to fire, you can use the TabCtl_Change() event to execute your code. By reading TabCtl.Value, you can determine the index number of the active tab page, and 'TabCtl.Pages(Index).Name' will give you the visible name of the active page.

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 how to eliminate the empty event procedures (those without any executable code) from the form module. The form we discussed above still included these empty procedures to trigger the built-in events and capture them in the class module.

We can remove them by adding a few lines of code to the Class_Init() subroutine. The updated subroutine, shown below, includes these changes, which cause the built-in event procedures to be invoked directly 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

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