Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is always rounded up.  When number of digits is greater than 0 (zero), then the number is rounded up to the specified number of decimal places. If number of digits specified is 0 (zero), then the number is rounded up to the nearest integer.

Visit the following link for more details on Roundup() Function:

The ROUNDUP() Function of Excel in Access.

The ROUNDDOWN() Function of Excel does the opposite of ROUNDUP() Function.  When the number of digits specified is greater than 0 (zero) then the number is rounded down to the specified number of decimal Places.  If the number of digits specified is 0 (zero) then the number is rounded down to the nearest integer.

Syntax:

ROUNDDOWN(Number, num_digits)

Number: RequiredAny Real Number.

num_digits: Required, Number of Digits the Number to Round Down to.

Public Function ROUNDDOWN(ByVal Num As Double, ByVal num_digits As Integer) As Double
'-------------------------------------------------
'ROUNDDOWN() Function of Excel Redefined in MS-Access
'Author: apr pillai
'Date  : Sept 2019
'Rights: All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------

Dim S1 As Integer, S2 As Integer

On Error GoTo ROUNDDOWN_Err
S1 = Sgn(Num)
S2 = Sgn(num_digits)

Select Case S1
    Case 0
        ROUNDDOWN = 0
        Exit Function
    Case 1
    Select Case S2
        Case 0
            ROUNDDOWN = Int(Num) * S1
        Case 1
            ROUNDDOWN = (Int(Num * (10 ^ num_digits)) / 10 ^ num_digits) * S1
        Case -1
            num_digits = Abs(num_digits)
            ROUNDDOWN = Int(Num / (10 ^ num_digits)) * 10 ^ (num_digits) * S1
    End Select
    Case -1
    Select Case S2
        Case 0
            ROUNDDOWN = Int(Abs(Num)) * S1
        Case 1
            ROUNDDOWN = (Int(Abs(Num) * (10 ^ num_digits)) / 10 ^ num_digits) * S1
        Case -1
            num_digits = Abs(num_digits)
            ROUNDDOWN = (Int(Abs(Num) / (10 ^ num_digits)) * 10 ^ num_digits) * S2
    End Select
End Select

ROUNDDOWN_Exit:
Exit Function

ROUNDDOWN_Err:
MsgBox Err & " : " & Err.Description, , "ROUNDDOWN()"
Resume ROUNDDOWN_Exit
End Function


The ROUNDDOWN() Function is not field tested for accuracy, use it at your own risk.

The Function is developed based on the sample output given in the Microsoft Help Document. The Microsoft Excel Help Document extract is reproduced below for your information.

FormulaDescriptionResult
=ROUNDDOWN(3.2, 0)Rounds 3.2 down to zero decimal places.3
=ROUNDDOWN(76.9,0)Rounds 76.9 down to zero decimal places.76
=ROUNDDOWN(3.14159, 3)Rounds 3.14159 down to three decimal places.3.141
=ROUNDDOWN(-3.14159, 1)Rounds -3.14159 down to one decimal place.3.1
=ROUNDDOWN(31415.92654, -2)Rounds 31415.92654 down to 2 decimal places to the left of the decimal point.31400
  1. Rounding Function MRound of Excel
  2. Microsoft Excel Power in MS-Access
  3. Roundup Function of Excel in MS-Access
  4. Proper Function of Excel in Microsoft Access
  5. Printing MS-Access Report from Excel
  6. Opening Excel Database Directly
Share:

WithEvents and All Form Control Types

Introduction

So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and capture in stand alone Class Module, in various ways.  We have done demo runs to capture Events Raised from Form, like: AfterUpdate and LostFocus, in Class Module from several Text Boxes. 

The Class Object instance, one for each Text Box on the Form, is created and enabled with the required Event and added into Object Array elements or to Collection Object or as Dictionary Object Items.

Assume that we have five text boxes on our Form.  Out of that three text boxes need some Event to run, to execute some task related to the data on that text box, like validation checks on data.  In that case we need three instance of the ClsText Class Module , one instance for each text box,  Other text boxes are left out.

When the enabled Event fires, from the Text Box on the Form, the related Class Object Array Element captures the Event and executes the Code within the Event Procedure. 

Last week we have added the Command Button control and it's Class Module along with Text Boxes for our demo runs.

New Demo Form: frmControls_All

In this demo Run we will include most of the frequently used controls on the Form..  The image of the demo Form is given below:

Besides Text Boxes and Command Buttons, we have Tab Control, Combo Boxes, List Boxes and Option Group controls.

We need new Class Module for each type of new controls on the Demo Form.  We already have Class Modules for Text Boxes and Command Buttons.

TextBox and CommandButton Class Modules.

Text Box and Command Button Class Module Code and their write-up is already posted in an earlier Article.  To reduce the size of this Post I will omit those details here.

You may visit directly to those areas of the Post, to view the VBA Code and their write-up, by selecting the Bookmark links given below:

  1. Class Module: ClsText
  2. Class Module: ClsCmdButton
  3. Download: You may download the Demo Database given there, run the sample Form and try out the controls on the From.  Explore the methods and the VBA Code implemented there.

Here, what we do is an extension of that with more controls on the Form.

Tab-Control Class Module: ClsTabCtrl

The Tab-Control Class Module: ClsTabCtrl VBA Code is given below:

Option Compare Database
Option Explicit

Private frm As Access.Form
Private WithEvents Tb As Access.TabControl

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

Public Property Set p_frm(ByRef frmValue As Access.Form)
  Set frm = frmValue
End Property

Public Property Get p_Tb() As Access.TabControl
   Set p_Tb = Tb
End Property

Public Property Set p_Tb(ByRef tbValue As Access.TabControl)
  Set Tb = tbValue
End Property

Public Sub Tb_Change()
Dim msg As String, title As String

Select Case Tb
    Case 0
        frm.Controls("Computer").Value = Environ("ComputerName")
        title = Tb.Pages(0).Name
        msg = "Tab Page Index = 0"
    Case 1
        frm.Controls("UserName").Value = Environ("UserName")
        title = Tb.Pages(1).Name
        msg = "Tab Page Index = 1"
End Select

MsgBox msg, , title

End Sub

The ClsTabCtrl Class Module declares Access.Form and Access.Tab-Control Properties in Objects frm and Tb respectively.  The Tab Control property is declared with the Key word WithEvents to capture the Events taking place on the control.

The Set Property Procedures p_frm assigns the Form Object to the frm Property, declared with Private Scope.  The Get Property procedure provides the Form reference to address any other control on the Form, like frm.p_frm.Controls("UserName").value = Environ("UserName")

The next Set Property Procedures assigns the Tab Control Object to the Tb Property.  Similarly, the Get Property Procedures provides the Tab-Control Object reference to the calling Program, as explained in the above paragraph.

Next, the Tab Page Click Event is captured in the Tb_Change() Sub-Routine and executes the Code there.  When the User Clicks on a particular Tab Control Page the Click Event is not fired instead  the Change Event takes place.  That is the reason why we have added the Tb_Change() Procedure.

Two string variables msg and title are declared to assign some string values to display in the  Message Box. When the Tab Page Click occurs the Tab Page Change Event fires and this is captured in the Tb_Change() sub-routine.  The sub-routine displays a message, indicating that the Event is  captured and executed the code in the sub-routine.

There are two Pages, on the Tab-Control, with Page index number 0 and 1.  There is a Text Box on each Page of the Tab Control.

When the Page Change Event fires we check for the Tab Page index number. If the first Page is clicked (with page index number 0), then the Text Box on that page will be updated with the Computer's Name.  Here, we use the frm Object reference to address the Text Box (with the name Computer)  on the first Tab Page and updates with the Computer's name.

The second page click will update the Text Box with the User Name (Window's UserName).

NB: The actual procedure you write on the Tb_Change() sub-routine depends on what you want to do on that Event in your Project.

The Combo Box Class Module: ClsCombo

The Class Module Code for Combo Box control is given below:

Option Compare Database
Option Explicit

Private WithEvents cbx As Access.ComboBox

Public Property Get p_cbx() As Access.ComboBox
 Set p_cbx = cbx
End Property

Public Property Set p_cbx(ByRef cbNewValue As Access.ComboBox)
 Set cbx = cbNewValue
End Property

Private Sub cbx_Click()
Dim vVal As Variant
Dim cboName As String

vVal = cbx.Value
cboName = cbx.Name

Select Case cbx
    Case "Combo10"
        'Code Goes here
    Case "Combo12"
        'Code Goes here
End Select

MsgBox "Clicked: " & vVal, , cboName
End Sub

The Class Module for Combo Box declares a single Property cbx Object, with Private Scope and with the key word WithEvents to capture the Events fired from the Combo Box.

The Public Property Procedure Set p_cbx() receives the Combo Box control reference from the Form and assigns it to the Property cbx.

The Get Property Procedure passes the reference of the control to the calling program outside this module.

The next sub-routine cbx_Click() captures the Click Event of the Combo Box on the Form. The Select Case structure tests for the source of the Click Event, fired from which Combo Box and accordingly the Code executes under that combo box name. 

If you would like to capture any other Events, like OnGotFocus, OnLostFocus, AfterUpdate, OnChange etc. you can write sub-routines in the same Class Module, like cbx_GotFocus() and write the required VBA Code.  Need to enable the Event in the Derived Class Module to fire the Event as and when it occurs on the Form.

The Click Event sub-routine displays a common message, with the item value selected from the Combo Box.

The List Box Class Module: ClsListBox

The List Box Class Module Code structure is similar to the Combo box Code and programmed to capture only the Click Event.

Option Compare Database
Option Explicit

Private WithEvents LstBox As Access.ListBox

Public Property Get p_LstBox() As Access.ListBox
 Set p_LstBox = LstBox
End Property

Public Property Set p_LstBox(ByRef pNewValue As Access.ListBox)
 Set LstBox = pNewValue
End Property

Private Sub LstBox_Click()
Dim vVal As Variant
Dim lst As String

vVal = LstBox.Value
lst = LstBox.Name

Select Case lst
    Case "List16"
        'Code
    Case "List18"
        'Code
End Select

  MsgBox "Clicked: " & vVal, , lst

End Sub

It can be modified with additional sub-routines to capture any other Event Raised on the List Box.  The existing Code works on similar lines of the Combo Box and displays the selected item value in the message box.

The Class Module for Option Group: ClsOption

The Option Group have three different styles: 1. Option Buttons, 2. Check Boxes, 3. Toggle Buttons.  In our Demo Form we have used only two styles: Option Buttons and Check Boxes.  But, all the three works the same way only the display style is different and their Control names start with the name Frame followed by a number, like any other control on the Form: Frame18, Text2, Combo10, List12 etc.

The ClsOption Class Module Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Opts As Access.OptionGroup

Public Property Get p_Opts() As Access.OptionGroup
  Set Opts = Opts
End Property

Public Property Set p_Opts(ByRef pNewValue As Access.OptionGroup)
  Set Opts = pNewValue
End Property

Private Sub Opts_Click()
Dim txtName As String, intVal As Integer
Dim msg As String, strVal As String

intVal = Opts.Value
strVal = Opts.Name

Select Case strVal
    Case "Frame25"
        Select Case intVal
            Case 1
                'code
            Case 2
                'Code
            Case 3
                'Code
        End Select
    Case "Frame34"
        Select Case intVal
            Case 1
                'Code
            Case 2
                'Code
            Case 3
                'Code
        End Select
End Select

msg = msg & " Click :" & intVal

MsgBox msg, , Opts.Name

End Sub

Option Group Items have labels that gives their actual purpose and meaning but all items have index numbers starting with 1.  In the Click Event Procedure we check for the Item index number to determine what to do, like Open a Form or Display Report or Run a Macro or whatever you want to do. 

It always fires the Frame Events and we check for the selected index number.to run a related action.

Now, the Class Modules for all the controls on the Demo Form is ready. 

Note:  There may be controls on the Form which are not enabled with any Event and doesn't fire any Event.  In those cases we don't create the Class Module instances for them.  But, we may read or update those control's values from other Control's Class Module instances. 

Example: We have two Text Boxes on the Tab Control Pages.  These Text Boxes are updated with Computer Name and Windows User Name, from the Tab Control's Class Module.

Now, we have all the sample Class Modules (let us call them the Class Module Templates) for all type of Controls on the Form. Depending on the requirement of a particular Form in your Project create a copy of the required Class Module Templates and customize their existing sub-routine or add new ones to capture required Events and run the related Code.

The Derived Class Module: ClsControls_All

We need an intermediary Class Module, between the stand alone Class Modules of each type of controls and the Form's Class Module,  to organize the Class Modules for the Controls on the Form and  to enable the required Events for them.

We will create a Derived Class Module with all type of controls' Class Modules as it's Properties.  The required Events will be enabled by testing their control names on the Form.

The Derived Class Module ClsControls_All Code is given below:

Option Compare Database
Option Explicit

Private tx As ClsText
Private cmd As ClsCmdButton
Private cbo As Clscombo
Private lst As ClsListBox
Private opt As ClsOption
Private tbc As ClsTabCtrl
Private Coll As Collection
Private fom As Access.Form

Public Property Get p_fom() As Access.Form
    Set p_fom = fom
End Property

Public Property Set p_fom(ByRef objFrm As Access.Form)
    Set fom = objFrm
    Class_init
End Property

Private Sub Class_init()
Dim ctl As Control
Const Evented = "[Event Procedure]"

Set Coll = New Collection

For Each ctl In fom.Controls 'check through Form controls

    Select Case TypeName(ctl) 'pick only the required control type
           Case "TextBox"
                Select Case ctl.Name
                    Case "Text2", "Text4", "Text6"
                        Set tx = New ClsText 'create new instance
                        Set tx.p_frm = fom 'assign Form Object to property
                        Set tx.p_txt = ctl 'assign control to p_txt Property
                        
                        tx.p_txt.AfterUpdate = Evented 'enable AfterUpdate Event
                        tx.p_txt.OnLostFocus = Evented 'enable LostFocus Event
                        
                'Add ClsText Object instance tx to Collection
                        Coll.Add tx
                'Release ClsText Object tx from memory
                        Set tx = Nothing
                End Select
                
            Case "TabControl"
                Set tbc = New ClsTabCtrl 'create instance of ClsTabCtrl
                Set tbc.p_frm = fom 'pass Form Object to p_frm Property
                Set tbc.p_Tb = ctl 'pass Tab Control to p_Tb Property
                
                tbc.p_Tb.OnChange = Evented 'enable OnChange Event
                
            'Add ClsTabCtrl instance tbc to Collection Object
                    Coll.Add tbc
            'Release tbc instance from memory
                Set tbc = Nothing
            
            Case "CommandButton"
                Select Case ctl.Name
                    Case "Command8", "Command9"
                    
                       Set cmd = New ClsCmdButton 'create new instance of ClsCmdButton
                       Set cmd.p_Btn = ctl ' pass Command Button control to p_Btn Property
                       
                       cmd.p_Btn.OnClick = Evented 'enable OnClick Event
                       
            'Add ClsCmdButton instance cmd to Collection Object
                      Coll.Add cmd
            'Release cmd instance from memory
                       Set cmd = Nothing
                End Select
                
            Case "ComboBox"
                Select Case ctl.Name
                    Case "Combo10", "Combo12"
                        Set cbo = New Clscombo ' create new instance of ClsCombo Class
                        Set cbo.p_cbx = ctl ' pass control (Combo10 or Combo12) to CB Property
                        
                        cbo.p_cbx.OnClick = Evented ' enable OnClick Event
                        
            'Add ClsCombo instance cbo to Collection Object
                       Coll.Add cbo
            'Release cbo instance from memory
                      Set cbo = Nothing
                End Select
                
            Case "ListBox"
                Select Case ctl.Name
                    Case "List14", "List16"
                      Set lst = New ClsListBox ' create new instance of ClsListBox
                        Set lst.p_LstBox = ctl ' pass the control to lst.LB Property of instance.
                        lst.p_LstBox.OnClick = Evented ' enable OnClick Event
                        
            'Add lst instance to Collection Object
                       Coll.Add lst
            'Release lst instance from memory
                      Set lst = Nothing
                End Select
                
            Case "OptionGroup"
                Select Case ctl.Name
                    Case "Frame25", "Frame34"
                      Set opt = New ClsOption ' create new instance
                        Set opt.p_Opts = ctl  ' pass control to opt.OB Property
                        
                        opt.p_Opts.OnClick = Evented ' enable OnClick Event
                        
            'Add opt instance to Collection Object
                       Coll.Add opt
            'Release lst instance from memory
                      Set opt = Nothing
                End Select
    End Select
    
Next

End Sub

On the Global declaration area of Class Module ClsControls_All  we have added Class Module of all controls on the Form as Objects with Private Scope.  We have declared a Collection Object and a Form Object as well.

With our earlier trial run experience we have learned that the Collection Object is the easiest and better option than the Class Object instance Arrays.  The Array method  needs separate index counters for each type of control Class Module Objects.  For every new instance of an Object we have to increment the index counters, re-dimension the Array for new Element and so on.

Adding each instance of different type of Control's Class Module to Collection Object is easier and alleviates the need for all the extra steps  mentioned above. 

The Set Property Procedures assigns the Form Object, passed from the active Form, to the fom Property. 

The Class_Init() sub-routine is called from the Set property procedure to enable the Events on each required control on the Form, so that when the Event fires it is captured in their Class Module Sub-Routines.

The Get Property procedure services the outside request for the Form Object. 

The Class_init() sub-routine declares a Control Object ctl and a string constant Evented.

The Collection Object is instantiated as the Object Coll.

The controls on Form, like Text Box, Tab Control etc. will be enabled with required Events, like AfterUpdate, LostFocus, Click or any other on those control's Class Module Object and then add those instances to the Collection Object Item

Remember, the Form Control Object Property, like Text Box was declared with the WithEvents key word, enabling it to capture the Event, when it occurs on the Controls on the Form.  When those Event occurs it is captured in it's Class Module Object instance in the Collection Object and executes the sub-routine code, related to that event.

The For Each . . .Next Loop takes each control on the Form and tests whether it is the required type, like TextBox, TabControl, ComboBox and others.  Controls like Labels, images, activex controls etc., if any, are ignored.

Further the control Name check is performed, within a particular Type of Controls, to enable the required Event for that object.  First we check for the Text Box controls with the names Text2, Text4, Text6. When one of these Text Box control is found the ClsText Class Module is instantiated as tx Object.  The Form object fom is passed to the tx.p_frm property of  tx object. The tx.p_txt  object property is assigned with the Text Control ctl.

In the next step Text2 Text Box is enabled with the AfterUpdate and LostFocus Events. After these initialization steps the ClsText Class Object tx is added to Collection Object Item.

The same process is repeated for Text4 and Text6.  If each Text Box needs different Event to be enabled then they must be put under different Case statements and enable the required Event and add a new instance of the Class Object to the Collection Object.

Since, all the three Text Boxes are enabled with the same AfterUpdate and OnLostFocus Events all their names are put within the same Case statement. 

Note:  There are two more Text Boxes, one each on both Tab Pages.  Even though they are part of the Form controls we have not enabled them with any Events.  They will be used for displaying some values during the execution of Tab Page Change() Event procedure.

The Tab Control's Page Click action runs the Change Event, not Click Event.  By default the first TabCtl18.pages(0) will be current.  When you click on the second TabCtl18.pages(1) the Text Box (with the name UserName) will be updated with the Windows User Name.  When the first tab page is clicked the Text Box on it will be updated with the Computer's Name, with the statement frm.Controls("Computer").Value = Environ("ComputerName") in the ClsTabCtrl.  To address these text box controls directly we have added a Form object frm Property to the Class Module ClsTabCtrl.

All other controls on the Form, Command Buttons,  Combo Boxes, List Boxes and Option Group are enabled with the Click Event only.  Their Class Modules have only Click Event Sub-Routines to capture and display a message for demo purposes. 

If you would like to capture some other Event from a control then add the sub-routine for that event in it's Class Module and enable it in the Derived Class Module ClsControls_All.

The Form: frmControls_All's Class Module Code

Option Compare Database
Option Explicit

Private A As New ClsControls_All

Private Sub Form_Load()
    Set A.p_fom = Me
End Sub

The Derived Class Object ClsControls_All is declared and instantiated as Object A.

The current Form Object is passed to the A.p_fom Property Procedure as it's parameter.  That is the only Code required on the Form's Class Module.

The Demo Run

Download the Demo database from the download-link given at the end of this Article.

When you open the Demo Database the Form frmControlls_All opens in normal View, by default.

Testing Text Box – AfterUpdate, LostFocus Events

Tap on the Tab Key when the insertion point is on the first Text Box, to fire the LostFocus Event.  The Text Box will be inserted with the Text: msaccesstips.com.  This method is good for inserting some default text, if the data field rule is 'not to leave the Text Field empty'.

Make some changes to the text, by adding/removing some text, and then press Tab Key again.  This time the AfterUpdate Event fires and a message box is displayed with the changed text.

The Next two Text Boxes also responds to these Events similarly.

When you press the Tab Key in the Text Box the default text msaccesstips.com is inserted, only when you leave it empty.  If you type some value into the Text Box and press Tab Key then both AfterUpdate and LostFocus Event fires one after other.  But the lostfocus Event will be silent.

Testing Tab Control Page Click Event

By default the first Tab Control Page will be the active page.  Click on the second Tab Control Page.  The Change Event fires and the Text Box on the page is updated with the Windows User Name.

Click on the first Tab Page.  The text box on the first tab page is updated with the Computer Name.

Command  Button Click Event.

Click on the top Command Button.  This will open Form1 displaying some text, with hyperlinks to this Website.

The second Command Button Click displays a message from the ClsText Class Module instance Item from the Collection Object.

Click Events of ComboBox, ListBox, Option Group

All these controls, on the Form frmControls_All , are enabled with the Click Event through the Derived Class Module ClsControls_All and clicking on them will display the selected item in a Message Box.

All the above Class Module based Event enabled Sub-Routine Code is for demonstration of the programming approach only. 

We have developed systematic and customizable Class Module Templates to make VBA Coding much easier than before.  When you start working on a new Project you can make a copy of this Class Module templates and customize it quickly, as per the current project's requirement.  It is easier to debug the code and you know where to look for issues that you may encounter on the field testing stage or while debugging logical errors of your Project.

The Functional Diagram

But, before that if you have not properly understood the intricacies or the arrangement of all the pieces of the puzzle and how they are all related each other in their functions then take a look closely at the diagram below.

I suggest you better take a second look from the beginning Pages of this Series.  The links are given at the bottom of this page. 

If you have a ready to use Access Database then make a copy of it and try to restructure the coding based on what you have learned here.  You will know the difference, when it becomes better organized and easily manageable.

Demo Database

You may download the demo database from the link given below and try out the Form as explained above.





  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink
  10. WithEvents and Report Lines Hiding
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types
Share:

WithEvents Textbox CommandButton Dictionary

Continued from Last Week's Post

So far we have used the Collection Object , to hold all the Class Object instances of the Form Controls as an alternative to Class Object Arrays.

If you have directly landed on this Page then please go through last Week's Post: WithEvents TextBox and CommandButton Control Arrays and then continue on this page.

Last week we have used Class Object Arrays of Text Box and Command Button Controls on the Form.

The difficulty of this method was to maintain separate Array Indexes for each type of control's Class Object instances, besides re-dimensioning each Array element every time.  When there are several type of Form Controls, like Combo Boxes, List Boxes, Option Groups, Tab Control and others this approach will grow into a very complex situation.  Earlier, we were able to alleviate this complexity by using Collection Object, as the container of all the controls Class Object Instances.

We have tried few examples with Collection Object in some of our earlier Post.  Links of all the Posts, on Form and Report Control's Event Capturing topic, are given at the end of this Post for your reference.

Usage of Dictionary Object, Replacing Array

Here, we are going to use the Dictionary Object, instead of Collection Object, to hold all the Form Controls' Class Object Instances.  Let us see how it works and what it takes to implement the Dictionary method.

Here, I will bring in Last week's Derived Class Object (ClsTxtBtn_Derived)  Code without any change.  The full VBA Code of last week's Class Module Derived Object Code is given below:

Option Compare Database
Option Explicit

Private T() As New ClsText
Private B() As New ClsCmdButton
Private m_Frm As Access.Form

Public Property Get mfrm() As Access.Form
  Set mfrm = m_Frm
End Property

Public Property Set mfrm(ByRef frmObj As Access.Form)
  Set m_Frm = frmObj
  init_Class
End Property

Private Sub init_Class()
   Dim ctl As Control
   Dim tCount As Long
   Dim bCount As Long
Const Evented = "[Event Procedure]"

tCount = 0 'counter for textbox controls
bCount = 0 'counter for Command Button controls
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount)  'redimension TextBox Class Objecct Array
         Set T(tCount).p_frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control
         
         Select Case ctl.Name
            Case "Text2" ' enable AfterUpdate and OnLostFocus Events
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text4"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text6"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
        End Select
            
      Case "CommandButton"
         bCount = bCount + 1 'increment counter for CommandButton
         ReDim Preserve B(1 To bCount) 'redimension Button Class Object Array
         Set B(bCount).p_Btn = ctl 'pass CommandButton control
        
        Select Case ctl.Name
            Case "Command8" 'Enable Click Event
                B(bCount).p_Btn.OnClick = Evented
            Case "Command9"
                B(bCount).p_Btn.OnClick = Evented
        End Select
      End Select
    Next
End Sub

The ClsText and ClsCmdButton Classes are defined as Array Objects T() & B() Properties, with an un-declared number of array elements.  The Form Property m_Frm declared as third item followed by the Get/Set Property Procedures for the Form Object. 

After assigning the Form Object in the Set Property Procedure the Class_Init() Sub-Routine is called to enable the required Form controls' Event Procedures.

Three Text Boxes (Text2, Text4, Text6) are enabled with the AfterUpdate() and LostFocus() Events only, because we didn't set up any other Sub-Routine in the Class Module ClsText to capture other Events from the Text Boxes.

Note: Even if we have sub-routine code for, say BeforeUpdate,  GotFocus, KeyDown, KeyUp etc., in the ClsText Class Module we may not use all of them for a particular Project.  Whatever Event we need to capture in a particular Form will only be enabled in the Derived Class Module.  Other sub-routines will remain in the Class Module till we encounter a need for them in a particular Form or Report.

The Form and TextBox control references are passed from the Derived Object to the ClsText Class Object Properties through the following statements:

Set T(tCount).p_frm = m_Frm
Set T(tCount).p_txt = ctl

Command Button Control reference is passed to the ClsCmdButton Class Module.  The Command Button Click Event is enabled.

This procedure repeated for each Control on the Form in the Derived Class Object Module: ClsTxtBtn_Derived.

Logical Error in VBA Code

The ClsTxtBtn_Derived Class Module Code works perfectly for our earlier example.  But, there is a logical error in the code and we will correct it in the later versions of the Code. 

The placement of the following lines of Code under the Case "TextBox are not in the correct location to place, because there is a chance that the Class Module Object will occupy more memory space.

Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount)  'redimension TextBox Class Objecct Array
         Set T(tCount).p_frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control

The issue with the above code placement is that if there are some extra text boxes on the Form, without any Event enabled on them, even then the Class Object will be instantiated for those cases also and added to the Array,  occupying extra memory space.  It will happen silently without any side effects.

The above code lines must be placed immediately below all the Case statements Case "Text2", Case "Text4" and Case "Text6" structures for the correction of the logical error.  This change is required for the Command Button Case statements also.  If all the Text Boxes and Command Buttons on the Form are enabled with some Event then no need for any change.

In all our demo Forms we have introduced two or three Text Boxes or few Command Buttons and enabled some Event on all of them.  In those situations the above Code placement is correct and intentionally made that logical position to keep the code simplified, avoiding duplication of code, under each Case Statement..

The Dictionary Object

If you have not come across the Dictionary Object and it's usage so far then please visit the following links or at least the first two:

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

By Comparing Collection Object with Dictionary, both have the Add method and takes two parameter values: ItemKey and Item.

Dictionary Object Syntax: Object.Add ItemKey, Item – both values mandatory.

Collection Object  Syntax:  Object.Add Item, ItemKey – second parameter  optional.

Unlike Dictionary Object the ItemKey is second parameter in Collection Object and is optional. Since,  all the control names on the Form are unique it can be used as Dictionary Object ItemKey.

Dictionary Object is part of Windows Scripting Language and need to create an Object in VBA with the following statement:

Dim D as Object
Set D = CreateObject("Scripting.Dictionary")

Alternatively you can select and add the Microsoft Scripting Runtime Library File to your Project from Tools - - > References Library List.  After that you can declare Dictionary Object as shown below:

Dim D as Dictionary

If you do this then it has an added advantage of displaying it's list of Properties and Methods, when you type the declared Object name followed by a dot ( say D.)

The new derived Class Module ClsTxtBtn_Dictionary VBA Code is given belowSo far we have used Collection Object as an alternative to Class Object Instance Arrays.  Here we will learn the usage of Dictionary Object as container of Class Object Instances (of TextBox and Command Button).

Option Compare Database Option Explicit Private T As ClsText Private B As ClsCmdButton Private m_Frm As Access.Form

Private D As Object Public Property Get mfrm() As Access.Form Set mfrm = m_Frm End Property Public Property Set mfrm(ByRef frmObj As Access.Form) Set m_Frm = frmObj init_Class End Property Private Sub init_Class() Dim ctl As Control Const Evented = "[Event Procedure]"

Set D = CreateObject("Scripting.Dictionary")

For Each ctl In m_Frm.Controls Select Case TypeName(ctl) 'Type name TextBox or CommandButton ? Case "TextBox" Select Case ctl.Name Case "Text2" Set T = New ClsText 'create new instance of Class Module Set T.p_frm = m_Frm 'pass Form Object Set T.p_txt = ctl 'pass the TextBox control T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item Set T = Nothing 'erase ClsText instance Case "Text4" Set T = New ClsText Set T.p_frm = m_Frm Set T.p_txt = ctl T.p_txt.AfterUpdate = Evented T.p_txt.OnLostFocus = Evented 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T Set T = Nothing Case "Text6" Set T = New ClsText Set T.p_frm = m_Frm Set T.p_txt = ctl T.p_txt.AfterUpdate = Evented T.p_txt.OnLostFocus = Evented 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T Set T = Nothing End Select Case "CommandButton" Select Case ctl.Name Case "Command8" Set B = New ClsCmdButton 'create new instance of ClsCmdButton Set B.p_Btn = ctl 'pass CommandButton control to Class Module B.p_Btn.OnClick = Evented 'Enable Click Event 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item Set B = Nothing 'erase ClsCmdBtn instance Case "Command9" Set B = New ClsCmdButton Set B.p_Btn = ctl B.p_Btn.OnClick = Evented 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B Set B = Nothing End Select End Select Next End Sub

Preparing for a Trial Run

  1. Create a new Class Module in the Demo database, you have downloaded from last week's Post, with the name ClsTxtBtn_Dictionary.
  2. Copy and Paste the above Code into the Class Module and save.
  3. Select Compile . . . from the Debug Menu to recompile the database and ensure that no errors encountered.
  4. Make a Copy of the Form frmClassArray with a new name frmClass_Dictionary.
  5. Open the Form frmClass_Dictionary in design view.
  6. Display the Form's Code Module and change the Code to match the Code lines given below:
    Option Compare Database
    Option Explicit
    
    Private A As New ClsTxtBtn_Dictionary
    
    Private Sub Form_Load()
       Set A.mfrm = Me
    End Sub
    
    
  7. Save the Form with the changed code.
  8. Open the Form in Normal View and try the Text Boxes and Command Buttons to test the LostFocus, AfterUpdate and Command Button Clicks works as before.

Revised Code Segments.

Since, all the three Text Boxes (Text2, Text4 and Text6) are enabled with the same set of Events (AfterUpdate & LostFocus) the Case statements can be clubbed into one line and can avoid duplication of Code as given below:

Select Case ctl.Name
            Case "Text2", "Text4", "Text6"
                Set T = New ClsText 'create new instance of Class Module
                Set T.p_frm = m_Frm 'pass Form Object
                Set T.p_txt = ctl 'pass the TextBox control

                    T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event
                    T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event
                
          'Add ClsTxt Class Object as Dictionary Object Item
                    D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item
                Set T = Nothing 'erase ClsText instance
        End Select

Similarly both the Command buttons have only one common Event, the Click Event.  Hence, their Code also can be combined into a single step, like the following code segment:

Select Case ctl.Name
            Case "Command8", "Command9"
                Set B = New ClsCmdButton 'create new instance of ClsCmdButton
                Set B.p_Btn = ctl 'pass CommandButton control to Class Module
           
                    B.p_Btn.OnClick = Evented 'Enable Click Event
            'Add ClsCmdButton Class Instance as Dictionary Item
                    D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item
                Set B = Nothing 'erase ClsCmdBtn instance
        End Select

The Revised ClsTxtBtn_Dictionary Code.

The full Class Module Code with the above change is given below:

Option Compare Database Option Explicit Private T As ClsText Private B As ClsCmdButton Private m_Frm As Access.Form Private D As Object Public Property Get mfrm() As Access.Form Set mfrm = m_Frm End Property Public Property Set mfrm(ByRef frmObj As Access.Form) Set m_Frm = frmObj init_Class End Property Private Sub init_Class() Dim ctl As Control Const Evented = "[Event Procedure]"

Set D = CreateObject("Scripting.Dictionary")

For Each ctl In m_Frm.Controls Select Case TypeName(ctl) 'Type name TextBox or CommandButton ? Case "TextBox" Select Case ctl.Name Case "Text2", "Text4", "Text6" Set T = New ClsText 'create new instance of Class Module Set T.p_frm = m_Frm 'pass Form Object Set T.p_txt = ctl 'pass the TextBox control T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item Set T = Nothing 'erase ClsText instance End Select Case "CommandButton" Select Case ctl.Name 'Both Command Buttons have only the same Click Event Case "Command8", "Command9" Set B = New ClsCmdButton 'create new instance of ClsCmdButton Set B.p_Btn = ctl 'pass CommandButton control to Class Module B.p_Btn.OnClick = Evented 'Enable Click Event 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item Set B = Nothing 'erase ClsCmdBtn instance End Select End Select Next End Sub

You may create a new Class Module, Copy and Paste the above Code and save.  Change the Form Module Code to incorporate the new name of this Module and try out the Form controls, to test whether all of them works as before.

We have tried only with two type of controls, the Text Box and Command Button class Modules here.  How it will be when we have to deal with all type controls  Combo, List, Tab Control, Option Group on the Form.  All of them needs separate Class Modules, like ClsText and ClsCmdButton.  We will conclude this topic with one or two posts with almost all type of controls on the Form. 

If you have progressed through the earlier Posts (the links: No. 1 to 12) presented below then you will not find any difficulty in defining and setting up Class Modules for those controls mentioned above in a similar way, with Click Event enabled for each one of the yet to be tested form controls.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink
  10. WithEvents and Report Lines Hiding
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types
Share:

WithEvents TexBox and CommandButton Control Arrays

Introduction.

Hope that you have gone through last three Posts on Report based Event Trapping in Class Module and modifying Report controls in real-time.  The links of those Posts are given below for your ready reference, if needed:

  1. WithEvents and Access Report Event Sink
  2. WithEvents and Report Lines Hiding
  3. WithEvents and Report Lines Highlighting

So far we have worked with only Text Box Arrays to capture built-in Events from Form in Class Module  and executes the Sub-Routines, which handles the required validation checks or other needed functions.

TextBoxes Event Capturing Route Map.

  1. Creates a Class Module with a Text Box Control and Form Properties.
  2. Add Sub-Routine(s) for AfterUpdate() and LostFocus(), to handle these built-in Events from Text Box on the Form.  The Text Box name that triggered the Event is identified in the Sub-routine and the Code is executed under the Text Box Name.
  3. Creates a Derived Class Module using the Text Box Class Module as Base Class. 
  4. Creates an Array of the Base Class enabling the required Text Box Events in each Array element, within the Derived Class Object. 
  5. This will eliminate the need for keeping the AfterUpdate() and LostFocus() empty Sub-routine stubs on the Form Module, to Raise those Events on the Form Module, when it occurs.
  6. An Array of Class Module with Text Box Property txt , one array element for each Text Box on the Form, is required because there is only one txt Property in the Base Class Module.   It can hold only one Text Box reference at a time.
  7. Instead of Class Object Arrays,  adding Class Object instances as Collection Object Item is proved as a better Option to hold all the Text Box Class instances.

In brief our technique is to create Class Module, with required Event Procedures, for each type of  control, Text Box, Combo Box, List Box, Tab Control, Option Group separately.  We insert the required Class Module Objects as Base class Properties in the Derived Class Object, depending on the control types on the Form.

Another point to remember is that, if you need to refer to a different control of the same type, other than the one triggered the Event, you must add an Access.Form Object as Property in the Base Class Module and assign the Form Object to that Property through the Derived Class Object.

TextBox and CommandButton Arrays.

So far we have worked with Arrays of Class Module with TextBox Property only.  We will create few Text Boxes and Command Buttons on a Form and learn as how their references are being assigned to instances of  Text Box and Command Button Base Class Module Arrays, in the Derived Class Module.

A particular Class Object Array element captures the built-in Events of a particular Text Box or Command Button and executes the Event Procedure from within the Array Element.

Sample Image of the Form, with the name frmClassArray in Design View is given below:


There are three Text Box Controls on the Form, with the names Text2, Text4, Text6 (need three singly dimensioned array with three elements) and two Command Buttons with the name Command8 and Command9 (an Array of two elements) of their respective Class Modules.

Class Module for TextBox: ClsText.

The Class Module: ClsText Code for Text Box control is given below:

Option Compare Database
Option Explicit

Private frm As Access.Form
Private WithEvents txt As Access.TextBox

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

Public Property Set p_Frm(ByRef pNewValue As Access.Form)
   Set frm = pNewValue
End Property

Public Property Get p_txt() As Access.TextBox
   Set p_txt = txt
End Property

Public Property Set p_txt(ByRef ptxtValue As Access.TextBox)
   Set txt = ptxtValue
End Property

Private Sub txt_AfterUpdate() 'Capture AfterUpdate Event here
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = txt.Name 'save TextBox name
msg = txtName 'save the same name in msg variable for msgbox
varVal = txt.Value 'read the value, if any, from TextBox

'write required code under textbox name
Select Case txtName
    Case "Text2"
        'Code
    Case "Text4"
        'code
    Case "Text6"
        'Code
End Select
'a common message displays for
'all TextBoxes for demo purposes.
msg = msg & ": " & varVal
MsgBox msg, , "AfterUpdate()"

End Sub

Private Sub txt_LostFocus() 'LostFocus actions from TextBoxes are captured here.
Dim txtName As String, varVal As Variant
Dim msg As String, strVal As String
Dim ctl As Control

txtName = txt.Name
varVal = txt.Value
strVal = Nz(varVal, "")

'if any textbox is empty
'then inserts the text: MSAccessTips.com
Select Case txtName
    Case "Text2" ' inserts MSAccessTips.com when empty.
        GoSub Check
    Case "Text4"
        GoSub Check
    Case "Text6"
        GoSub Check
End Select

Exit Sub

Check:
   If Len(strVal) = 0 Then
     txt.Value = "MsAccessTips.com"
   End If
Return

End Sub

The Access.Form Property frm is declared at the beginning of the Module followed by the Access.TextBox Property txt in the next line. 

The txt Property is declared with the key word WithEvents that enables to capture the Events (User-Defined or Built-in Events) taking place on the Form.

The frm Object Property is required to refer to a Text Box control, other than the Event Triggered one, if needed.  Both Properties are declared with Private Scope preventing direct access to the Properties from outside the Class Module.

The Get and Set Property Procedures p_Frm()   for frm Property and p_txt() for txt Property are next steps. The Active Form Object is assigned to the frm Property, through the Form_Load() Event Procedure, not directly but through the Derived Class Module ClsTxtBtn_Derived.

The p_txt() Property Procedures are called from the Derived Class Module (ClsTxtBtn_Derived – the VBA Code is given after the Command Button Class Module ClsCmdButton Code) and the Text Box controls are assigned to each array element of the ClsTxt Class Module.

Once the frm Property is assigned with the Form Object we can set a reference to any control on the Form to read or write values. 

Through the Event capturing Sub-Routines you can set reference to a different Text Box control on the Form to Read/Write value in the following manner:

'Assume Text2 is the active control now
Set mytxt = frm.Controls("Text6")

'Read value from mytxt
    mytxtvalue = mytxt.Value

'Write value to mytxt
    mytxt = 25

The txt_AfterUpdate() Subroutine handles the Text2, Text4, Text6 controls' Events in their respective array elements separately.  You may write validation checks on values in the textbox and display an appropriate message or replace a different value etc.  In this sample subroutine a common message is displayed with the Text Box name, to know that the Sub-Routine is executed on Event Triggering on the Form.

The txt_LostFocus() Event Procedure inserts the text msaccesstips.com, if the text box is left empty otherwise it will not show any message.

Note: When you try the demo run of the attached database tab through the text fields to insert the default text automatically then edit them and press Tab or Enter Key to trigger the AfterUpdate() Event.

Class Module of Command Button: ClsCmdButton.

The Command Button Class Module: ClsCmdButton VBA Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Btn As Access.CommandButton

Public Property Get p_Btn() As Access.CommandButton
  Set p_Btn = Btn
End Property

Public Property Set p_Btn(pBtnValue As Access.CommandButton)
   Set Btn = pBtnValue
End Property

Private Sub Btn_Click()
Dim BtnName As String

BtnName = Btn.Name

Select Case BtnName
    Case "Command8"
        DoCmd.OpenForm "Form1", acNormal
    Case "Command9"
        MsgBox "Thank you, " & BtnName & " Clicked.", , BtnName
End Select

End Sub

The Btn Property is declared with Private Scope and with the key word WithEvents to capture Events, when they fire from the Command Button on the Form.

The Get/Set Property Procedures (p_Btn()) retrieves/assigns the Command Button Object to the Property.

In the Btn_Click() Event Procedure we check the name of the Command Button to determine what to do if a particular Command Button is clicked.

Here, Command8 button click will open a form: Form1.

Command9 button click will only display a message.

The Derived Class Module: ClsTxtBtn_Derived

.We plan to monitor and capture the enabled built-in Events from Text Boxes and Command Buttons on the Form and execute the Event Procedures in the Class Module Object instance. 

We have a Class Module for Text Box controls and another one for Command Buttons.  We will use both as Base Classes for the Derived Object ClsTxtBtn_Derived.

The derived Class Module's VBA Code is given below:

Option Compare Database
Option Explicit

Private T() As New ClsText
Private B() As New ClsCmdButton
Private m_Frm As Access.Form

Public Property Get mfrm() As Access.Form
  Set mfrm = m_Frm
End Property

Public Property Set mfrm(ByRef frmObj As Access.Form)
  Set m_Frm = frmObj
  init_Class
End Property

Private Sub init_Class()
   Dim ctl As Control
   Dim tCount As Long
   Dim bCount As Long
Const Evented = "[Event Procedure]"

tCount = 0 'counter for textbox controls
bCount = 0 'counter for Command Button controls
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount) 'redimension TextBox Class Objecct Array
         Set T(tCount).p_Frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control
         
         Select Case ctl.Name
            Case "Text2" ' enable AfterUpdate and OnLostFocus Events
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text4"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text6"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
        End Select
            
      Case "CommandButton"
         bCount = bCount + 1 'increment counter for CommandButton
         ReDim Preserve B(1 To bCount) 'redimension Button Class Object Array
         Set B(bCount).p_Btn = ctl 'pass CommandButton control
        
        Select Case ctl.Name
            Case "Command8" 'Enable Click Event
                B(bCount).p_Btn.OnClick = Evented
            Case "Command9"
                B(bCount).p_Btn.OnClick = Evented
        End Select
      End Select
    Next
End Sub

The TextBox Class Module ClsText is instantiated as an Array Object with an undeclared number of elements.  Likewise the ClsCmdButton Class Module is also declared in the second line.  The Access.Form Object m_frm is declared to receive the active Form Object passed from the Form_Load() Event Procedure and act as an intermediary to pass the reference to each array element of the ClsText Class Object.

Next the Get/Set Property Procedures mFrm() for the m_frm Form Object controls the Form object retrieval and assignment actions.

In the Set Property Procedure the Class_Init() (this is not the Class_Initialize() sub-routine) sub-routine is called to create separate Class Object Arrays for each TextBox Object and Command Button.

One Control (ctl) Object and two counter variables tcount for TextBoxes and bcount for Command Button Count.  They are used as class objects array indexes during re-dimensioning the ClsText and ClsCmdButton Class Object Arrays.

The Constant Variable Evented is assigned with the string "[Event Procedure]".

Next both counter variables are initialized to zero.

The For Each . . . Next loop takes each control on the Form, checks it's type name whether it is  TextBox or CommandButton control.  The TypeName(ctl) Function gives the type name of the control.

If the control is a text box it's counter variable tcount is incremented by one.  The Text Box Class Module Object T is re-dimensioned for 1 to tcount elements, preserving the earlier array elements' data, with the statement: ReDim Preserve T(1 to tcount). 

Note: In the re-dimension statement, it never says ReDim Preserve T(1 to tcount) As ClsText , as we normally do for normal variables,  like Re-Dim Preserve Qty(1 to cnt) as Single.

Next, the reference of the Form Object copied into the Text Box Class  Module Property in the statement Set T(tcount).p_Frm = m_Frm to the tcountth  Array element object instance.

Next, Set T(tcount).p_txt = ctl passes the Text Box reference to the same object element.

In the next step, we check for the Text Box name to determine which Event to be enabled.  Each Text Box may need different Event to run depending on the requirement of that particular data Field.  Here, we apply a blanket rule of enabling the AfterUpdate and OnLostFocus Events for all Text Boxes for demo purposes.

Hence, the following statements are added for all Text Box controls:

T(tCount).p_txt.AfterUpdate = Evented
T(tCount).p_txt.OnLostFocus = Evented

When the Command Button control is identified it's counter variable bcount is incremented by one and it's Class Object B instance is re-dimensioned for 1 to bcount elements, preserving the earlier elements data, if any.

The Command Button Class Module doesn't have the Form Property.

The current Command Button control reference is passed to the B Object bcountth element through the statement Set B(bCount).p_Btn = ctl.

Next, we check for the Command Button Name and enables the Click Event for each Command Button. 

Note: If all the command Buttons need only the Click Event then we don't need to check for their individual names but the statement B(bCount).p_Btn.OnClick = "[Event Procedure]" immediately after the statement Set B(bCount).p_Btn = ctl is enough.  But, the detailed Code for each Command Button is presented for clarity.

This process is repeated for all Text Boxes and Command Buttons on the Form. Other controls like Labels or any other control on the Form is ignored.

The Form frmClassArray Class Module Code.

Option Compare Database
Option Explicit

Private D As New ClsTxtBtn_Derived

Private Sub Form_Load()
   Set D.mfrm = Me
End Sub

The Form_Load() Event Procedure passes the Form Object to the D.mfrm() Property Procedure of the Derived Class Object ClsTxtBtn_Derived.  The Form Object reference is copied to each  ClsText Class Object Array Element, from the derived Class object.

Downloads

Download the Demo Database from the link(s) below and try out and study the Code.  You may run the Code in Debug Mode by pressing F8 key to step through each executable line of Code.




  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink
  10. WithEvents and Report Lines Hiding
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types
Share:

WithEvents and Report Line Highlighting

Introduction

This is really a re-run of an earlier Post: Highlighting Reports published during August, 2007.  The full Code was written on the Report Class Module itself.  The change here is that we will free up the Report Class Module and transfer all the Code into the Class Module.  The Report Detail Section Print Event is captured in the Class Module Object and runs the Code to highlight the required Report line item.

If you have tried out the sample Reports in the Demo Databases in earlier two Posts then the Code we use in this Class Module (major part) is familiar to you. 

If you have not come across those Articles then their links are given below:

The Highlight of this Project

In the Report Detail Section OnPrint() Event Procedure draws an oval shaped circle, within the boundaries of the Text Box.  The Text Box holds the exam marks from Student's Table.  If the student is not successful in obtaining his/her pass marks of 60% or more then the Marks of such cases are highlighted with an oval shaped circle around the marks.

Sample Image of the Report Demo Run Print Preview is given below:

Even though the Report design is a simple one I would like to draw your attention to the Text Box where we draw the oval shape around the Marks.  

The oval shape is drawn within the boundaries of the Total Text Box.  The size of the Text Box should not be too wide, like the Remarks Label, or too short.  In either case there will not be any problem in attempting to draw the shape inside the boundaries of the text box, but some portion of the shape may overlap or may not  appear correctly around the marks within the Text Box.

The Class Module

More details on this when we go through the Class Module Code.

The Class Module: ClsStudentHighlight Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Rpt As Access.Report
Private WithEvents secRpt As Access.[_SectionInReport]
Private WithEvents secFutr As Access.[_SectionInReport]

Private txt As Access.TextBox
Private max As Access.TextBox
Private pct As Access.TextBox
Private lgnd As Access.Label

Public Property Get mRpt() As Access.Report
   Set mRpt = Rpt
End Property

Public Property Set mRpt(RptNewVal As Access.Report)
Const strEvent = "[Event Procedure]"

On Error GoTo mRpt_Err

  Set Rpt = RptNewVal
  
  With Rpt
     Set secRpt = .Section(acDetail)
     Set secFutr = .Section(acFooter)
     secRpt.OnPrint = strEvent
     secFutr.OnPrint = strEvent
  End With

  Set txt = Rpt.Controls("Total")
  Set max = Rpt.Controls("Maxmarks")
  Set pct = Rpt.Controls("Percentage")
  Set lgnd = Rpt.Controls("Legend")

mRpt_Exit:
Exit Property

mRpt_Err:
MsgBox Err.Description, , "mRpt()"
Resume mRpt_Exit

End Property

Private Sub secRpt_Print(cancel As Integer, PrintCount As Integer)
'  Draw ellipse around controls that meet specified criteria.

Dim m_max As Double
Dim m_pct As Double
Dim curval As Double
Dim pf As Double
Dim pp As Double
Dim yn As Boolean

On Error GoTo secRpt_Print_Err

m_max = max.Value 'read Maxmarks TextBox Value
pp = pct.Value 'read Pass Percentage TextBox value

curval = Nz(txt.Value, 0) 'read obtained marks from Total TextBox
pf = Int(curval / m_max * 100 ^ 2) / 100 'calculate obtained marks percentage
yn = (pf >= pp) 'Passed or Not (TRUE/FALSE)

'call the DrawCircle Subroutine with Pass/Fail flag
'and the Control as parameters
Call DrawCircle(yn, txt)

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print"
Resume secRpt_Print_Exit

End Sub

Private Sub secFutr_Print(cancel As Integer, PrintCount As Integer)
Dim y As Boolean, lbl As Control

On Error GoTo secFutr_Print_Err

y = False 'set the flag false to draw oval shape
Set lbl = lgnd 'pass label control in Page Footer
Call DrawCircle(y, lbl) 'draw circle in legend label

secFutr_Print_Exit:
Exit Sub

secFutr_Print_Err:
MsgBox Err.Description, , "secFutr_Print"
Resume secFutr_Print_Exit

End Sub

Private Sub DrawCircle(ByVal bool As Boolean, ovlCtl As Control)
Dim ctl As Control
Dim bolPrintCircle As Boolean
Dim sngAspect As Single
Dim intShapeHeight As Integer
Dim intShapeWidth As Integer
Dim sngXCoord As Single
Dim sngYCoord As Single

On Error GoTo DrawCircle_Err

If bool Then 'if pass no highlighting, change logic for pass cases
    bolPrintCircle = False
Else 'highlight failed cases
    bolPrintCircle = True
End If

Set ctl = ovlCtl
        
    If Not IsNull(ctl) Then
        If bolPrintCircle Then
           ' change this value to adjust the oval shape of the circle.
            sngAspect = 0.25
   
            ' Determine coordinates of ctl and to draw ellipse.
            ' Determine height and width of ellipse.
            intShapeHeight = ctl.Height
            intShapeWidth = ctl.Width
    
            'calculate circle vertical Y coordinate
            sngYCoord = ctl.Top + (intShapeHeight \ 2)

            'calculate horizontal X coordinate of circile
            sngXCoord = ctl.Left + (intShapeWidth \ 2)
            
            'draw an ellipse around the Total TextBox
            Rpt.Circle (sngXCoord, sngYCoord), intShapeWidth \ 2, RGB(255, 0, 0), , , sngAspect
          bolPrintCircle = False
        End If
    End If


DrawCircle_Exit:
Exit Sub

DrawCircle_Err:
MsgBox Err.Description, , "DrawCircle()"
Resume DrawCircle_Exit

End Sub

In the Class Module Properties the first line declares the Report Object in Rpt Variable.

Next two lines declares the Report Detail and Footer  Sections in secRpt and secFutr Object respectively.

Next three lines declares Text Box Objects for selected Text Boxes in  txt, max and pct Objects to retrieve values from these controls on the Report.

The last Property declaration in the global area is a Label control.  This will be used to draw an oval shape as legend symbol, along with another label with the caption Not Successful indicating what the same symbol appearing around the students  marks signifies..

The Property Get Procedure is actually not required in this Module and it is added here for completeness.  The Rpt Object is not at all accessed from outside this Module.

The Property Set Procedure receives the current Report Object through the Form_Load() Event Procedure as parameter and assigns it to the Rpt Object .

Next the Report Detail and Footer Sections are assigned to secRpt and secFutr Properties respectively.  Next two lines enables their Print Events to capture it when it happens on the Report.

Next three lines assigns the Text Box controls of the Report in txt, max and pct Text Box Properties declared in the global area of the module.

There is an empty label control in the Report Footer Section of the Report with the name Legend.  A Label Property is declared with the name lbl in the global area.  The Legend label is assigned in the lbl Property through the last statement Set lbl = Rpt.Controls("Legend") in the Set Property Procedure.

There are three Sub-Routines in the Class Module.  The Print Event when takes place in the Detail Section the secRpt_Print() runs, and when it happens in the Report Footer Section the sub-routine secFutr_Print() executes.  The third sub-routine DrawCircle() is called from both the above mentioned sub-routines to draw  an oval shape or ellipse around some of the Total Text Boxes in the Detail Section of the Report and on the Legend Label control in the Report Footer Section of the Report.

The Report Detail Section Print Event

When the Report's Detail Section starts printing (during Print Preview not Print View) the Print Event fires and secRpt_Print() sub-routines captures that Event and start executing the Code. 

The Text Box Property (max, pct and txt) values are read into m_max, pp and curval local variables.  The percentage of marks obtained by the student is calculated, up to two decimal places precision.  It is compared with the Pass Percentage (pp) and arrives at the logical result of Passed (TRUE) or Not Successful (FALSE) and saves the result in  Boolean variable yn.

Next, the DrawCircle() sub-routine is called, passing the value in yn as first Parameter and Total Text Box control as second parameter.

The DrawCircle() Sub-Routine.

The DrawCircle() subroutine first checks the first parameter received in Bool variable is TRUE or FALSE.  Accordingly a local Boolean variable bolPrintCircle  is set with the TRUE/FALSE Flag to signal the Circle drawing code segment to draw the oval shape or not to.

In this sample demo we have taken the failed student's cases to highlight the Marks with an oval shape around it.  So when the calculated marks percentage is below 60% then the yn Flag is set to False.  When the yn flag is False the bolPrintCircle is set to True to draw the circle around those values. 

The Text Box's positional values Left, Top and dimension Width and Height values are used to calculate the center point (Horizontal and Vertical point Coordinates) of the circle. Half measure of the Width Value of Text Box determines the Radius of the circle.

If the Text Box is too wide then the circle drawn in the Text Box will loose the top and bottom side of the circle, left and right side of the circle will appear as two Arcs.  The solution is to reduce the vertical radius of the circle (the Aspect Ratio), in relation to the actual radius value calculated based on the width of the Text Box. 

The vertical radius of the circle is reduced to the one-fourth of the horizontal radius or Aspect Ratio of the circle is set as sngAspect = 0.25.  The result is an oval shaped circle around the Text Box value. 

Aligning Text inside the Text Box

The Text Box value is horizontally text-aligned to the center of the Text Box.  But, vertically the value will normally appear near the top edge of the Text Box (and near the top edge of the circle too).  To shift the value down to make it appear somewhere in the middle of the oval shape  vertically, the Top Margin is set with the value 0.1cm manually in design view.  This Property value can be set only at design time, not through Code.

In the Report Footer Section there is a label control with the name Legend.   In the Report Footer_Print() Event calls the DrawCircle() sub-routine with the label control as parameter to draw an oval shape in the label control.  There is another label control with the Caption Not Successful, indicating what the oval shape around the marks of the student signifies.

Report Module Code

Option Compare Database
Option Explicit

Private R As New ClsStudentHighlight

Private Sub Report_Load()
  Set R.mRpt = Me
End Sub

The ClsStudentHighlight Class Module is instantiated in Object R.

On the Report_Load() Event Procedure the current Report Object is assigned to the Property R.mRpt.

Download the Demo database from the link given below and tryout the Report and Code.




  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink
  10. WithEvents and Report Lines Hiding
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types
Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...

Labels

Blog Archive

Recent Posts