Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Add Class Objects as Dictionary Items


We have learned the fundamentals of Dictionary Object, done trial runs for sorting simple items, and displayed Access Table records through Dictionary Object to Ms-Access Form.

Now, let us learn how to add Ms-Access Class Objects to Dictionary and how to retrieve each item and display it’s Property Values and Method output in Debug Window.

If you would like to go back and take a look at earlier related Articles then the links are given below.

We need the Class Object: ClsArea Code in your database to try out this example of Dictionary Object.  If it is not already in there then ClsArea Class Module Code is given below.

Insert a Class Module and change the Name Property Value to ClsArea then copy and paste the following VBA Code in there and save the Module:

Option Compare Database
Option Explicit

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

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

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

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

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

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

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

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

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

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

The ClsArea Class Object have three Properties (strDesc, dblLength, dblWidth) and a method: Area(). We will instantiate this Class Object to calculate the Area of several rectangular shapes or Rooms and add each Class Object instance to Dictionary Object Item.  The value entered in the Class Object’s strDesc Property will be used as Key of each Dictionary Object Item. 

As we already know that the Dictionary Object Keys must be Unique Values. Ensure that no duplication occurs in the Class Object’s strDesc Property Value.

For example, if we have two or three bedrooms to find the area for then their names must be something like Bed Room1, Bed Room2, Bed Room3, and so on.

The ClassObjInDictionary() Procedure Code.

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

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

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

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

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

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

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

End Sub

The VBA Code Line by Line.

At the beginning of the Code, we have created and instantiated the Dictionary Object D.

The Desc String variable is to key in the Description for strDesc Property Value of the Class Object and that value is used as a control for the Do While . . . Loop.  The control is that the Loop will execute till the single character Q (for Quit) is entered into the Variable Desc.  By this method, you can enter any number of Class Objects into the Dictionary Object Items.  When you are done enter Q into the Description Desc Variable to Quit from the Loop.

Next,  creates an instance of the Class Object ClsArea with Object Variable C.

Again the Desc = InputBox() function statement is put within a second Do While . . . Loop,  to ensure that the user keys in some value into the Desc Variable. 

If the user presses Enter Key, Clicks OK, or Cancel Button, without entering some value, then keep the InputBox() function running, till the User types in a Value.

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

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

Now, the ClsArea Class Object is ready to Add to the Dictionary Object.

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

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

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

Why it has to be this way, creating new instances of the Class Object for each Item, is an important point you should know about?

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

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

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

Enter a few Room’s Description, Length, and Width Values to test the Code. Enter the letter Q to complete the Data entry, when you are ready to take a dump of the data into the Debug Window.

A sample Listing is given below:

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

In the Printing Code segment, we have not created an instance of the ClsArea Object C, to read the Class Object Pointers from the Dictionary Item into it, before printing the Values, from the location the Pointer pointing to, into the Debug Window. 

Note: If you feel more comfortable by creating an instance of ClsArea Class Object in C then you may do so.  In this case, both methods work well. 

The Set C = D(mKey) reads the Location Reference (Pointer) of ClsArea Class Object from the Dictionary Item into C and retrieves it’s Property and Method values to print on the Debug Window. 

If you made a sample Run of the Code and understood how the Code works then try running the Code after shifting the Class Object instance creating and removal statements outside the Do While . . . Loop.  After the change run the Code, add few items into Dictionary and let it print the input Class Object  Property Values, and check the output in the Debug Window.

Take a Trial Run With the Following Change in the Code

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

Desc = ""
Set C = New ClsArea

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

I have shifted the statement Set C = New ClsArea to a position above the Do While . . . Loop.  The statement Set C = Nothing below the statement Loop so that it executes only after completing data entry of Class Objects for Dictionary within the Do While . . . Loop.

I have keyed in all the five sample items listed above, in the same order of their names, with different values for Length and Width.

Finally, the printing section lists out all five items in the Debug Window.  But, unfortunately only the last item values are printed for all five Items we have entered.

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

Why it has happened this way?

When we add an Object with its Properties to Dictionary Object only the Reference (Location Address) of the Class Object is saved in Dictionary Object Item. 

When an instance of the Class Object is created with the New Key Word that instance’s Location Address will not change.  The Values keyed into its Properties keeps on overwriting earlier values.  Every time the Class Object’s Location Address is Added to the Dictionary Object Item, instead of the Object Property Values of the Class Object.

By creating different instances of the Class Object, within every cycle of the Loop, it always creates a new Class Object with a different Location Address. The Dictionary Object keep this address for its own reference. 

When the statement Set C = Nothing is executed it sets the Object Variable C not pointing anywhere but the physical data created at the memory location is not deleted and keeps alive by the Pointer saved in the Dictionary Object. 

But, after shifting the Set C = New ClsArea and Set C = Nothing statements, above and below the Do While . . . Loop respectively, we are forced to use only one instance of the Class Object to enter several Class Object property values, one after the other, overwriting the earlier property values.  

Since all the Items in Dictionary Object carries the same Class Object instance’s Reference, the print out displays the last entered Class Object Values only, for all different Items entered into the Dictionary Object.  The Key values are maintained separately and shows correctly in the listing in Debug Window.

Next week we will try how to Add, Edit, Update, Delete Class Objects in Dictionary through Ms-Access Form.


  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes


  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form


  11. Dictionary Object Basics
  12. Dictionary Object Basics-2
  13. Sorting Dictionary Object Keys and Items
  14. Display Records from Dictionary to Form
  15. Add Class Objects as Dictionary Items
  16. Update Class Object Dictionary Item on Form


No comments:

Post a Comment

Comments subject to moderation before publishing.



MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.



Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.


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