Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Ms-Access Class Module and Collection Object

Last week we had a brief introduction to the Collection Object Basics.  We have learned as how to Add items to a Collection Object and the data item retrieval methods.  We have used mixed data types as Collection Items in our examples and they were intended for demonstration purposes only. 

Normally, Collection Object Items will be one type of values or Objects with their Properties.

We have familiarized with the following Collection Object Methods earlier:

  • Add – to add an Item to the Collection Object
  • Count – returns the Count of Total Items within the Collection Object
  • Item – Takes item sequence number as parameter and returns a specific Item from the Collection.
  • Remove – Takes item number as parameter and deletes the item from Collection.

The Add method have four parameters: Item, Key, Before, After and we have learned their usages too.

Now, let us proceed further with the real world usage of Collection Object with few examples.

The sample VBA Code given below uses the first Ms-Access Class Object ClsArea, we have created earlier, for calculating Area of rectangular Rooms, Objects etc.  We have created Array of Class Module Object ClsArea and worked with them to calculate area of several Rooms.

Now, it is time to learn the usage of Collection Object, instead of Arrays, to create and add several room details into the Collection Object. The items can be retrieved, when needed, and print the details in Debug Window.

  1. Open your database and display the VBA Window (ALT+F11).
  2. Copy and paste the following VBA Code into a Standard Module:
    Public Sub Coll_TestOne()
    Dim Arr As ClsArea
    Dim objColl As Collection, obj As Object
    Dim j As Integer, iMax As Integer
    Dim strName As String
    
    iMax = 5
    
    'instantiate the Collection object in memory
    Set objColl = New Collection
    
    'Create a Collection of five ClsArea Object
    'with it's Property values filled-in
    For j = 1 To iMax
        Set Arr = New ClsArea
        
        strName = "Bed Room" & j
        Arr.strDesc = strName
        Arr.dblLength = 20 + j
        Arr.dblWidth = 15 + j
        
      objColl.Add Arr 'Add current Class Object to Collection
      
        Set Arr = Nothing
    Next
    
    'Printing Section
    Debug.Print "Description", "Length", "Width", "Area"
    For Each obj In objColl
        With obj
            Debug.Print .strDesc, .dblLength, .dblWidth, .Area
        End With
    Next
    
    Set objColl = Nothing
    
    End Sub
    

    Before running the Code, let us take a look at each line and see what it does.

    First line declares Arr as ClsArea Object.  Next line declares ObjColl as a Collection Object and Obj as a generic Object type.  Next line declares two Integer type variables and the line next to that  declares a String variable.

    The iMax variable is initialized with the value 5 for For . . . Next Loop control purposes.  We will create five ClsArea Objects and their property values will be assigned before adding it to the Collection Object, one by one.

    Next executable line instantiates the Collection Object ObjColl in memory.

    Next, a For . . . Next Loop is set up to run for 1 to iMax (or 5) times.  The Arr is instantiated as ClsArea Class Object in memory, in preparation for assigning the first ‘Bed Room1’ Property Values.  The room Description will be created with the constant value Bed Room and with  the control variable j value to the name of the Rooms, as ‘Bed Room1’ to ‘Bed Room5’.

    Like-wise dblLength Property value of the room will be 20+j (or 21 to 25) and dblWidth Property value will be 15+j (16 to 20).

    After assigning of Arr property values the Class Object is added to the Collection ObjColl with the Add  Method.

    The Set Arr = Nothing statement removes the current Class object from memory.

    The For . . . Next Loop repeats to Add four more items into the Collection.

    Next, the For. . . Next Loop, in the Printing Section, uses the Obj Object variable as control to read each item from the Collection and prints the ClsArea Object Property Values in the Debug Window.

  3. Click somewhere in the middle of the Code.
  4. Press CTRL+G to display the Debug Window.
  5. Click somewhere in the code and press F5 Key to run the Code.

The output on the debug window will be as shown below:

Description   Length        Width         Area
Bed Room1      21            16            336 
Bed Room2      22            17            374 
Bed Room3      23            18            414 
Bed Room4      24            19            456 
Bed Room5      25            20            500 

We have not entered the second parameter Key Value in the above example.  The printing section uses an object variable to retrieve each ClsArea Class Object item, in the same order, as they were added to the Collection, and prints it’s property values into the Debug Window.

The following Code snippet can be replaced, in the above Code, to retrieve each item from the Collection Object.  This will use the item sequence number, as parameter to the Item Method to retrieve each object, in the same order as they were added to the Collection.

'Printing Section
Debug.Print "Description", "Length", "Width", "Area"
For j = 1 To objColl.Count
    With objColl.item(j)
        Debug.Print .strDesc, .dblLength, .dblWidth, .Area
    End With
Next

The number of items in the collection is determined by the expression ObjColl.Count in the For . . ..Next Loop.

You may insert a Stop statement above the Printing Section and run the above code again.  When the program pauses execution at the Stop statement, select Locals Window from View Menu. 

Click on the plus symbol [+]ObjColl to display ClsArea Object Items within the Collection Object.

Click on the plus symbol [+]Item 1 to exand the item and display the ClsArea Object Properties and their Values.

Note the dblLength property value is showing as 21.  We will try to change this value by  typing an expression in the Debug Window.  Type the following expression directly in Debug Window and press Enter Key:

ObjColl.Item(1).dblLength = 50

Check the Locals Window now, the value 21 now changed to 50.  You can overwrite the Item Property Values but cannot replace the object item directly. 

If you would like to overwrite an object item, then you must Remove the wrong item first from Collection, with item sequence number as parameter (say Remove 1 ), and Add a new object with whatever changes required to the new item.  You may use the After or Before parameter with proper item number to position the item to the exact area in the Collection Object, from where you have removed the old item.  Check last week’s Article: Ms-Access and Collection Object Basics for clarity on Before or After parameter usage of Add Method.

Let us try another example, with few changes to the above code, to Add items with KEY value into the Collection Object.  In the printing section we will retrieve ClsArea Class Object items from Collection with the use of Key values.

Copy and Paste the following Code into a Standard Module:

Public Sub Coll_TestTwo()

Dim Arr As ClsArea
Dim objColl As Collection
Dim j As Integer, obj As Object
Dim iMax As Integer
Dim Desc As String, strKey As String

iMax = 5

Set objColl = New Collection

For j = 1 To iMax
    Set Arr = New ClsArea
    
    Desc = "Bed Room" & j
    
    Arr.strDesc = Desc
    Arr.dblLength = 20 + j
    Arr.dblWidth = 15 + j
    
    objColl.Add Arr, Desc 'Add Item to Collection with KEY parameter
  
    Set Arr = Nothing
Next

'Print items in reverse order
Debug.Print
For j = objColl.Count To 1 Step -1
    strKey = "Bed Room" & j
    
    Set obj = objColl.item(strKey) 'access items with KEY value
  
    With obj
         Debug.Print .strDesc, .dblLength, .dblWidth, .Area
    End With
Next

Set objColl = Nothing

End Sub

We have declared two more String Variables (Desc) to store the Item Description for ClsArea Object instance property strDesc. The same description will be used as Key value, in the Add method too. 

The strKey variable will be used to create the Key Value in the printing section for retrieving each item object from the Collection.  We can re-use the Desc variable in the printing Section also, for creating the Key Value, but a separate variable strKey is used for clarity.

The  objColl.Add Arr, Desc statemment adds the ClsArea Object instance Arr into the Collection Object Item, with the Description (Bed Room1) in Desc Variable as Key Value to the Collection Object, as first Item.

Other Syntax variants of the above statement are given below for reference.

objColl.Add item:=Arr, key:=Desc 'statement with Parameter Names
  'OR
objColl.Add key:=Desc, item:=Arr 'values in improper order with Parameter Names
  '

In the Printing Section we have set the For . . . Next Loop’s control sequence in reverse order to retrieve the items using the Key Values for a change, from ‘Bed Room5’ to ‘Bed Room1’ order.  The Set ObjColl = Nothing statement clears the Collection Object from memory.

There are few problems with the Key Value of Collection Object:

  1. It accepts only String data type as Key Value.
  2. Once the Key Value is assigned to a Collection item you cannot retrieve the Key Value itself, or create a list of the Keys from the Collection Object.
  3. If you already know the Key Value (like Employee Code) you can retrieve the actual Object Item belongs to that Key from the Collection.
  4. If you attempt to Add an Item with an existing Key Value in the Collection it will trigger an Error.

A better option is the Dictionary Object.  It has much more flexible options to store and retrieve values or Object Items.

We will explore and experiment with the Dictionary Object and it’s methods next week.

  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

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 Utilities msaccess controls Access and Internet Class Module MS-Access Scurity MS-Access and Internet Queries msaccess reports Array External Links msaccess tips Accesstips Menus and Toolbars Objects Downloads MsaccessLinks Process Controls Property Art Work Event Collection Object Controls WithEvents msaccess How Tos Graph Charts VBA msaccessQuery Combo Boxes List Boxes Command Buttons Data Emails and Alerts Form Query Report Calculation Command Button Custom Wizards DOS Commands Data Type Dictionary Object ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference RaiseEvent 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 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 reference restore switch toolbar updating upload

Featured Post

WithEvents and Report Line Highlighting

Introduction This is really a re-run of an earlier Post: Highlighting Reports published during August, 2007.  The full Code was written on ...

Labels

Blog Archive

Recent Posts