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

Introduction.

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, how to retrieve each item, and display its Property Values and Method output in the 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)
    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, 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 the 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, 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:")
    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 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.

In the next step, 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 assign them to 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 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, 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 some Description to 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 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 the 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 its Property and Method values to print in 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 runs the Code, add a 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 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 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 the 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 keep 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 keeps 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 kept 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 carry the same Class Object instance’s Reference, the printout displays the last entered Class Object Values only, for all different Items entered into the Dictionary Object. The Key values are maintained separately and show correctly in the listing in the Debug Window.

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