LEARN MS-ACCESS TIPS AND TRICKS: Controls

Showing posts with label Controls. Show all posts

Microsoft TreeView Control Tutorial

a.p.r. pillaiSeptember 11, 2020Common Controls, Controls, ImageList Control, ListView Control, Nodes, Tables, TreeView Control 7 comments

A. TreeView Control Tutorial.

The Microsoft TreeView Control, part of the Microsoft Windows Common Controls library, is a powerful component for displaying related data in a hierarchical node structure. It can represent information such as index entries and subentries, folder structures similar to the left pane of Windows Explorer, or any collection of related items, complete with tree lines, checkboxes, and graphical icons.

In addition to the TreeView Control, we will also use the ListView and ImageList controls, which are part of the Microsoft Windows Common Controls ActiveX library, to enhance functionality and visual presentation within Microsoft Access applications.

In the coming weeks, we will explore how to design, build, and customize a TreeView Control from scratch. As a preview of the concepts and implementations we will cover, I will also share several TreeView demonstration images illustrating the final results we will be working toward.

B. Sample Demo Images.

Nodes in the Collapsed state.

The Sample Demo TreeView Image with all Nodes in Collapsed form.
Right-click to open the Large Image in a New Window.
Nodes in the Expanded View.
The above TreeView Control Nodes are in the expanded view.
Right-click to open the Large Image in a New Window.

With Arrowhead Image Icons.
The TreeView Sample Display, with arrowhead Image Icons displayed to the left of each Node Text.
Right-click to open the Large Image in a New Window.
Folder Images for Root-Level Nodes, others with arrowheads.
Next, we will examine a TreeView display integrated with related data presented in a subform. The root-level nodes are visually enhanced with two graphical states: a closed folder icon in the default state and an open folder icon when selected. Selecting a root-level node not only changes its icon but also automatically expands the node to display its associated child nodes.

The subform dynamically updates to display information related to the selected root-level node. In addition, selecting specific child nodes can trigger the display of another form, which is normally kept in a hidden state to present additional related details.

Right-click to open the Large Image in a New Window.
TreeView and ListView Controls.
In the next form layout, there are two panels. The left panel contains the TreeView control, which displays nodes representing product categories. When a category node is clicked, the right panel—comprising the ListView control—updates to display the related product items, along with their quantities and list prices in separate columns.
Right-click to open the Large Image in a New Window.

C. Creating Sample Data for Trial Run.

Let us try out the TreeView Control with some sample data shown below, based on the first two images displayed at the beginning of this Page.

Right-click to open the Large Image in a New Window.

The sample data table contains three fields:

ID – An AutoNumber field that generates unique ID numbers. AutoNumber is used here for convenience, but regardless of the type, each record in the table must have a unique ID value. If the ID is numeric, it must be converted to a string (with at least one alphabetical character) before adding it to the TreeView control.
Description (Desc) – Contains the node descriptions. The entries in this column are logically related to one another.
ParentID – A numeric field that identifies the parent node for each record. This value should also be converted to a string before using it in the TreeView control.

To build the hierarchy, we need to understand how the description values relate to one another. Based on these relationships, appropriate values are entered into the ParentID field.

For example, the logical hierarchy could represent:

Authors → Publishers → Bookstores where the books are sold.
Members of a family tree.
Product categories → Products → Stock → Price.

This related information may not always exist in a single column or table—it could be spread across multiple columns or even different tables.

The ParentID field plays a crucial role in defining the hierarchical arrangement of nodes. If the ParentID field is empty, the record is treated as a root-level node. A child node must always have its ParentID Field populated with the parent record ID.

A root-level node can have one or more child nodes, and a child node can, in turn, have its own child nodes, creating multiple hierarchy levels.

We will first load the sample data into a TreeView control to view its initial arrangement. Then, by filling in the ParentID field with related IDs, we can reorganize the nodes into the desired logical order.

D. Windows Common Controls Library File.

First, open an existing database or create a new one.
Press ALT + F11 to open the VBA editor, then choose References… from the Tools menu.
In the list of available references, look for Microsoft Windows Common Controls and select it.
- If the file is not listed, click Browse… and locate MSCOMCTL.OCX in the Windows system directory.
- In Windows 7 (64-bit), you will typically find it in the SysWOW64 folder.
- Once selected, click OK to close the References dialog.
Create a Table with the following structure:
Right-click to open the Large Image in a New Window.
Name the Table as Sample and save it.
Add about twelve records in the Table, as shown in the data view Image above.
E. Creating TreeView Control on Form
Create a New blank Form.
Click on the ActiveX Controls button from the Controls Group, find the Microsoft TreeView Control, then select it.
Click OK to insert a TreeView control on the Form.
Drag the TreeView control slightly down and to the right to leave some space along the top and left edges.

Next, use the bottom-right sizing handle to stretch the control toward the right and downward, enlarging it to match the proportions shown in the sample image below.
Right-click to open the Large Image in a New Window.
Display the control's Property Sheet and change its Name Property Value to TreeView0.
Display the VBA Editing Window of the Form.

F. Access VBA Code.

Copy and paste the following VBA Code into the Module, overwriting the existing lines of code there:

Option Compare Database
Option Explicit

Dim tv As MSComctlLib.TreeView
Const KeyPrfx As String = "X"

Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim nodKey As String
Dim ParentKey As String
Dim strText As String

Set tv = Me.TreeView0.Object

strSQL = "SELECT ID, Desc, ParentID FROM Sample;"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rst.EOF And Not rst.BOF
    If Nz(rst!ParentID, "") = "" Then
        nodKey = KeyPrfx & CStr(rst!ID)
        strText = rst!Desc
        ‘Add the TreeView Root Level Nodes
        tv.Nodes.Add , , nodKey, strText
    
    Else
        ParentKey = KeyPrfx & CStr(rst!ParentID)
        nodKey = KeyPrfx & CStr(rst!ID)
        strText = rst!Desc
        ‘Add the Record as Child Node
        tv.Nodes.Add ParentKey, tvwChild, nodKey, strText 
        
    End If
    rst.MoveNext
Loop
rst.Close

Set rst = Nothing
Set db = Nothing

End Sub

Save the Form with the name frmSample, but don’t close the VBA Window.
G. VBA Code Review – Line by Line

Let’s take a quick look at the VBA code and understand what it does.

In the Global Declaration area of the Form module:
- tv is declared as a TreeView object.
- KeyPrfx is declared as a String constant with the value "X".
The TreeView Node’s Key must always be a string and must contain at least one non-numeric character. Since our sample table’s key values are purely numeric, we convert them to strings and prefix them with the letter "X".
- Example: 1 → "X1"
- Simply converting a numeric value to a string (without an alphabet) will not be accepted as a valid Node key.
Note: If the Node Key and Parent Key are already in alphabetic or alphanumeric form, conversion is unnecessary. All Node keys must still be unique.

In the Form_Load() event procedure:
1. Database and Recordset objects are declared, along with four string variables.
2. The statement:
```
Set tv = Me.TreeView0.Object
```
  assigns the TreeView0 control on the form to the object variable tv.
3. OpenRecordset() Opens the sample table records using the SQL string strSQL.
4. The Do While...Loop ensures the recordset is processed only if it is not empty; otherwise, the loop exits and the procedure ends.
Determining Node Type
- If the ParentID field is empty, the record becomes a Root-level Node.
  - A Root Node needs only a unique Key and Text (from the ID and Desc fields).
- If the ParentID field has a value, then the record becomes a Child Node of either a Root Node or of an upper-level Child Node.
Building the Node Key & Text.
- nodKey is created by converting the ID field to a string and prefixing it with "X". Example: ID = 1 → nodKey = "X1".
- strText stores the Desc field value. This keeps the Nodes.Add() parameters short and readable, especially when field references are long.
Adding the Node

The statement:
```
tv.Nodes.Add(...)
```
calls the Add() method of the TreeView’s Nodes collection to insert the node into TreeView0 on frmSample.

Syntax of Add() method:
```
tv.Nodes.Add([Relative], [Relationship], [Key], [Text], [Image], [SelectedImage]) As Node
```
- All six parameters are optional.
- Calling it without parameters adds an empty Root Node (a blank tree line).
Requirements:
- Root Node: Needs only Key and Text.
- Child Node: Requires both [Relative] and [Relationship].
  - Omitting either adds it as a Root Node (no error occurs).
  - [Relative] is the Node Key of the existing parent (from ParentID).
  - [Relationship] is the constant tvwChild (value 4), identifying it as a child of the parent node.

The other Constant values for the Relationship Argument are used to position the Child Nodes to a specific location. Constant Values are as follows:

tvwFirst = 0, places as the first Node, at the level of the relative Node.

tvwLast = 1, places as the last Node, at the level of the relative Node.

tvwNext = 2, places the Node after a specified Node.

tvwPrevious = 3, places the Node immediately preceding the specified Node.

Note: You can experiment with different values in the Relationship argument and run the code in Debug Mode, keeping the VBA editor and the form in Normal View side by side. Observe how the nodes are arranged during each iteration of the code. This will help you understand the effect of each relationship type.

This is useful when editing the TreeView—such as deleting an item, inserting another item in its place, or adding a new node at a specific location.

A node referenced in the [Relative] argument must already exist in the Nodes collection before you can add a child node to it. Otherwise, it will generate an error.

The process continues until all records in the recordset have been processed.

Note: You may need to review the VBA code again after running the demo to reinforce your understanding.

H. The First Trial Run.

Open the Form in Normal View. The Trial Run result looks like the Image given below.

Right-click to open the Large Image in a New Window.

It doesn’t look much different from a regular list box at the moment. This is because we have not yet assigned any values in the ParentID field in our sample table. To arrange items hierarchically in the TreeView control, we must define relationships between the rows in the table.

I. Understanding the Relationship Between Records

Open the sample table and examine how the records relate to each other.

We will keep the Database item as a root node. The database object also contains several top-level objects—Application, DBEngine, Workspaces collection, and Databases collection—which we have not included here.

Next, we have the Tables group item with ID value 2.

In the following records, we see the Table, Fields collection, and Field item, which are related to the Tables group. Our goal is to place the Table, Fields, and Field records under the parent node Tables (ID value 2).

In this hierarchy, the Tables record is the parent node, and the Table, Fields, and Field records are the child nodes.

J. Updating the ParentID Field

To achieve this structure, we need to update the ParentID field value 2 for the Table, Fields, and Field records (the node key of Tables).

Update only these records, then close the table. Once completed, your records should look like the example shown in the image below.

Right-click to open the Large Image in a New Window.

Now, open frmSample in Form View and check the TreeView control.
At first glance, the display will look exactly the same as before—no visible changes.
However, the updates you made are already in effect; they’re just not reflected visually yet.

K. The TreeView Control Property Sheet.

The TreeView control has its own Property Sheet, and these settings influence how the control appears. We’ll adjust one of these properties before returning to the TreeView to see the change.

Switch frmSample to Design View.
1. Right-click the TreeView control.
2. From the shortcut menu, highlight TreeCtrl_Object and select Properties.
3. The Property Sheet will now appear, as shown below.
  
  Right-click to open the Large Image in a New Window.
4. The Settings on this Property Sheet change the appearance of the TreeView Display.
  The top-left Property Style has already selected Option 7 (tvwTreeLinesPlusMinusPictureText), the maximum features available.
5. Change the LineStyle Property Value = 1 (tvwRootLines) and click the Apply button, then click OK to close the Property Sheet.
  L. Running After the LineStyle Property Change
6. Save the form and open it in Normal View.
  You’ll now see the tree lines displayed correctly.
7. The Tables node now shows a plus (+) sign to its left, indicating that it has one or more child nodes at the next level, which are currently collapsed.
  - Click the plus (+) sign to expand the node and reveal all child nodes sharing the same ParentID.
  - Click the minus (–) sign to collapse them again, hiding the child nodes and changing the symbol back to a plus (+).
  When expanded, the display will resemble the image shown below:
  Right-click to open the Large Image in a New Window.
  Updating the ParentID for Other Records.
  
  Next, we will establish the parent–child relationships for the remaining records.
  1. Forms and Controls
    - Update the ParentID field of the Form, Controls, and Control records with the Forms item's ID value (Node-Key).
    - This ensures that these records will appear under the Forms node as its child nodes in the TreeView.
  2. Reports and Controls
    - Similarly, update the ParentID field of the Report and Control records with the Reports record’s ID value (Node-Key).
    - This positions them under the Reports node as its child nodes.
8. Once the updates are complete, your Sample Table should match the ParentID values shown in the illustration below:
  
  Right-click to open the Large Image in a New Window.
  After applying the above changes, open the form and expand all the nodes in the TreeView. The display should now resemble the image shown below, with every parent node and its corresponding child nodes fully visible.
  
  Right-click to open the Large Image in a New Window.
  All child nodes related to the root-level nodes—Tables, Forms, and Reports—are grouped under their respective parent nodes. However, a child node may also have a parent, a grandparent, or even a great-grandparent node, depending on the hierarchy.
  N. Arranging All Objects in a Logical Hierarchical Order
  Let’s take the first root-level node, Tables, as an example. Logically:
  - Field (record ID 5) is directly related to the Fields collection (record ID 4).
  - The Fields collection is related to the Table.
  - The table is part of the Tables collection.
  This means that each item in the group (record numbers 5 through 2) is related to the step above it in the hierarchy.
  
  Next, we will position these child nodes under their respective parent nodes in the correct order and see how the arrangement appears in the TreeView control.
9. Open your Sample Table and change the ParentID values of the Tables related to Child Records as shown below:
  
  Right-click to open the Large Image in a New Window.
10. The record Field with ID 5 has Field (record ID 4) as its parent. Therefore, update the record with ID 5's ParentID field value to 4.
  
  Similarly:
  - Record 4 (Field) has record 3 (Table) as its parent, so we set its ParentID to 3.
  - Record 3 (Table) has record 2 (Tables) as its parent, so we set its ParentID to 2.
  Note: The records do not have to appear next to each other in the table to establish this hierarchy.
  
  After making these changes, save the table and open frmSample to view the results. Your TreeView display should now resemble the image shown below, with all nodes expanded.
  Right-click to open the Large Image in a New Window.
The Child Node of a Root-level Node can be a Parent Node to its own Child or Children. This way, it can go several steps down the tree.
Right-click to open the Large Image in a New Window.

Change the other two groups of Child Node's ParentID field values. When you do that correctly, it will look like the image given above.

Download TreeView Demo Database.

TreeView_Demo.zip

DICTIONARY OBJECT

Access Form Control Arrays and Event-3

a.p.r. pillaiMay 16, 2019Class Module, Collection Object, Controls, Event, Form, Objects, Property, WithEvents 3 comments

Introduction.

This article builds on last week’s topic, focusing on how to capture TextBox AfterUpdate and LostFocus events and validate their Values through a class module array.

In the previous session, we stopped short of discussing how to move all the VBA code from the Form_Load() event procedure into a separate class module, leaving the form module almost free of event procedures. The VBA code in this new structure will define the TextBox control Class Module Array and handle the required built-in events within their respective array elements. This approach will leave only three or four lines of code in the form module, while shifting all the logic into a derived class module object.

Earlier, we had created Derived Class Objects by using a class module as a base class and extending its functionality. We will apply the same concept here as well.

We are using TextBox controls first—rather than other controls on the form—for these array-based examples because they are the most commonly used controls. A TextBox supports several events, including BeforeUpdate, AfterUpdate, LostFocus, Enter, Exit, KeyDown, KeyUp, and OnKey. Depending on the requirements, we can choose to invoke one or more of these events from within the derived class object.

We can define a set of standard event procedures in the TextBox class module to handle commonly used events such as BeforeUpdate, AfterUpdate, Enter, or Exit. However, only the required event handlers need to be activated for each TextBox control. This can be done during the array element initialization by assigning:

obj.txt.EventName = "[Event Procedure]"

This approach enables the selective activation of event procedures for individual TextBox instances.

Since each Form may require different validation rules or processing logic, the code inside these class event procedures often needs customization. An effective way to manage this is to create a TextBox Class Module Template and incorporate the most frequently used event procedures. For a new form, simply copy this template and modify it to suit the specific requirements of the TextBox controls on that form.

Other control types on a form—such as Command Buttons, Combo Boxes, and List Boxes—generally rely on fewer events, most commonly Click or DblClick. We will examine managing these other control types in arrays later.

Eventually, we will also explore whether there are more effective approaches than arrays for managing multiple instances of different types of controls on the same form.

Moving Form's Class Module Code to Derived Class Module

Returning to today’s topic—moving the Form Module code into a separate Class Module—we will create a new Derived Class Module Object based on the existing ClsTxtArray1_2 Class Module as the Base Class. The code currently in the Form_Load() event procedure of the Form Module will be relocated into this new Derived Class.

If you haven’t already downloaded last week’s demo database, please do so using the link provided before proceeding. We will make copies of the relevant Modules and Forms to modify the code, ensuring that both the original and the updated versions of the code and forms are available within the same database. After making these changes, you can immediately run the forms to observe how the new implementation works.

Download TxtArray2_2007.zip

Download TxtArray2_2003.zip

After downloading the database, open it in Microsoft Access. You can then open the Form Module and review its code.

Next, copy the ClsTxtArray1_2 Class Module into a new Class Module named ClsTxtArray1_3, without making any changes to its code. Similarly, make a copy of the existing form and rename it TxtArray1_3Header. Any modifications will be done on these new copies, ensuring that the original Form and Class Module remain intact and unaltered.

We will use last week’s sample Form (shown in the image below) along with its Form Module VBA code, also reproduced below for your reference.

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

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_2

Private Sub Form_Load()
Dim cnt As Integer
Dim ctl As Control

For Each ctl In Me.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     If ctl.Name = "Text8" Then
       Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Else
       Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End If
     
  End If
Next
End Sub

Make a Copy of the above Form and name it as frmTxtArray1_3Header.

Create a new Class Module with the name ClsTxtArray1_3. Copy the VBA Code from the ClsTxtArray1_2 Class Module and paste it into the new Module.

Last week’s Class Module ClsTxtArray1_2 Code is reproduced below for reference.

Option Compare Database
Option Explicit

Private WithEvents Txt As Access.TextBox

Public Property Get mTxt() As Access.TextBox
  Set mTxt = Txt
End Property

Public Property Set mTxt(ByRef txtNewValue As Access.TextBox)
  Set Txt = txtNewValue
End Property

Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = Txt.Name
msg = ""
Select Case txtName
    Case "Text0"
        'Valid value range 1 to 5 only
        varVal = Nz(Txt.Value, 0)
        If varVal < 1 Or varVal > 5 Then
           msg = "Valid Value Range 1-5 only: " & varVal
        End If
    Case "Text8"
        'validates in LostFocus Event
    Case "Text10"
        'valid value 10 characters or less
        'Removes extra characters, if entered
        varVal = Nz(Txt.Value, "")
        If Len(varVal) > 10 Then
           msg = "Max 10 Characters Only. " & varVal
           Txt.Value = Left(varVal, 10)
        End If
    Case "Text12"
        'Date must be <= today
        'Future date will be replaced with Today's date
        varVal = DateValue(Txt.Value)
        If varVal > Date Then
          msg = "Future Date Invalid. " & varVal & vbCr & "Corrected to Today's Date."
          Txt.Value = Date
        End If
    Case "Text14"
        'A 10 digit number only valid
        varVal = Trim(Str(Nz(Txt.Value, 0)))
        If Len(varVal) <> 10 Then
          msg = "Invalid Mobile Number: " & varVal
        End If
End Select

If Len(msg) > 0 Then
    MsgBox msg, vbInformation, Txt.Name
End If

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant, msg As String

tbx = Nz(Txt.Value, "")

msg = ""
If Len(tbx) = 0 Then
  msg = Txt.Name & " cannot be left Empty."
  Txt.Value = "XXXXXXXXXX"
End If

If Len(msg) > 0 Then
   MsgBox msg, vbInformation, Txt.Name
End If

End Sub

The Derived Class: ClsTxtArray1_3Header

The ClsTxtArray1_3 Class Module will be used as the Base Class for our new Derived Class Module. We will name it ClsTxtArray1_3Header, with extended functionality.

Create a new Class Module with the name ClsTxtArray1_3Header. The Derived Class Module, with its Properties and Property Procedures, is given below:

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_3
Private frm As Access.Form

Public Property Get mFrm() As Access.Form
  Set mFrm = frm
End Property

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
 'Form Module Code goes here
End Sub

Copy and paste the above code into the new Header Class Module you have created.

Check the first two Property declarations. First Property ClsTxtArray1_3 Class Object is instantiated as an undefined Array: Ta() – Ta stands for TextBox-Array.

The next property, frm, is introduced to give this Class Module access to the Form from which we plan to transfer the existing VBA code. All actions that were previously handled in the Form Module will now be managed here.

We will create Get and Set Property procedures to handle references to the Form. It will be a Set property (not a Let property) because we are passing a Form object, not a simple value, to it.

Immediately after the Form’s reference is received in the Set Property Procedure, we call the Class_Init() (this is not the same as Class_Initialize(), which runs automatically when a Class Object is instantiated) sub-routine to run the same code moved here from the Form’s Module.

Now, we will transfer the following Code from the Form_Load() Event Procedure into the Class_Init() sub-routine and make changes in the Form Module.

Copy and paste the following lines of code from the Form Module into the Class_Init() sub-routine, replacing the Comment line:

Dim cnt As Integer
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End Select
     
  End If
Next

Form's Class Module Code

Open the Form frmTxtArray1_3Header in the design view. Display the Code Module. Copy and paste the following Code into the Form's Module, overwriting the existing Code:

Option Compare Database
Option Explicit

Private T As New ClsTxtArray1_3Header

Private Sub Form_Load()
  Set T.mFrm = Me
End Sub

We have instantiated the Derived Class ClsTxtArray1_3Header in Object Variable T. With the statement Set T.mFrm = Me, the active form's reference is passed to the T.mFrm() Set Property Procedure.

Immediately after this action, on the Form_Load () Event procedure, the Class_Init() sub-routine runs in the ClsTxtArray1_3Header Class, and the txtArray1_3 Class Object array elements are created by invoking Events for each Text Box on the Form. Hope you are clear with the Code above.

If you are ready to modify the Form Module, compile the database to ensure that everything is in order.

Save and close the Form, open it in Normal View, and try out each TextBox, and ensure that its Event sub-routines are performing as expected.

Replacing Class Object Array with Collection Object Items

The TextBox Class Object Array method works well for handling multiple TextBoxes. However, creating an array requires a counter variable, resizing the array for each new element while preserving the existing elements, and incrementing the counter for the next TextBox on the form, and so on.

When a form contains multiple controls of other types—such as Command Buttons, ComboBoxes, or ListBoxes—we would need to create separate arrays for each control type, each with its own counter and resizing logic in the class module. We will explore this approach in a future example.

A more efficient way to handle such complex scenarios is to use a Collection object instead of arrays. We will demonstrate this approach here, with TextBoxes, so you can get a practical feel for managing multiple controls using collections.

Create a new Derived Class Module with the name ClsTxtArray1_3Coll.
Copy and Paste the following Code into the Class Module:

Option Compare Database
Option Explicit

Private C As New Collection
Private Ta As ClsTxtArray1_3
Private frm As Access.Form

Public Property Get mFrm() As Access.Form
  Set mFrm = frm
End Property

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
'-----------------------------
'Usage of Collection Object, replacing Arrays
'-----------------------------
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     
     Set Ta = New ClsTxtArray1_3  'instantiate TextBox Class
     Set Ta.Txt = ctl 'pass control to Public Class Property
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta.Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta.Txt.AfterUpdate = "[Event Procedure]"
     End Select
     C.Add Ta 'add to Collection Object
  End If
Next

End Sub

A Collection Object Property is declared and instantiated at the beginning.

The TextBox Class Module is defined, not instantiated, in the Object Variable Ta.

The TextBox Class Ta Object is instantiated within the Control Type Test condition. A new Ta Object instance is created for each TextBox on the Form.

After enabling the Events, the Ta Class Object is added to the Collection Object as its Item.

This method is repeated by adding a new instance of the TextBox class Object for each TextBox on the Form, with its required Events enabled, as a new Item to the Collection Object. The Code is cleaner than the Array method.

Make a copy of the Form frmTxtArray1_3Header with the name frmTxtArray1_3Coll.

Open it in Design View and display the Form's Code Module.
Copy and paste the Following Code into the Form Module, replacing the existing Code.

Option Compare Database
Option Explicit

Private Ta As New ClsTxtArray1_3Coll

Private Sub Form_Load()
  Set Ta.mFrm = Me
End Sub

The only change here is the name of the derived object, which has been updated to ClstxtArray1_3Coll. After making this change, recompile the database.

Save the Form, open it in Normal View. Test the TextBoxes as before.

It should work as before.

Downloads

You can download the database, which includes all the modules and forms with the suggested changes applied.

Download TxtArrayHeader_2007.zip

Download TxtArrayHeader_2003.zip

Links to WithEvents ...Tutorials.

Access Form Control Arrays and Event-2

a.p.r. pillaiMay 11, 2019Array, Class Module, Controls, Event, Objects, Property 2 comments

Control Arrays and Events-2.

Last week, we learned how to create a Class Object Array for TextBoxes on the Form. The built-in AfterUpdate and LostFocus events raised from the TextBoxes on the Form are captured by their corresponding elements in the Class Object Array. Each element then executes its own AfterUpdate() or LostFocus() subroutine, instead of running the code within the Form’s Class Module as we normally would.

For example, the AfterUpdate event of the first TextBox is captured by the first element of the Class Object Array, and its AfterUpdate () subroutine is executed there. In the same way, events from other TextBoxes are also handled by their respective Class Object Array elements.

If you are new to this topic, please refer to the earlier pages using the links below to understand the step-by-step transition of code from one stage to the next.

The AfterUpdate and LostFocus event handler code we wrote earlier in the Class Module was generic in nature and applied to all TextBox controls on the Form. These were test subroutines created solely to experiment whether the events triggered from each TextBox on the Form were being correctly captured by their respective Class Module array elements.

Data Validation Checks

Now, it’s time to define specific data validation rules for each TextBox on the form and ensure that the user is notified whenever a rule is violated.

To make sure these rules are clear, I’ve placed descriptive labels above each Text Box on the form. These labels indicate how the values entered in the TextBoxes will be validated within the Class Module array through their AfterUpdate and LostFocus events.

An image of the form, showing the TextBoxes along with their corresponding validation rule labels, is provided below.

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

Note: This setup is intended purely for demonstration purposes, so the validation rules are not strictly enforced. The second TextBox accepts any type of input—whether text, numbers, or other characters. The Mobile Number field, however, checks only the length of the entered value. Additionally, the Mobile Number TextBox has an Input Mask applied to restrict input to digits only.

The first TextBox accepts only values between 1 and 5. Any value outside this range triggers an error message.
The second TextBox is validated in the LostFocus event to ensure it is not left blank. If it is empty, an error message is displayed, and a sample string is inserted automatically.
The third TextBox accepts text or numbers up to 10 characters long. Any extra characters beyond this limit are removed, and the field is updated accordingly. If left blank, no error is shown.
The fourth TextBox is a date field. Any date later than today is considered invalid.
The last TextBox accepts only a 10-digit number.

The Class Module Changes.

We will write the VBA code for the above simple validation checks in the class module.

The earlier version of the VBA code in the class module ClstxtArray1 is given below for reference.

Option Compare Database
Option Explicit

Public WithEvents Txt As Access.TextBox

Private Sub Txt_AfterUpdate()
Dim txtName As String, sngval As Single
Dim msg As String

txtName = Txt.Name
sngval = Nz(Txt.Value, 0)

msg = txtName & " _AfterUpdate. :" & sngval
MsgBox msg, vbInformation, Txt.Name

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant
tbx = Nz(Txt.Value, "")

If Len(tbx) = 0 Then
  MsgBox Txt.Name & " is Empty.", vbInformation, Txt.Name
End If
End Sub

The Event-based Sub-Routine

Check the Txt_AfterUpdate() event procedure. This single event handler in the class module receives the AfterUpdate event from all the text boxes on the form. The txt.Name property identifies which text box triggered the event, while the txt.Value property provides the value entered in that text box. Using these two properties, you can write specific validation logic for the contents of each text box.

The text box validation sample VBA code of the txt_AfterUpdate() Event Sub-routine is given below.

Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = Txt.Name
msg = ""
Select Case txtName
    Case "Text0"
        varVal = Nz(Txt.Value, 0)
        If varVal < 1 Or varVal > 5 Then
           msg = "Valid Value Range 1-5 only: " & varVal
        End If
    Case "Text8"
        ' validation in OnLostFocus Event
    Case "Text10"
        varVal = Nz(Txt.Value, "")
        If Len(varVal) > 10 Then
           msg = "Max 10 Characters Only. " & varVal
           Txt.Value = Left(varVal, 10)
        End If
    Case "Text12"
        varVal = DateValue(Txt.Value)
        If varVal > Date Then
          msg = "Future Date Invalid. " & varVal
        End If
    Case "Text14"
        varVal = Trim(Str(Nz(Txt.Value, 0)))
        If Len(varVal) <> 10 Then
          msg = "Invalid Mobile Number: " & varVal
        End If
End Select

If Len(msg) > 0 Then
    MsgBox msg, vbInformation, Txt.Name
End If

End Sub

The text box name (Txt.Name) received from the AfterUpdate Event is checked in the Select Case. . . End Select structure. Depending on the text box name and the TextBox value (Txt.Value), the validation check is performed; if Invalid, an appropriate message is displayed.

On the Form_Load() Event Procedure, we have added the OnLostFocus() Event only for TextBox8 on the form. When the insertion point leaves this text box, the LostFocus Event fires and is captured in the Private Sub txt_LostFocus() subroutine of the class module. If the TextBox is empty, the sample text string “XXXXXXXXXX” is inserted into TextBox8, followed by an error message.

The LostFocus subroutine is given below:

Private Sub Txt_LostFocus()
Dim tbx As Variant, msg As String

tbx = Nz(Txt.Value, "")

msg = ""
If Len(tbx) = 0 Then
  msg = Txt.Name & " cannot leave it Empty."
  Txt.Value = "XXXXXXXXXX"
End If

If Len(msg) > 0 Then
   MsgBox msg, vbInformation, Txt.Name
End If

End Sub

Here, we are not testing for TextBox8, as we did in the AfterUpdate() event procedure, because we have not added the LostFocus Event for any other TextBox.

Did you notice that the statement Txt.value = "XXXXXXXXXX" writes the string back to the same TextBox from which the event was captured? But what if we need to access another control on the form to read or write data there?

To achieve this, we must introduce a Form object property in the class module. We will implement this along with the upcoming code changes, as part of our future plan is to move all actions from the form module to the class module.

The full VBA Code of the Class Module: ClsTxtArray1_2 is given below:

Option Compare Database
Option Explicit

Public WithEvents Txt As Access.TextBox

Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = Txt.Name
msg = ""
Select Case txtName
    Case "Text0"
        varVal = Nz(Txt.Value, 0)
        If varVal < 1 Or varVal > 5 Then
           msg = "Valid Value Range 1-5 only: " & varVal
        End If
    Case "Text8"
        ' validation in OnLostFocus Event
    Case "Text10"
        varVal = Nz(Txt.Value, "")
        If Len(varVal) > 10 Then
           msg = "Max 10 Characters Only. " & varVal
           Txt.Value = Left(varVal, 10)
        End If
    Case "Text12"
        varVal = DateValue(Txt.Value)
        If varVal > Date Then
          msg = "Future Date Invalid. " & varVal
        End If
    Case "Text14"
        varVal = Trim(Str(Nz(Txt.Value, 0)))
        If Len(varVal) <> 10 Then
          msg = "Invalid Mobile Number: " & varVal
        End If
End Select

If Len(msg) > 0 Then
    MsgBox msg, vbInformation, Txt.Name
End If

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant, msg As String

tbx = Nz(Txt.Value, "")

msg = ""
If Len(tbx) = 0 Then
  msg = Txt.Name & " cannot leave it Empty."
  Txt.Value = "XXXXXXXXXX"
End If

If Len(msg) > 0 Then
   MsgBox msg, vbInformation, Txt.Name
End If

End Sub

The Form Module VBA Code

The form module code remains unchanged from last week’s example, except that the class module name has been updated to ClstxtArray1_2.

I maintain the class module code from earlier articles as separate versioned copies, which is why the class module name has changed here.

Additionally, I made a minor change in the form module code for the TextBox8 control — it now raises only the LostFocus event. In the earlier version, the code triggered both the AfterUpdate and LostFocus events.

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_2

Private Sub Form_Load()
Dim cnt As Integer
Dim ctl As Control

For Each ctl In Me.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     If ctl.Name = "Text8" Then
       Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Else
       Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End If    
  End If
Next
End Sub

Downloads

We have now moved all the event-handling code—normally written in the form’s class module—into a separate class module, keeping all the underlying actions completely hidden from the user.

However, if you look at the current Form_Load() event procedure, you’ll notice that there’s still quite a bit of code left in the form module.

In the coming weeks, we’ll explore techniques to shift almost all this remaining code into the Standalone Class Module, leaving only three or four lines in the Form Module.

In the meantime, you can download the demo database from the links below, try it out, and study the code to understand how it works.

Download TxtArray2_2007.zip

Download TxtArray2_2003.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.

LEARN MS-ACCESS TIPS AND TRICKS

Microsoft TreeView Control Tutorial

A. TreeView Control Tutorial.

B. Sample Demo Images.

Nodes in the Collapsed state.

Nodes in the Expanded View.

With Arrowhead Image Icons.

Folder Images for Root-Level Nodes, others with arrowheads.

TreeView and ListView Controls.

C. Creating Sample Data for Trial Run.

D. Windows Common Controls Library File.

E. Creating TreeView Control on Form

F. Access VBA Code.

G. VBA Code Review – Line by Line

Determining Node Type

Building the Node Key & Text.

Adding the Node

H. The First Trial Run.

I. Understanding the Relationship Between Records

J. Updating the ParentID Field

K. The TreeView Control Property Sheet.

L. Running After the LineStyle Property Change

Updating the ParentID for Other Records.

DICTIONARY OBJECT

Access Form Control Arrays and Event-3

Introduction.

Moving Form's Class Module Code to Derived Class Module

The Derived Class: ClsTxtArray1_3Header

Form's Class Module Code

Replacing Class Object Array with Collection Object Items

Downloads

Links to WithEvents ...Tutorials.

Access Form Control Arrays and Event-2

Control Arrays and Events-2.

Data Validation Checks

The Class Module Changes.

The Event-based Sub-Routine

The Form Module VBA Code

Downloads

Links to WithEvents ...Tutorials.

WithEvents and Defining your own Events

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.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Useful-Links

Popular Posts

About Me

Blog Archive

Labels

Report Abuse

Statcounter

**The Class Module: ClsEventTest Code**