Add Class Objects as Dictionary Items ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

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

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

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

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

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

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

Option Compare Database
Option Explicit

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

The ClassObjInDictionary() Procedure Code.

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

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

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

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


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

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

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

End Sub

The VBA Code Line by Line.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

A sample Listing is given below:

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

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

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

The statement


Set C = D(mKey)

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

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

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

Take a Trial Run With the Following Changes in the Code

The Do While ... loop segment with suggested changes is given below for 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:")
    Loop
       If Desc = "Q" Then Exit Do
       
    C.strDesc = Desc
    C.dblLength = CDbl(InputBox("Length of " & UCase(Desc) & ": "))
    C.dblWidth = CDbl(InputBox("Width of " & UCase(Desc) & ": "))
    
'add to Dictionary
'Description is added as Key of Dictionary Object
    D.Add Desc, C
    Desc = ""
'Clear Class Object
Loop
    Set C = Nothing

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

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

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

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

Why has it happened this way?

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

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

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

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

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

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

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