Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Table Records in Collection Object and Form


Data records from a Table will be Added as the Collection Object Items and Description field values go into the Collection Object as Key Parameter.

The source values for the Combo Box on the Form are also taken from the Description Field of the Table.   The selection of an item from the ComboBox will be used as Collection Object Key, to pick the corresponding record from the Collection Object and displays the field values in text boxes on the Form.  The Form and Text Controls are unbound to the Table.

We have created a small table Table1 with few records, for experimenting with the Collection Object

The Table image is given below:

The Table Structure image is given below for reference.

Sample Demo Form.

We have designed a small form with a Combo box on the header of the Form.  The Row Source Property of the Combo box is set with the following SQL

SELECT [Table1].[Desc] FROM Table1 ORDER BY [Desc]; 

to pick the Desc field value as Row Source of the Combo Box.

Four text boxes with their Child Labels are added in the Detail Section of the Form.  The Text Box Name Property values are set with the same name of each field on the Table, for easier reference in the Program, in the same order, they are appearing on the Table.

The design image of the frmtable1 is given below:

The Normal View of the Form frmTable1, with data displayed from Collection Object, is given below for reference.  The Combo box contents also displayed on the Form.

The following code runs in the frmTable1 Form’s Class Module.  If you have already designed the above Form, ensure that the TextBoxes are set with the field name of the Table structure shown above.  The Combo box name is cmbDesc.  You can download a database with the Code from the link given at the end of this Page. 

Form Module Code.

Copy and Paste the following Code into the frmTable1’s Class Module:

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

This is how it works:

  1. On the Form_Load() Event Procedure each record from Table1 is added as the Collection Object Item.  The record description Field (Desc) value is used as the Key parameter value of the Item.
  2. The Desc field values are also used as Combo Box List values on the Form.
  3. When the user selects an item from the Combo Box, the cmbDesc_AfterUpdate() Event Procedure runs, retrieves the record from Collection Object, using the Key value selected from the combo box, and displays the record field values in the TextBoxes on the Form.
  4. The Objects are cleared from memory when the Form is closed.

At the declaration area of the Module, the Collection Object Coll and an empty txtBox() Array are declared.

In the Form_Load Event Procedure after the Database (db) and recordset (rst) object declarations, the flds variable is declared to store the count of record fields.

The next line declares Form, Section, and Control Objects. They are used for scanning for textboxes on the Detail Section of the Form, collect their Name Property Values and store them in the txtBox() Array for later use. 

The Rec() Variant Array is to store the record field values, before moving the record into the Collection Object as a single Item.

The strKey String Variable is declared to assign the record Desc field value and use it as Key of current record added to the Collection Object.  The Collection Object Key must be unique values.

Note: Each area of code is commented suitably to understand what they do.  Go through the code line by line to understand them.

The Form_Load() event procedure does the following:

  1. Opens Table1 and reads the first record fields count into the variable flds.
  2. The Form’s Detail Section is set into the sec Section Object Variable.
  3. The Detail Section of the frmTable1 Form is scanned for Text Box controls and collects their Names into the txtBox() Array.
  4. Next, the Collection Object is instantiated as the Object Variable Coll.
  5. At the beginning of the Do While . . . Loop the first record field values are added into the Variant Variable Rec Array.
  6. The Description (Desc) field value is saved into the string Variable strKey.
  7. The statement Coll.Add Rec, strKey adds the first record values from the Rec Array as the First Item, with the value in strKey as Item KEY of the Collection Object.
  8. The rst.MoveNext advances the Record Pointer to the next Record and repeats this action till all the records in the table are added to the Collection.
  9. The recordset is closed.

At the Form Load Event Procedure, all the records in the Table are loaded into the Collection Object.  The Combo Box at the Header Section of the Form is loaded with the Description field values of the Table.

When the user selects an item from the Combo Box the cmbDesc_AfterUpdate() Event Procedure runs. 

The Combo Box value is stored into strD and used in the statement R = Coll(strD) to retrieve the Record Array Values from the Collection, using the Key value in strD variable, into the Variant Array Variable R.  The statement R = Coll.Item(strD) is equally valid.

Here, you might have noticed that we have not declared the Variant Variable R as an Array.  VBA automatically dimensions for the correct number of elements and data type, when the record is read from the Collection Object Item. 

The next two steps calculate the Array dimension range (Low and High values) and use them in the For Next . . .  Loop as control values.  The record field values are copied to the TextBoxes on the Form, using the text box names collected in the txtBox Array.

Download the Demo Database.

Download TableColl2003.zip

Download TableColl2007.zip

  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

Ms-Access Class Module and Collection Object


Last week we had a brief introduction to the Collection Object Basics.  We have learned 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 value or Object 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 a parameter and returns a specific Item from the Collection.
  • Remove – Takes item number as a parameter and deletes the item from Collection.

The Add method has 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 an Array of Class Module Object ClsArea and worked with them to calculate the 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.

Adding Class Object to Collection.

  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
    'Printing Section
    Debug.Print "Description", "Length", "Width", "Area"
    For Each obj In objColl
        With obj
            Debug.Print .strDesc, .dblLength, .dblWidth, .Area
        End With
    Set objColl = Nothing
    End Sub

    VBA Code Line By Line.

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

    First-line declares Arr as ClsArea Object.  The next line declares ObjColl as a Collection Object and Obj as a generic Object type.  The 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 them to the Collection Object, one by one.

    The 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 the dblWidth Property value will be 15+j (16 to 20).

    After assigning 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 a 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 a 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

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 expand 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 a 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 the 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.

Save to Collection with Key.

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
'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
      MsgBox "Error: " & Desc & " Already Exists."
   End If
   On Error GoTo 0
   Set Arr = Nothing 'release temporary ClsArea Object

'Print items in reverse order
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

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 statement 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 the first Item.

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

objColl.Add item:=Arr, key:=Desc 'statement with Parameter Names
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 a 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. Wrapper Class Functionality Transformation


  1. Ms-Access and Collection Object Basics
  2. Ms-Access Class Module and Collection Object
  3. Table Records in Collection Object and Form


  1. Dictionary Object Basics
  2. Dictionary Object Basics-2
  3. Sorting Dictionary Object Keys and Items
  4. Display Records from Dictionary to Form
  5. Add Class Objects as Dictionary Items
  6. Update Class Object Dictionary Item on Form



MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.



Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts ListView Control Query VBA msaccessQuery Calculation Event ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Android App Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code