Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Wednesday, January 23, 2019

Table Records in Collection Object and Form

Introduction.

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 the Collection Object's 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 a few records, for experimenting with the Collection Object

The Table image is given below:

The Table Structure image is given below for reference.

The 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 the 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 are also displayed in 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. All Records from Table1, are added as the Collection Object Items in the Form_Load() Event Procedure.  The record description Field (Desc) value is used as the Key parameter value of the Item method.

  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 retrieves the record from the Collection Object, using the Key value chosen 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.

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

The Database object db and rst record set objects are declared, in the Form_Load Event Procedure. Next, the Variable flds declared to store the record fields count.

The next line declares Form, Section, and Control Objects. They are required to look for TextBoxes on the Form and collect their Name Property Values and save them in the txtBox Array. 

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

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

Note: The VBA Code segment/line is commented suitably to understand what they do.  Go through the code line by line a second time to understand them.

The Form_Load() event procedure does the following:

  1. It opens Table1 and reads the first record's field count and saves it into the variable flds.

  2. The Form’s Detail Section is set into the sec Object Variable.

  3. The Detail Section of the Table1 Form is searched for TextBox 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 to the Rec Variant  Array Variable.

  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 Table1 record set is then 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 the 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 following two steps, in the VBA code, 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

2 comments:

  1. When will you explore and experiment with the Dictionary Object?

    ReplyDelete
  2. Soon. Hope you have understood the significance of this page.

    ReplyDelete

Comments subject to moderation before publishing.

Powered by Blogger.