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 goes 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.   Selection of an item from the Combo Box, 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.

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 Text Boxes 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  link given at the end of this Page. 

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 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 Text Boxes 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 text boxes  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 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 correct number of elements and data type, when the record is read from the Collection Object Item. 

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


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
Share:

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 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