LEARN MS-ACCESS TIPS AND TRICKS

WithEvents Button Combo List Textbox Tab

a.p.r. pillaiApril 30, 2019Class Module, Event, Forms, RaiseEvent, WithEvents No comments

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 CommandButton Click and TextBoxAfterUpdate, in the class module. Although we did not write any code in 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 in 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

The 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 TextBox, validates it, and if the value doesn't fall within the valid range, one of the User-Defined Events 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 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 standalone Class Module clsEvent1 to capture the Events Raised on the Form. Hence, we have not declared the Tab-Control Object in the global declaration area.

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

Check the subroutine 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) using 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 trying to capture 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 the 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. 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() for the Tab Control in 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 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.

Downloads

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

More exciting events are to take place next week.

Download ClsEvent12007.zip

Download ClsEvent12003.zip

Links to WithEvents Tutorials.

WithEvents and Defining your own Events

a.p.r. pillaiApril 25, 2019Class Module, Command Button, Controls, Event, WithEvents 1 comment

Defining Custom Event and Class Module.

I hope you have reviewed last week’s introduction to WithEvents, Event, and RaiseEvent, experimented with the sample forms, and understood how the VBA code in both forms interacts. This time, we will try a similar example with one form and a class module, and see what changes are needed in the class module to capture events from the form module.

From this point onwards, we will use the Form and Class Module combination to capture built-in Events from the Form and execute the required code within the Standalone Class Module.

Our ultimate goal is to capture all the commonly used built-in events raised by various controls on a form—such as Command Buttons, Text Boxes, Combo Boxes, List Boxes, Option Group buttons, and others—and handle them using VBA code in a Class Module, an array of class modules, or a collection object. This approach requires only a few lines of code in the form module to pass the form object reference to the class module, allowing the event-handling code to be written in the class module instead of directly in the Form Module.

There’s a long way to go from here, and I hope you will follow each week’s posts and try out the sample exercises provided to track the progressive changes in the code and understand their relevance at each stage.

If you are not yet familiar with class modules, I recommend starting with the earlier articles, beginning with “MS Access Class Module and VBA.”

Last week’s example was simply a demonstration of user-defined events and how to capture them in the target module. The RaiseEvent action was triggered from within a built-in event procedure: Qty_AfterUpdate().

A re-run of last week's Demo with a change.

We will run last week’s example here one more time, with some changes in the setup of related Objects.

Example 2: In this demo, we will use a single form (Form_Form3Custom) and a class module (ClsCustomEvent). The event will be raised from the form, captured in the Standalone Class Module, and the required code will be executed from there.

The sample Form: Form3Custom image is given below:

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

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

Text Box Name: Qty
Command Button Name: cmdClose
The label above the Text Box. – Form heading.

Form Module Code.

VBA Code behind Form3Custom is given below:

Option Compare Database
Option Explicit

Public ofrm As ClsCustomEvent

Public Event QtyLess(mQty As Single)
Public Event QtyMore(mQty As Single)
Public Event Closing()

Private Sub Form_Open(Cancel As Integer)
  Set ofrm = New ClsCustomEvent
  Set ofrm.mfrm = Me
End Sub

Private Sub Qty_AfterUpdate()
  If Qty < 1 Then RaiseEvent QtyLess(Qty)
  If Qty > 5 Then RaiseEvent QtyMore(Qty)
End Sub

Private Sub cmdClose_Click()
  RaiseEvent Closing
  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

Open Form3Custom in Normal View.
Enter a value greater than 5 into the TextBox and press the Tab Key. If everything went well, you will see an error message.
Try entering a negative value (say –2) into the TextBox and press the Tab key. An error message will be displayed from the Class Module.
If you enter any value in the range of 1 to 5, then no error message will appear.
- Note: Take a closer Look at the ClsCustomEvent VBA Code.
- In the Global declaration area, the Form Object is declared as Private WithEvents frm as Form_Form3Custom (the Form’s specific Class Module Name) rather than the normal declaration Private WithEvents frm as 'Access.Form'.
- The Property Get and Set Property Procedures also use the same Object Type declarations as Form_Form3Custom. The specific Form module name is used as the source of the Event firing.
- In the Custom Event Procedures: QtyLess(), 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 to capture built-in events—such as AfterUpdate or Click—from form controls in a class module, and execute the appropriate code for validation checks, calculations, and other tasks, rather than running them within the Form’s class module.

For built-in Events firing from Controls in a form, we don't need to define Event and RaiseEvent statements on the Form.

However, the WithEvents declaration is required in a class module to capture events from Form controls such as text boxes, command buttons, combo boxes, list boxes, and others.

Built-in Event Capturing in Class Module.

With this background knowledge, we will now use a form—similar to Form2 from the earlier example—with a few modifications, along with a class module to capture the built-in events.

An Image of the sample Form is given below:

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

One TextBox and two Command Buttons are on the Form. The Command Button with the caption Exit closes the Form. The Label at the top is for information purposes only.

The Control names are given below:

Text Box Name: Text1
Command Button: cmdRaise
Command Button 2: cmdClose
Top Label for information only

The value entered in the TextBox is validated in the AfterUpdate built-in event, and a message is displayed. The acceptable range of valid values is from 1 to 5. Any value outside this range will trigger an error message.

When the command button, immediately below the TextBox, is clicked, it displays the current value in the text box.
The bottom command button, when clicked, displays a message indicating that the Form is closing.

These actions are not handled in the form’s code module; instead, they are captured and executed 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 at the top declares an instance of the class module ClsEventTest with the object named 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 other words, the current form object is assigned to the class module object Instance m_obj.

The Text1_AfterUpdate(), cmdRaise_Click(), and cmdClose_Click() event procedures serve only as placeholders and contain no executable VBA code. A comment line is added inside each procedure to prevent the compiler from removing these empty event procedures.

These empty event procedures must currently exist in the form’s module to trigger the events and allow them to be captured in the class module object, where the actual code is executed. Although we can remove them later with some modifications in the class module, for now, we’ll proceed one step at a time.

This approach works similarly to the RaiseEvent action used in our earlier user-defined event procedure.

Likewise, the built-in AfterUpdate event of the TextBox (triggered when you enter a value and press the Tab key) is captured in the class module, and the corresponding VBA code is executed there.

The TextBox also supports other events, such as BeforeUpdate, LostFocus, GotFocus, and more. To capture these events in the class module as well, their corresponding empty event procedures must exist in the form’s module, while the actual handling code should be written in the class module’s subroutines.

Naturally, a question may arise: if these empty event procedures are mandatory (at this stage, yes) in the form’s module, why not just write the entire code there? If this thought crosses your mind, it means you’re on the right track to understanding this technique. We will soon explore a way to eliminate these empty procedures from the form module altogether.

**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 check what we have in the Class Module.

In the Global declaration Area of the Module, four Object variables are declared with the WithEvents statement.

In the first line, a Form Object named frm. This Object will be assigned to 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 assigned references to the TextBox and Command Button controls on the form.

The Public Property Set mFrm() procedure accepts the form object passed from the form’s module.

The Class_Init() subroutine (not to be confused with Class_Initialize()) is called from within the Set procedure and initializes the TextBox and Command Button controls by linking them to the corresponding controls on the form.

For example, the statement:

Set btn = frm.Controls("cmdRaise")

sets a reference to the Command Button control named cmdRaise on the form object frm.

Similarly, the remaining statements in the Init() procedure set references to the other controls (declared with WithEvents), such as Text1 and btnClose, on the same form.

Try out the sample Form and Code.

In next week’s post, we will explore how to remove the empty event procedures from the form’s code module.

Links to WithEvents Tutorials.

WithEvents Ms-Access Class Module Tutorial

a.p.r. pillaiApril 15, 2019Event, Form Instances, RaiseEvent, WithEvents No comments

Object Event, Event Firing, Capturing.

MS Access Forms and Reports each have their own Class Modules. Nearly all controls on a form—such as CommandButtons, TextBoxes, ComboBoxes, ListBoxes, and others—support event-driven programming, which is one of the key features that make a Microsoft Access Application a powerful Database Management System.

For example, the TextBox control provides several events, such as GotFocus, LostFocus, BeforeUpdate, and others. We typically write VBA code in these event procedures to perform tasks controlling data entry, validating data, or updating modified data.

Similarly, the OnClick event of a Command Button can be used to open forms or reports, or to run procedures or macros that perform specific tasks. Normally, code for such predefined events is written directly within the form’s or report’s class module.

This time, however, we will take a different approach — by redirecting event handling to an external location, instead of the normal procedure of writing Code directly in the Form's Class Module.

From MS Access’s perspective, these event procedures can be categorized into two groups: Built-in and User-Defined Events.

Built-in events triggered by controls on a form or report can be captured in a stand-alone class module, allowing you to execute code there to perform any desired actions, rather than writing the code directly in the form or report’s class module.
In addition, we can define our own custom events within form or report modules and capture them either in another form’s class module or in an independent class module object. The required code to handle the event can then be written in the target module to perform the necessary actions for the form. This approach only requires a single line of code in the built-in event procedure to transmit the event to the target location, where the actual handling logic can be implemented.

First, we will explore the second option — defining custom events — and learn how to invoke a custom event from one form and capture it in another form or a class module object as it occurs, then execute the required task there.

We are starting with custom events because they demonstrate all the fundamental elements of this powerful programming feature. This approach involves a few basic statements (listed below), their correct placement in different modules, and the proper naming of events in both the source and target modules, which is crucial for the process to work correctly.

The WithEvents, Event, and RaiseEvent Statements.

We will start with a simple example, but it is essential to understand the placement of the key elements of a custom event and how they work together in a synchronized manner.

--- 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 built-in events, such as button clicks or ComboBox selections, in a class module object is much simpler than defining a custom event in one class module and capturing it from another.

Events and event trapping are strictly handled through class modules and cannot be implemented in standard modules. However, you can still call subroutines or functions in Class Modules from a Standard module.

Demo Run of User-Defined Events.

Let’s try an example using two simple forms — each containing a TextBox and a Command Button. This trial run will help you understand the basic steps involved in this procedure. The sample design of Form1 is shown below:

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

Create a new Form named Form1 and open it in Design View.
Insert a Command Button on the Detail Section of the Form.
Display the Property Sheet (F4) and change the Name Property Value to cmdOpen.
Change the Caption Value to Open Form2.
Insert a Label Control above the Command Button and change the Name Property Value to Label1 and the Caption to Label1.
Change Form1’s Has Module Property value to Yes.
Display the Module of Form1.

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

Create a second Form with the name Form2 and open it in Design View.
A sample image of Form2 is given below:

Right-click to open Large Image in New Tab/Window.
Create a Text Box on the Form and change its Name property value to Qty.
Change the child-label Caption to Order Qty (1-5):
Create a Command Button below the Text Box and change the Name Property value to cmdClose and change the Caption Property value to Close.
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.
The sequence of actions between Form1 and Form2 is illustrated in the diagram below. You can use this as a guide when experimenting with your own examples.

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, states that whatever action is 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 starts only when you call the RaiseEvent Statement, and it fires the Event declared at the Class Module level of Form or Report, with parameter value (if defined).

Example: RaiseEvent QtyUpdate(Me!Qty).

The same name QtyUpdate() is a Subroutine Name – not to declare as a 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 a parameter and display a message based on the validity of the value passed from Qty textbox on Form2.

The subroutine name is always prefixed with Form2’s Class Module instance Property name: frm, and the subroutine header line is written as Private Sub frm_QtyUpdate(sQty As Single), to tune into the correct frequency of transmission.

The subroutine name (QtyUpdate) in the event declaration on Form2 must match the subroutine name on Form1. On Form1, the subroutine name will be prefixed with Form2’s instance variable and an underscore, for example: frm_QtyUpdate(sQty As Single), as described 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.

Open Form1.
Click on the Command Button. Form2 is instantiated in memory and made visible.
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 named Qty (Quantity); the valid value range is 1 to 5.
Any value outside this range is invalid, and an appropriate error message is displayed.
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.
Try this out by entering different values into the TextBox and pressing the Tab Key.
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 know how the whole thing works, and try something similar in your own way. When in doubt, use this page as a reference point.

Links to WithEvents ...Tutorials.

Update Class Object Dictionary Item through Form

a.p.r. pillaiMarch 29, 2019Add, Class Module, Collection Object, Dictionary Object, Item, Key, Objects 1 comment

Manage Dictionary Item Through Form.

Last week, we conducted a trial run to add and retrieve Class Objects from Dictionary Object Items.
One important point we emphasized was that each Class Object added to the Dictionary must be created as a new temporary instance of the Class Object every time it is added.

This practice is not exclusive to the Class or Dictionary objects; it is a general best practice that should be followed whenever adding objects to a Collection, Dictionary, or an Object Array, to prevent accidental overwriting of existing instances in memory. A brief review of the correct procedure is given below:

Create a temporary Object instance.
```
Set tmpObj = New Object
```
Assign values to the temporary object’s Properties.
Add the temporary object instance as an Item to the Dictionary, Collection, or as an element in the Object Array.
Release the temporary object instance from memory so that it can be reused:
```
Set tmpObj = Nothing
```

Repeat steps 1 to 4 for each new object you want to add.

If you would like to know why we can’t use only one instance of the Class Object to add several Items to the Dictionary Object, visit the page Add Class Object as Dictionary Object Items?

Class Object Instances as Dictionary Items

Now, let us get ourselves prepared for a trial run to add, edit, update, and delete Class Object Items in the Dictionary Object, through an MS-Access Form. We worked with the Form and Dictionary Object about two weeks back, by adding and retrieving simple items in the Dictionary Object.

This time, we are dealing with Objects and Properties in Dictionary Objects, not simple Items, with added data management functions. Besides that, when the Form is closed, the Dictionary Item Values (ClsArea Class Object Property Values) are saved to a Table.

We will use our simple ClsArea Class Object, with only three Property Values to fill in (Description, Length, and Width Values), for our trial run.

Even though the Table is used only at the end of the session, we will design a simple table first with the fields: Description (strDesc), Length (dblLength), and Width (dblWidth), matching the Class Module ClsArea Object Property Names in brackets.

Create a new Table with the name ClsArea with the Field Names and Field Types shown in the Table Design Image shown below. For Numeric Data Fields, select Double Precision Number Type.

Right-click to open Large Image in New Tab/Window.
Next, we need a Form with the following design to manage the data of the Dictionary Object.

Right-click to open Large Image in New Tab/Window.
Design a Form with the following Controls (not bound to the Table):
- Combo Box on the Header:
  Name Property Value: cboKey
  Row Source Type = Value List
- Four Unbound Text Boxes in the Detail Section of the Form with the following names:
  strDesc
  dblLength
  dblWidth
  Area
- Create another TextBox to the right of the last TextBox with the Name:
  ItemCount
- Create three Command Buttons below the Text Boxes, on the Detail Section, with the Names:
  cmdAdd and Caption: Add
  cmdEdit and Caption: Edit
  cmdDelete and Caption: Delete
- Create two Command Buttons in the Footer Section of the Form with the Names:
  cmdSave and Caption: Save to Table
  cmdExit and Caption: Exit
The Form’s Normal View Image, with some sample data, is given below:

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

As the Form control names indicate, you can enter the Property values of the ClsArea Class Object and add the Class Object as an Item to the Dictionary Object.
The value entered in the strDesc Property will be used as the Key for the Dictionary Item.

Important: Ensure that the Description (strDesc) values are unique for each item. If a duplicate key is entered, the Dictionary Object will reject it and trigger an error.
(Extensive validation checks have been intentionally omitted in the code to keep it simple.)

You can also retrieve a specific Class Object’s property values from the Dictionary by selecting its Key from the cboKey Combo Box, then:

Edit the values directly on the Form and update them back into the Dictionary, or
Delete the unwanted item from the Dictionary Object entirely.

The Form Module Event Procedures.

There are several Event Procedures defined in the Form’s Class Module.

A brief explanation of each procedure is provided below.

Option Compare Database
Option Explicit

Dim C As ClsArea, xC As ClsArea
Dim D As Object, Desc As String
Dim editFlag As Boolean, saveFlag As Boolean
Dim strV As String

All important Objects and Variables are declared in the Global declaration area.

Private Sub Form_Load()

Private Sub Form_Load()
    Set D = CreateObject(“Scripting.Dictionary)
    Me.cmdEdit.Enabled = False
    Me.cmdDelete.Enabled = False
    saveFlag = False
End Sub

At the Form Load Event, the Dictionary Object is instantiated.

Edit and Delete Command Buttons are disabled because the Form will be in data entry mode by default. The data save detection flag saveFlag is set to false. The flag will be set to True when the Dictionary Data is saved to the Table, from one of two possible selections of Command Buttons at the Footer of the Form.

Private Sub Form_Current()

Private Sub Form_Current()
Dim o As String
    o = cboKeys.RowSource
    If Len(o) = 0 Then
       Me.cboKeys.Enabled = False
    Else
       Me.cboKeys.Enabled = True
    End If
End Sub

Checks whether the Combo box's Row Source property has any Value in it (when the Form is open, it will be empty); if not, disable the Combo box Control.

Private Sub strDesc_GotFocus()

Private Sub strDesc_GotFocus()
 If editFlag Then
  Me.cmdAdd.Enabled = False
  Me.cmdEdit.Enabled = True
  Me.cmdDelete.Enabled = True
 Else
  Call initFields
  Me.cmdAdd.Enabled = True
  Me.cmdEdit.Enabled = False
  Me.cmdDelete.Enabled = False
  editFlag = False
 End If

End Sub

Checks whether the form is in Edit mode or not. Edit Mode Flag is set when the user clicks the Edit Command Button. This happens after the user selects an item from the Combobox.

If in edit mode, the data entry fields are not cleared, the Edit and Delete Command Buttons are enabled, and the Add Command Button is disabled.

If it is in Add mode, then data fields are emptied in preparation for a new Item, the Add Command Button is enabled, and the Edit and Delete Command Buttons are disabled.

Private Sub dblWidth_LostFocus()

Private Sub dblWidth_LostFocus()
'Area is displayed for info purpose only
  Me!Area = Nz(Me!dblLength, 0) * Nz(Me!dblWidth, 0)
End Sub

On the dblWidth Text Box's LostFocus Event, the product of dblLength * dblwidth is displayed in the Area Text Box.

Private Sub cmdAdd_Click()

Private Sub cmdAdd_Click()
'--------------------------------------------------
'1. Add Class Object as Item to Dictionary Object
'2. Update Combobox RowSource Property
'--------------------------------------------------
Dim tmpstrC As String, tmpLength As Double, tmpWidth As Double
Dim flag As Integer, msg As String
Dim cboVal As String, cbo As ComboBox

editFlag = False
Set cbo = Me.cboKeys

tmpstrC = Nz(Me!strDesc, "")
tmpLength = Nz(Me!dblLength, 0)
tmpWidth = Nz(Me!dblWidth, 0)

flag = 0
If Len(tmpstrC) = 0 Then flag = flag + 1
If tmpLength = 0 Then flag = flag + 1
If tmpWidth = 0 Then flag = flag + 1
If flag > 0 Then
   msg = "Invalid Data in one or more fields, correct them and retry."
   MsgBox msg, , "cmdAdd()"
   Exit Sub
End If

Desc = ""
Set C = New ClsArea 'create a new instance
'add Property Values
    C.strDesc = tmpstrC
    C.dblLength = tmpLength
    C.dblWidth = tmpWidth
    
'add Class Object instance to Dictionary
    
    D.Add tmpstrC, C 'Description is the Key
    
    Call comboUpdate(tmpstrC) 'update description as combobox item
       
    Me.ItemCount = D.Count 'display dictionary Items count
    
    'Call initFields 'set all fields to blanks
    Me.strDesc.SetFocus 'make description field current
    
'Clear Class Object
    Set C = Nothing

End Sub

The TextBox values are moved into temporary variables and checks to see whether any text box is empty or not.

The ClsArea Class Object Properties are assigned with strDesc, dblLength & dblwidth TextBox values from temporary variables.

Adds the Class Object to the Dictionary Object.

Updates the Combo Box with the Dictionary Object Key from the Class Object's Description (strDesc) Value.

The Dictionary Items Count is displayed on the ItemCount Text Box.

The focus is set in the strDesc field. The text boxes are cleared for entry of new values.

Private Sub cboKeys_AfterUpdate()

Private Sub cboKeys_AfterUpdate()
On Error Resume Next

strV = Me!cboKeys
Set xC = D(strV)
If Err > 0 Then
   MsgBox "Item for Key: " & strV & " Not Found!"
   Exit Sub
End If

Me!strDesc = xC.strDesc
Me!dblLength = xC.dblLength
Me!dblWidth = xC.dblWidth
Me!Area = xC.Area

Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True
Me.strDesc.Enabled = False

On Error GoTo 0

End Sub

Retrieving the Dictionary Item

After adding several items to the Dictionary Object, you can retrieve any item by selecting its Key from the Combo Box. The corresponding details will be displayed on the Form. In this mode, the Add command button and the strDesc field will remain disabled. The strDesc field will be unlocked only when you click the Edit button.

If you select an invalid Key (for example, a Key value that was changed during earlier edit operations) from the Combo Box, an error message — ‘Item for Key: XXXX not found’ — will be displayed, indicating that the specified item does not exist in the Dictionary Object.

Private Sub cmdEdit_Click()

Private Sub cmdEdit_Click()
'Edit the displayed item properties
Dim cap As String
Dim mDesc As String

editFlag = True

strV = Me!cboKeys

cap = Me.cmdEdit.Caption
Select Case cap
   Case "Edit"
      Me.strDesc.Enabled = True
      Me.cmdAdd.Enabled = False 'when editing Add button is disabled
      Me.cmdEdit.Caption = "Update" 'Edit Button Caption is changed
      
   Case "Update" 'Button clicked when Caption is Update
   'directly replace the property value in the Item
      xC.strDesc = Me!strDesc
      xC.dblLength = Me!dblLength
      xC.dblWidth = Me!dblWidth
      mDesc = Me!strDesc 'changed Description is copied to mDesc
      
   If mDesc <> strV Then 'checks with key in combobox value if not same then
      D.Key(strV) = mDesc 'Change Dictionary Key of Item
      
      'update new desc to Combobox
      'old desc also remains in combobox
      Call comboUpdate(mDesc)
   End If
      Call initFields
      Me.strDesc.SetFocus
      Me.cmdAdd.Enabled = True 'Enable Add button to add new item
      Me.cmdEdit.Caption = "Edit" 'change caption from Update to Edit
      Me.cmdEdit.Enabled = False 'disable Edit Button
      Me.cmdDelete.Enabled = False 'disable Edit Button
End Select

End Sub

Editing Dictionary Item

The item retrieved from the Dictionary and displayed on the Form can be edited by clicking the Edit command button. When you click it, the button’s caption changes to Update.

Make the required changes to the fields (including the Description, if needed), click the Update button again to save the changes back into the Dictionary Object.

If the strDesc value (the Dictionary Key) is modified, the corresponding old Key in the Dictionary is replaced with the new Description value. The Combo Box will also be updated to reflect the new Key, though the old Key appears in the Combo Box until it is refreshed.

After the update, the data fields are cleared, the Add button is enabled again, and the Edit and Delete buttons are disabled.

Private Sub cmdDelete_Click()

Private Sub cmdDelete_Click()p
Dim txtKey As String, msg As String

txtKey = Me!cboKeys
msg = "Delete Object with Key: " & txtKey & " ..?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbCritical) = vbYes Then
   D.Remove (txtKey) 'Remove the Item matching the Key
   
   MsgBox "Item Deleted."
   
   Call initFields
   
   Me.strDesc.Enabled = True
   Me.strDesc.SetFocus 'make description field current
   Me.ItemCount = D.Count 'display items count
   Me.cmdAdd.Enabled = True
   Me.cmdEdit.Enabled = False
   Me.cmdDelete.Enabled = False
   
End If

End Sub

Deleting Dictionary Item

When the Delete Command Button is clicked, the current Item on the Form is deleted from the Dictionary, after the user reconfirms it.

The data entry fields are cleared, and the item count control is updated with the reduced number of items.

Private Sub cmdSave_Click()

Private Sub cmdSave_Click()
Dim msg As String

msg = "Form will be closed After Saving Data,"
msg = msg & vbCr & "Proceed...?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbInformation, "cmdSave_Click()") = vbNo Then
  Exit Sub
Else
  Call Save2Table
  DoCmd.Close
End If

End Sub

Saving Data from the Dictionary Object.

When you have finished working with the Form and want to save the Dictionary Data Class’s property values from the Dictionary to the temporary Table (clsArea) and to close the Form, click on the 'Save to Table' Command Button.

Private Sub cmdExit_Click()

Private Sub cmdExit_Click()
Dim msg As String
If saveFlag Then
    DoCmd.Close
Else
    msg = "Dictionary Data Not saved in Table!"
    msg = msg & vbCr & vbCr & "Click Cancel to Go back"
    msg = msg & vbCr & vbCr & "Click OK to discard Data and close the Form!"
    If MsgBox(msg, vbOKCancel + vbDefaultButton2 + vbQuestion, "cmdExit()") = vbOK Then
        DoCmd.Close
    Else
        Call Save2Table
        DoCmd.Close
    End If
End If
End Sub

If you choose to click the Exit command button instead of the Save to Table button, you will be prompted to confirm whether you want to save the data. If you decide not to save, you can select the appropriate option to close the form without writing the data to the table.

There are also a few common subroutines that are called from multiple event procedures. Their code is included in the full listing provided below:

Private Sub initFields()
Clears all text boxes on the form when called from event procedures.
Private Sub comboUpdate(ByVal stDesc As String)
Called from the cmdAdd_Click() and cmdEdit_Click() event procedures to update the Combo Box items.
Private Sub Save2Table()
Called from the cmdSave_Click() and cmdExit_Click() event procedures to save the Dictionary Object data to the table.

The Form's Class Module Code.

Highlight, Copy, and Paste the Entire Code given below in the Form's Class Module of your Form. Save the Form with the name frmDictionary.

Option Compare Database
Option Explicit

Dim C As ClsArea, xC As ClsArea
Dim D As Object, Desc As String
Dim editFlag As Boolean, saveFlag As Boolean
Dim strV As String

Private Sub Form_Load()
    Set D = CreateObject(“Scripting.Dictionary”)
    Me.cmdEdit.Enabled = False
    Me.cmdDelete.Enabled = False
    saveFlag = False
End Sub

Private Sub Form_Current()
Dim o As String
o = cboKeys.RowSource
If Len(o) = 0 Then
  Me.cboKeys.Enabled = False
Else
  Me.cboKeys.Enabled = True
End If
End Sub


Private Sub cboKeys_AfterUpdate()
On Error Resume Next

strV = Me!cboKeys
Set xC = D(strV)
If Err > 0 Then
   MsgBox "Item for Key: " & strV & " Not Found!"
   Exit Sub
End If

Me!strDesc = xC.strDesc
Me!dblLength = xC.dblLength
Me!dblWidth = xC.dblWidth
Me!Area = xC.Area

Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True
Me.strDesc.Enabled = False

On Error GoTo 0

End Sub

Private Sub cmdAdd_Click()
'--------------------------------------------------
'1. Add Class Object as Item to Dictionary Object
'2. Update Combobox RowSource Property
'--------------------------------------------------
Dim tmpstrC As String, tmpLength As Double, tmpWidth As Double
Dim flag As Integer, msg As String
Dim cboVal As String, cbo As ComboBox

editFlag = False
Set cbo = Me.cboKeys

tmpstrC = Nz(Me!strDesc, "")
tmpLength = Nz(Me!dblLength, 0)
tmpWidth = Nz(Me!dblWidth, 0)

flag = 0
If Len(tmpstrC) = 0 Then flag = flag + 1
If tmpLength = 0 Then flag = flag + 1
If tmpWidth = 0 Then flag = flag + 1
If flag > 0 Then
   msg = "Invalid Data in one or more fields, correct them and retry."
   MsgBox msg, , "cmdAdd()"
   Exit Sub
End If

Desc = ""
Set C = New ClsArea 'create a new instance
'add Property Values
    C.strDesc = tmpstrC
    C.dblLength = tmpLength
    C.dblWidth = tmpWidth
    
'add Class Object instance to Dictionary
    
    D.Add tmpstrC, C 'Description is the Key
    
    Call comboUpdate(tmpstrC) 'update description as combobox item
       
    Me.ItemCount = D.Count 'display dictionary Items count
    
    'Call initFields 'set all fields to blanks
    Me.strDesc.SetFocus 'make description field current
    
'Clear Class Object
    Set C = Nothing

End Sub

Private Sub cmdDelete_Click()
Dim txtKey As String, msg As String

txtKey = Me!cboKeys
msg = "Delete Object with Key: " & txtKey & " ..?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbCritical) = vbYes Then
   D.Remove (txtKey) 'Remove the Item matching the Key
   
   MsgBox "Item Deleted."
   
   Call initFields
   
   Me.strDesc.Enabled = True
   Me.strDesc.SetFocus 'select description field current
   Me.ItemCount = D.Count 'display items count
   Me.cmdAdd.Enabled = True
   Me.cmdEdit.Enabled = False
   Me.cmdDelete.Enabled = False
   
End If

End Sub

Private Sub cmdEdit_Click()
'Edit the displayed item properties
Dim cap As String
Dim mDesc As String

editFlag = True

strV = Me!cboKeys

cap = Me.cmdEdit.Caption
Select Case cap
   Case "Edit"
      Me.strDesc.Enabled = True
      Me.cmdAdd.Enabled = False 'when editing Add button is disabled
      Me.cmdEdit.Caption = "Update" 'Edit Button Caption is changed
      
   Case "Update" 'Button clicked when Caption is Update
   'directly replace the property value in the Item
      xC.strDesc = Me!strDesc
      xC.dblLength = Me!dblLength
      xC.dblWidth = Me!dblWidth
      mDesc = Me!strDesc 'changed Description is copied to mDesc
      
   If mDesc <> strV Then 'checks with key in combobox value if not same then
      D.Key(strV) = mDesc 'Change Dictionary Key of Item
      
      'update new desc to Combobox
      'old desc also remains in combobox
      Call comboUpdate(mDesc)
   End If
      Call initFields
      Me.strDesc.SetFocus
      Me.cmdAdd.Enabled = True 'Enable Add button to add new item
      Me.cmdEdit.Caption = "Edit" 'change caption from Update to Edit
      Me.cmdEdit.Enabled = False 'disable Edit Button
      Me.cmdDelete.Enabled = False 'disable Edit Button
End Select

End Sub

Private Sub cmdExit_Click()
Dim msg As String
If saveFlag Then
    DoCmd.Close
Else
    msg = "Dictionary Data Not saved in Table!"
    msg = msg & vbCr & vbCr & "Click Cancel to Go back"
    msg = msg & vbCr & vbCr & "Click OK to discard Data and close the Form!"
    If MsgBox(msg, vbOKCancel + vbDefaultButton2 + vbQuestion, "cmdExit()") = vbOK Then
        DoCmd.Close
    Else
        Call Save2Table
        DoCmd.Close
    End If
End If
End Sub

Private Sub cmdSave_Click()
Dim msg As String

msg = "Form will be closed After Saving Data,"
msg = msg & vbCr & "Proceed...?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbInformation, "cmdSave_Click()") = vbNo Then
  Exit Sub
Else
  Call Save2Table
  DoCmd.Close
End If

End Sub

Private Sub Save2Table()
Dim db As Database, rst As Recordset
Dim recCount As Long, j As Long, item

On Error GoTo Save2Table_Error

recCount = D.Count

Set db = CurrentDb
Set rst = db.OpenRecordset("ClsArea", dbOpenTable)
For Each item In D.Items
   With rst
        .AddNew
         !strDesc = item.strDesc
         !dblLength = item.dblLength
         !dblWidth = item.dblWidth
        .Update
    End With
Next

rst.Close
Set rst = Nothing
Set db = Nothing

saveFlag = True
MsgBox "Data Saved  to Table: ClsArea"

Save2Table_Exit:
Exit Sub

Save2Table_Error:
MsgBox Err & ":" & Err.Description, , "Save2Table_Click()"
Resume Save2Table_Exit

End Sub

Private Sub Form_Unload(Cancel As Integer)
Set D = Nothing
End Sub

Private Sub strDesc_GotFocus()
    If editFlag Then
        Me.cmdAdd.Enabled = False
        Me.cmdEdit.Enabled = True
        Me.cmdDelete.Enabled = True
    Else
        Call initFields
        Me.cmdAdd.Enabled = True
        Me.cmdEdit.Enabled = False
        Me.cmdDelete.Enabled = False
        editFlag = False
    End If

End Sub


Private Sub dblWidth_LostFocus()
'Area is displayed for info purpose only
  Me!Area = Nz(Me!dblLength, 0) * Nz(Me!dblWidth, 0)
End Sub

Private Sub initFields()
'Empty all fields
    Me!strDesc = Null
    Me!dblLength = Null
    Me!dblWidth = Null
    Me!Area = Null
    Me.cmdAdd.Enabled = True
End Sub

Private Sub comboUpdate(ByVal stDesc As String)
Dim cbo As ComboBox, cboVal As String
Set cbo = Me.cboKeys
    cboVal = cbo.RowSource
    cboVal = cboVal & ";" & stDesc
    cbo.RowSource = cboVal
    cbo.Requery
    If Len(cboVal) > 0 Then
      Me.cboKeys.Enabled = True
    End If
End Sub

Perform a Demo Run.

Open the frmDictionary form, and try adding a few entries to the Dictionary object.
Select an item from the Combo Box to view the property values of the corresponding class object.
Click the Edit command button and modify the Description, Length, and Width values as needed.
Click the Edit button again (now showing the caption Update) to save the changes back into the Dictionary object. If you open the Combo Box afterward, you will see both the updated item with the new description and the old item with its previous description.
Select the old description from the Combo Box. You will receive an error message indicating that the item no longer exists, as its Dictionary key has been updated with the new description.
To delete an entry, select an item from the Combo Box and click the Delete command button. The selected item will be removed from the Dictionary object.
The event procedure code has been kept simple, without validation checks, error-handling routines, or safeguards against unintended user actions on the form.
You may enhance the code with these features, if needed, and reuse it in your own projects.

Downloads

You may download this demo database from the link given below.

Download Dictionary_2003.zip

Download Dictionary_2007.zip

MS-ACCESS CLASS MODULE

Add Class Objects as Dictionary Items

a.p.r. pillaiMarch 19, 2019Accesstips, Class Module, Dictionary Object, Object Reference, Objects No comments

Class Objects in Dictionary Object.

We have learned the fundamentals of the Dictionary Object, experimented with sorting simple items, and displayed Access table records on an MS Access Form from the Dictionary Object.

Now, let us move a step further and learn how to add MS Access Class Objects to a Dictionary, retrieve each object item, and display their Property values and Method outputs in the Debug Window.

If you would like to revisit the earlier related articles for reference, their links are provided below.

We need to have the ClsArea class object in the database to try out this example using the Dictionary object.

If it is not already present, you can create it by following these steps:

Open the VBA Editor.
Insert a new Class Module.
In the Properties Window, change the (Name) property of the new class to ClsArea.
Copy and paste the following VBA code into the ClsArea class module and save it.

Option Compare Database
Option Explicit

Private p_Desc As String
Private p_Length As Double
Private p_Width As Double

Public Property Get strDesc() As String
  strDesc = p_Desc 'copy the value from p_Desc
End Property

Public Property Let strDesc(ByVal strNewValue As String)
  p_Desc = strNewValue
End Property

Public Property Get dblLength() As Variant
  dblLength = p_Length
End Property

Public Property Let dblLength(ByVal dblNewValue As Variant)
    Do While Val(Nz(dblNewValue, 0)) <= 0
      dblNewValue = InputBox("Negative/0 Values Invalid:", "dblLength()", 0)
    Loop
  p_Length = dblNewValue
End Property

Public Property Get dblWidth() As Variant
  dblWidth = p_Width
End Property

Public Property Let dblWidth(ByVal dblNewValue As Variant)
    Do While Val(Nz(dblNewValue, 0)) <= 0
      dblNewValue = InputBox("Negative/0 Values Invalid:", "dblwidth()", 0)
    Loop
  p_Width = dblNewValue
End Property

Public Function Area() As Double
  If (Me.dblLength > 0) And (Me.dblWidth > 0) Then
     Area = Me.dblLength * Me.dblWidth
  Else
     Area = 0
     MsgBox "Error: Length/Width Value(s) Invalid., Program aborted."
  End If
End Function

Private Sub Class_Initialize()
    p_Length = 0
    p_Width = 0
    'MsgBox "Initialize.", vbInformation, "Class_Initialize()"
End Sub

Private Sub Class_Terminate()
   'MsgBox "Terminate.", vbInformation, "Class_Terminate()"
End Sub

The ClsArea Class object has three properties — strDesc, dblLength, and dblWidth — and one method: Area().

We will create multiple instances of this class to represent different rectangular shapes or rooms, and calculate their areas using the Area() method, and add each class object instance as an Item in a Dictionary object.

The value stored in the class object's strDesc property will be used as the Key for each corresponding item in the dictionary.

Since dictionary keys must be unique, make sure that the strDesc property values are not duplicated.

For example, if you have several bedrooms to calculate areas for, you can name them Bedroom1, Bedroom2, Bedroom3, and so on.

The ClassObjInDictionary() Procedure Code.

Let us try the Dictionary with the Class Module Object ClsArea as Items. The sample VBA Code for the Dictionary Object is given below. Copy and paste it into a Standard Module and save the Module.

Public Sub ClassObjInDictionary()
'--------------------------------------------------
'Add Class Object as Items to Dictionary Object
'Retrieve the Class Object from Dictionary Object
'and Print the values in the Debug Window.
'--------------------------------------------------
Dim C As ClsArea
Dim D As Object, Desc As String, mKey

Set D = CreateObject("Scripting.Dictionary")
D.CompareMode = 1
Desc = ""

Do While Not Desc = "Q"
'instantiate Class Object
Set C = New ClsArea
    
    'Get input Values for ClsArea Object\
    Do While Len(Desc) = 0
      Desc = InputBox("Description or Q=Quit:")
    Loop
       If Desc = "Q" Then Exit Do
       
    C.strDesc = Desc
    C.dblLength = CDbl(InputBox("Length of " & UCase(Desc) & ": "))
    C.dblWidth = CDbl(InputBox("Width of " & UCase(Desc) & ": "))
    
'add to Dictionary
'Description is added as Key of Dictionary Object
    D.Add Desc, C
    Desc = ""
'Clear Class Object
    Set C = Nothing
Loop


If D.Count = 0 Then
  MsgBox "No Data in Dictionary Object!" & vbCr & "Program Aborted."
  Exit Sub
End If

'Output Section
Debug.Print "Key Value", "Description", "Length", "Width", "Area"

For Each mKey In D.keys
        Set C = D(mKey)
        Debug.Print mKey, C.strDesc, C.dblLength, C.dblWidth, C.Area
Next

End Sub

The VBA Code Line by Line.

At the beginning of the code, we create and instantiate the Dictionary object D.

We use the Desc string variable to capture the description text that will be assigned to the strDesc property of the class object. This same variable also acts as the control for the Do While ... Loop.

The loop continues to run until the user enters the single character Q (for Quit) into the Desc variable.

This approach allows you to enter any number of class object instances into the dictionary. When you are done, simply enter Q in the description prompt to exit the loop.

Next, we create an instance of the ClsArea class object using the object variable C.

Inside this loop, the statement Desc = InputBox() is placed within a second Do While ... loop. This ensures that the user actually enters a value into the Desc variable.

If the user presses Enter, clicks OK, or Cancel without entering any text, the InputBox() function will repeat the prompt until a valid value is entered.

The valid Description Value is assigned to the C.strDesc Property of the Class Object.

Through the next two InputBox() functions, collect the Length and width values of the Room from the user and assign them to C.dblLength and C.dblWidth Properties, respectively.

Now, the ClsArea Class Object is ready to be added to the Dictionary Object.

The statement D.Add Desc, C adds the current instance of the ClsArea Class Object in the Dictionary Object as its first Item to Desc (or C.strDesc Property Value) as the Key of Dictionary Item.

Next, we clear the ClsArea Class Object instance C from memory.

You might have noticed by now that the Class Object instance C is created at the beginning of the outer Do While ... Loop, fills up the Class Object Property Values, adds it to the Dictionary Object, and the Class Object instance C is Set to Nothing as the last statement within the Loop. That means we are creating a New Class Object instance for each Item in the Dictionary Object.

Why it has to be this way- creating new instances of the Class Object for each Item- is an important point to keep in mind.

When we add the Class Object instance as an Item to the Dictionary Object, internally, only the Class Object’s Location Address is saved in the Dictionary Object as a Pointer. The actual Class Object Property values are not moved to the Dictionary Object Item.

When we execute the statement Set C = Nothing, the Class Object instance C is cleared, but the instance’s location reference (pointer) is saved in the Dictionary Object Item. The actual ClsArea Class Object remains in that location, and we can retrieve it using the Object Pointer saved in the Dictionary Object Item.

When a new Class Object Instance is created, it is stored in a new location in memory, and its reference is added to the Dictionary Object.

Enter some Description for a few bedrooms, Length, and Width Values to test the Code. Enter the letter Q to complete the Data entry when you are ready to take a dump of the data in the Debug Window.

A sample Listing is given below:

Key Value     Description   Length        Width         Area
Bed Room1     Bed Room1      14            15            210 
Bed Room2     Bed Room2      12            12            144 
Living Room   Living Room    23            24            552 
Kitchen       Kitchen        11            11            121 
Store Room    Store Room     21            14            294

In the printing code segment, we did not create a new instance of the ClsArea object (C) to read the class object pointers stored in the dictionary items. Instead, we directly accessed the stored object references from the dictionary and printed their values in the Debug Window.

Note: If you feel more comfortable doing so, you may create an ClsArea object instance (C) and assign the stored dictionary item reference to it. Both approaches work equally well.

The statement


Set C = D(mKey)

reads the object reference (pointer) of the ClsArea object from the dictionary item into C. Once assigned, you can retrieve its property values and method output and print them to the Debug Window.

If you have already run the sample code and understood how it works, try a small modification:

Move the object creation and object cleanup (removal) statements for the ClsArea object outside the Do While...Loop.
Then, rerun the code, add a few items to the dictionary, and print their property values to verify the output in the Debug Window.

Take a Trial Run With the Following Changes in the Code

The Do While ... loop segment with suggested changes is given below for changes in your code. Check the highlighted statements above and below the Do While ... Loop.

Desc = ""
Set C = New ClsArea

Do While Not Desc = "Q"
'instantiate Class Object
    
    'Get input Values for ClsArea Object
    Do While Len(Desc) = 0
      Desc = InputBox("Description or Q=Quit:")
    Loop
       If Desc = "Q" Then Exit Do
       
    C.strDesc = Desc
    C.dblLength = CDbl(InputBox("Length of " & UCase(Desc) & ": "))
    C.dblWidth = CDbl(InputBox("Width of " & UCase(Desc) & ": "))
    
'add to Dictionary
'Description is added as Key of Dictionary Object
    D.Add Desc, C
    Desc = ""
'Clear Class Object
Loop
    Set C = Nothing

I moved the statement Set C = New ClsArea to a position above the Do While...Loop, and placed the Set C = Nothing statement below the Loop so that it executes only after completing the data entry of Class Objects into the Dictionary within the Do While...Loop.

I entered all five sample items listed earlier, using the same names but different values for Length and Width.

Finally, the printing section listed all five items in the Debug Window.
However, instead of showing the individual values entered for each item, only the last item values are printed for all five entries.

Key Value     Description   Length        Width         Area
Bed Room1     Store Room     12            13            156 
Bed Room2     Store Room     12            13            156 
Living Room   Store Room     12            13            156 
Kitchen       Store Room     12            13            156 
Store Room    Store Room     12            13            156

Why has it happened this way?

When we add a Class Object with its Properties to a Dictionary Object, only the reference (memory address) of the Class Object is stored in the Dictionary Item—not its actual Property values.

When an instance of the Class Object is created using the New keyword, that instance is assigned a fixed memory location (address). Any new values entered into its Properties will overwrite the previous values stored in that same instance. Each time this same object reference is added to the Dictionary, the Dictionary stores only the address of the Class Object, not a copy of its current Property values.

By contrast, if we create a new instance of the Class Object during each loop cycle, a fresh object is created, with a different memory address. The Dictionary stores these unique addresses, allowing each Item to retain its own distinct Property values.

When the statement Set C = Nothing is executed, it simply clears the reference from the object variable C so it no longer points to any location. However, the actual object data remains alive in memory because its reference is still held by the Dictionary.

But when we moved the Set C = New ClsArea and Set C = Nothing statements outside the Do While...Loop, we ended up using only a single instance of the Class Object to input multiple sets of values. Each new set of Property values overwrote the previous ones in that same object. As a result, all the Dictionary Items ended up pointing to this single object instance, which holds only the last set of values entered.

Therefore, during printing, even though the Keys appear correctly in the listing, all the Items show the same (last entered) Property values.

Next week, we will learn how to add, edit, update, and delete Class Objects in the Dictionary through an MS Access Form.

Introduction

Capturing Events from Different Types of Controls

The Form Class Module Code.

VBA Code Line by Line

The Class Module ClsEvent1

Class Module ClsEvent1 Code Line by Line

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

VBA Code for the Tab Control Page Click Event

Removing Empty Event Procedure from Form Module

Revised Form Module Code

Revised Class Module ClsEvent1

Downloads

Links to WithEvents Tutorials.

Defining Custom Event and Class Module.

A re-run of last week's Demo with a change.

Form Module Code.

Create a Class Module

Testing the User-Defined Events

Important Points to Note.

Built-in Event Capturing in Class Module.

Form Module Code

The Class Module: ClsEventTest Code

Class Module VBA Code Line by Line

Links to WithEvents Tutorials.

Object Event, Event Firing, Capturing.

The WithEvents, Event, and RaiseEvent Statements.

Demo Run of User-Defined Events.

Form1 Class Module Code

Form2 Class Module Code

How does it work?

The Demo Run.

Links to WithEvents ...Tutorials.

Manage Dictionary Item Through Form.

Class Object Instances as Dictionary Items

The Form Module Event Procedures.

Retrieving the Dictionary Item

Editing Dictionary Item

Private Sub cmdDelete_Click()

Deleting Dictionary Item

Saving Data from the Dictionary Object.

The Form's Class Module Code.

Perform a Demo Run.

Downloads

MS-ACCESS CLASS MODULE

COLLECTION OBJECT

DICTIONARY OBJECT

Class Objects in Dictionary Object.

The ClassObjInDictionary() Procedure Code.

The VBA Code Line by Line.

Take a Trial Run With the Following Changes in the Code

Why has it happened this way?

MS-ACCESS CLASS MODULE

COLLECTION OBJECT

DICTIONARY OBJECT

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter

**The Class Module: ClsEventTest Code**