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)
    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 have three Properties (strDesc, dblLength, dblWidth) and a method: Area(). We will instantiate this Class Object to calculate Area of several rectangular shapes or Rooms and add each Class Object 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 bed rooms to find area for then their names must be something like: Bed Room1, Bed Room2, Bed Room3 and so on.

Let us try out Dictionary with the Class Module Object ClsArea as Items. Sample VBA Code for Dictionary Object is given below.  Copy and Paste it in 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

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 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 collects 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 it’s 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 last statement within the Loop.  That means we are creating New Class Object instance for each Item in the Dictionary Object.

Why it has to be this way, 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.

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 works 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.

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:")
    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 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 list 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 it’s 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 it’s 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 instance of the Class Object, within every cycle of the Loop, it always creates new Class Object with different Location Address. The Dictionary Object keep this address for it’s 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
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 How Tos Functions 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 Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database 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 Excel 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 function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

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 captu...

Labels

Blog Archive

Recent Posts