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:
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
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 selected Month’s current date’s Day.
Text Box – Text1:
AfterUpdate Event – Raises User-Defined Events: QtyLess() & QtyMore()
LostFocus Event – If 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 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.
Links to WithEvents Tutorials.
- WithEvents Ms-Access Class Module Tutorial
- WithEvents and Defining Your Own Events
- withevents Button Combo List TextBox Tab
- Access Form Control Arrays and Event Capturing
- Access Form Control Arrays and Event-2
- Access Form Control Arrays and Event-3
- WithEvents in Class Module for Sub-Form TextBox Events
- WithEvents in Class Module and Data Entry
- WithEvents and Access Report Event Sink
- WithEvents and Report Lines Hiding
- WithEvents and Report Lines Highlighting
- Withevents TextBox and Command Button Arrays
- Withevents TextBox CommandButton Dictionary
- Withevents and all Form Control Types
No comments:
Post a Comment
Comments subject to moderation before publishing.