Table Records in Collection Object and Form ~ LEARN MS-ACCESS 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:

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.
The Desc field values are also used as Combo Box List values on the Form.
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.
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:

It opens Table1 and reads the first record's field count and saves it into the variable flds.
The Form’s Detail Section is set into the sec Object Variable.
The Detail Section of the Table1 Form is searched for TextBox controls and collects their Names into the txtBox() Array.
Next, the Collection Object is instantiated as the Object Variable Coll.
At the beginning of the Do While . . . Loop the first record field values are added to the Rec Variant Array Variable.
The Description (Desc) field value is saved into the string Variable strKey.
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.
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.
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.