January 2019 ~ LEARN MS-ACCESS TIPS AND TRICKS

Option Compare Database Option Explicit Private Coll As Collection Dim txtBox() As String Private Sub Form_Load() Dim db As Database Dim rst As Recordset Dim flds As Long, k As Long

Dim frm As Form, Sec As Section, ctl As Control Dim Rec() As Variant, strKey As String 'Open Table1 to upload records into Collection Object Set db = CurrentDb Set rst = db.OpenRecordset("Table1", dbOpenDynaset) 'get record fields count flds = rst.Fields.Count - 1

'Set Detail Section of Form to scan for Text Boxes Set frm = Me Set Sec = frm.Section(acDetail) 'Redim txtBox() to save Textbox names from Form 'to display field values ReDim txtBox(0 To flds) As String 'Get Text Box Names & save into txtBox() Array from Detail Section of Form 'this will be used in ComboBox AfterUpdate Event Procedure k = 0 For Each ctl In Sec.Controls If TypeName(ctl) = "TextBox" Then txtBox(k) = ctl.Name k = k + 1 End If Next

'instantiate Collection Object Set Coll = New Collection

'Redimension Rec Array for number of fields in Table ReDim Rec(0 To flds) As Variant 'Add each record into the Collection Object Do While Not rst.EOF 'Get current record field values into Rec Variant Array For k = 0 To flds Rec(k) = rst.Fields(k).Value Next

'Description Field Value as Key strKey = rst.Fields("Desc").Value 'Add record to the Collection Object with Key Coll.Add Rec, strKey rst.MoveNext Loop rst.Close Set rst = Nothing Set db = Nothing End Sub

Private Sub cmbDesc_AfterUpdate() Dim strD As String, R As Variant Dim j As Long, L As Long, H As Long 'Get Selected Collection Key from ComboBox strD = Me![cmbDesc] 'Retrieve the record from Collection 'using Collection KEY and save the field 'Values into the Variant Variable R = Coll(strD) L = LBound(R) H = UBound(R) 'Add Field Values into corresponding Text Boxes For j = L To H Me(txtBox(j)) = R(j) Next Me.Refresh End Sub Private Sub Form_Unload(Cancel As Integer) 'Remove Collection from Memory on Form Close Set Coll = Nothing End Sub

Introduction.

Last week, we had a brief introduction to the basics of the Collection object. We learned how to add items to a Collection and retrieve them using various methods. In our earlier examples, we used mixed data types as Collection items, but those were intended purely for demonstration purposes.

In practical use, a Collection typically holds items of a single data type or objects (such as class modules) along with their properties and methods.

We have already become familiar with the following Collection object methods:

Add – Adds an item to the Collection.
Count – Returns the total number of items in the Collection.
Item – Takes an item’s sequence number (index) as a parameter and returns that specific item.
Remove – Takes an item’s sequence number (index) as a parameter and deletes that item from the Collection.

The Add method also has four parameters—Item, Key, Before, and After—whose usage we explored earlier.

Now, let us move on to a real-world example of using the Collection object.

The sample VBA code below demonstrates how to use the ClsArea class (which we created earlier to calculate the area of rectangular rooms or objects) with a Collection object. Previously, we used an array of ClsArea class objects to calculate the area of several rooms. This time, we will use a Collection object instead of an array to create and store details of multiple rooms. We can then retrieve these items as needed and display their details in the Debug window.

Adding a Class Object to the Collection.

Open your database and display the VBA Window (ALT+F11).

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

The VBA Code Line By Line.
Before running the Code, let us take a look at each line and see what it does.
The first line declares Arr as a ClsArea object. The next line declares it ObjColl as an Collection object and Obj as a generic Object type. The following line declares two integer-type variables, and the line after that declares a String variable.
The iMax variable is initialized with the value 5 to control the For...Next loop. We will create five ClsArea objects, assign their property values, and then add them one by one to the Collection object.
Next, the ObjColl collection object is instantiated in memory.

A For...Next loop is then set up to run from 1 to iMax (or 5). Within the loop, it Arr is instantiated as a new ClsArea class object in memory, ready to receive its first set of property values. The room description will be created by combining the constant text "Bedroom" with the loop control variable j, resulting in room names from "Bedroom1" to "Bedroom5".
Similarly, the dblLength property value of each room will be set to 20 + j (resulting in values from 21 to 25), and the dblWidth property will be set to 15 + j (resulting in values from 16 to 20).
After assigning the property values to Arr, the class object is added to the ObjColl collection using the Add method.
The statement Set Arr = Nothing then releases the current class object from memory.
The For...Next loop continues this process to create and add four more ClsArea objects to the collection.

Next, another For ... Next loop in the printing section uses the Obj object variable to iterate through each item in the collection and print the ClsArea object property values in the Debug Window.

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

The output in 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 provided the second parameter (Key value) in the above example. In the printing section, an object variable is used to retrieve each ClsArea class object from the Collection in the same order in which they were added, and then print their property values in the Debug Window.

The following code snippet can be used as a replacement in the above program to retrieve each item from the Collection object. This version uses the item’s sequence number as a parameter to the Item method to fetch each object, preserving the order in which 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 code. When the program pauses at the Stop statement, open the Locals Window from the View menu.

Click the plus symbol [+]ObjColl to expand it and display the ClsArea object items stored within the collection.

Next, click the plus symbol [+]Item 1 to expand the first item and view its ClsArea object properties and their current values.

Notice that the dblLength property value is shown as 21. We will now attempt to change this value by typing an expression directly in the Immediate (Debug) Window.

Type the following expression in the Debug Window and press Enter:

ObjColl.Item(1).dblLength = 50

Check the Locals Window now — you will see that the value 21 has changed to 50. This demonstrates that you can overwrite the property values of an object already stored in the collection. However, you cannot directly replace the object item itself in the collection.

If you want to replace an entire object item, you must first remove the existing item from the collection by specifying its sequence number as a parameter (for example: ObjColl.Remove 1). Then, create a new object with the required changes and add it back to the collection.

To place the new item back in the same position where the old item was removed, you can use the Before or After parameter of the Add method along with the appropriate item number.

For more details, refer to last week’s article “MS-Access and Collection Object Basics” for a clear explanation of the Before and After parameter usage of the Add method.

Save to Collection with Key.

Let us try another example by making a few changes to the previous code. This time, we will add items to the Collection Object using Key values. In the printing section, we will retrieve the ClsArea Class Object items from the Collection by referencing their Key values instead of their index numbers.

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
'instantiate Collection Object
Set objColl = New Collection

For j = 1 To iMax
'instantiate a temporary ClsArea Object
    Set Arr = New ClsArea
    
    Desc = "Bed Room" & j
 'Assign Property Values
    Arr.strDesc = Desc
    Arr.dblLength = 20 + j
    Arr.dblWidth = 15 + j
    
   On Error Resume Next
'Validate Collection Key, ClsArea Object with the same Key exists or not
   Set obj = objColl.Item(Desc)
   
   If obj Is Nothing Then ' doesn't exists, Add the ClsArea Instance
      objColl.Add Arr, Desc 'Add Item to Collection with KEY parameter
   Else
      MsgBox "Error: " & Desc & " Already Exists."
   End If
   
   On Error GoTo 0
   Set Arr = Nothing 'release temporary ClsArea Object
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 introduced two additional String variables, including Desc, to store the item description for the ClsArea object’s Arr.strDesc property. This same description will also serve as the Key value in the Add method.

The strKey variable will be used when retrieving each ClsArea object from the Collection by its key. Although we could reuse the Desc variable for this purpose, we use a separate strKey variable for better clarity.

The statement objColl.Add Arr, Desc adds the ClsArea object instance Arr to the Collection, using the description (for example, "Bedroom1") stored in the Desc variable as the key for this 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 arranged the For ... Next loop to run in reverse order, retrieving items from the Collection by their Key values—starting from "Bed Room5" down to "Bed Room1".
The statement Set ObjColl = Nothing is used to clear the Collection object from memory.
However, there are a few limitations when using Key values with a Collection object:
It accepts only the String data type as a Key value.
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.
If you already know the Key Value (like Employee Code), you can retrieve the actual Object Item belonging to that Key from the Collection.
If you attempt to add an Item with an existing Key Value in the Collection, it will trigger an Error.

A better alternative is the Dictionary object, which provides greater flexibility for storing and retrieving items or values compared to the Collection object.

We will explore and experiment with the Dictionary Object and its methods next week.