Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

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 Class Module msaccess forms Animations msaccess animation 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 Collection Object Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Dictionary Object Form Report Calculation Command Button Data Emails and Alerts Query RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 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

Running Sum in MS-Access Query

Introduction. We are familiar with creating Auto-Numbers in Query Column, through an earlier Function created with the Name QrySeq(), publi...

Labels

Blog Archive

Recent Posts