Class Module and Collection Object.
Steps to Create a Data View Form
-
Create the Form
-
Use the built-in Access Form Wizard to create a Data View Form with the required fields from your table or query.
-
-
Add a ComboBox in the Form Header
-
Insert a ComboBox in the Form Header Section.
-
Set its Row Source to a field with unique values (for example, [Last Name]) from the form’s record source table/query.
-
The ComboBox will serve as a record key, allowing you to randomly retrieve the selected record from the Collection Object and display its values in unbound TextBoxes.
-
-
Insert a Hidden TextBox for the Key Field
-
Add an unbound TextBox in the Header of the form and name it
KeyField. -
Set its Visible property to
False. -
In its Control Source, enter the expression:
-
If [Last Name] alone does not provide unique values, create a query that concatenates First Name and Last Name into a single expression (e.g.,
FullName: [FirstName] & " " & [LastName]). -
Use this query as the form’s record source and populate the ComboBox with this new field instead.
-
-
Add a Close Button in the Footer
-
Insert a Command Button in the Form Footer.
-
Name it
cmdClose. -
Set its Caption property to Close.
-
-
Copy VBA Code
-
Open the Form1 Module from the demo database.
-
Copy its VBA code and paste it into the module of your newly created Employees Form.
-
-
Save and Close
-
Save the form.
-
Close the form to complete the setup.
-
-
The Data View Form runs on the ready-made VBA code in the DATA_View Class Module. This Class Module is fully reusable—any form created using the same method with any Table or Query as the source data can use it without modification.
Once the form is opened in Normal View, simply select an item from the ComboBox. The record with the matching key value is instantly retrieved from the Collection Object and displayed in the unbound TextBoxes. Since this form is designed strictly for data viewing, all TextBoxes are locked to prevent editing.
Creating this form is extremely quick and straightforward:
-
You don’t need to write any code in the Form Module.
-
The Form Wizard automatically places and arranges the TextBoxes properly.
-
With just the simple steps outlined earlier, the entire setup can be completed in about five minutes—and the form is ready to run with the DATA_View Class Module.
The Ready-made Reusable Form Module Code:
Option Compare Database Option Explicit Private Cls As New DATA_View Private Sub Form_Load() Set Cls.o_frm = Me End Sub Private Sub Form_Unload(Cancel As Integer) Set Cls = Nothing End Sub
The DATA_View Class Object is instantiated in the Employees Form Module, and the current Form Object is assigned to the o_Frm() Property of the DATA_View Object.
The ready-made, reusable DATA_View Class Module Code.
Option Compare Database
Option Explicit
Private WithEvents cbo As ComboBox
Private WithEvents cmd As CommandButton
Private oFrm As Form
Private frmSec As Section
Private Coll As New Collection
Private txtBox() As String
Private strTable As String
'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'With Reusable Code
'------------------------------------------------------
'Quick Data View Screen
'Saving Table/Query Records in Collection Object
'Author: a.p.r. pillai
'Date : 26/04/2024
'Remarks: Keep Recordset in Collectuon Object
' : and Retrieve specific record using Key
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Property Get o_frm() As Form
Set o_frm = oFrm
End Property
Public Property Set o_frm(ByRef vfrm As Form)
Set oFrm = vfrm
Set frmSec = oFrm.Section(acDetail)
Call Class_Init
End Property
Private Sub Class_Init()
Dim db As Database
Dim rst As Recordset
Dim flds As Integer
Dim ctl As Control
Dim k As Integer
Dim Rec() As Variant, strKey As String
Dim vKeyName As String
strTable = oFrm.RecordSource
Set cmd = oFrm.cmdClose
cmd.OnClick = "[Event Procedure]"
Set cbo = oFrm.cboName
cbo.OnClick = "[Event Procedure]"
'Make the Data Field TextBoxes Unbound
'Save the Field Names on the Form into the txtBox() Array
flds = 0
For Each ctl In frmSec.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.ControlSource = ""
flds = flds + 1
ReDim Preserve txtBox(1 To flds) As String
'Get the selected Field Names from the
'TextBoxes on the Form's Detail Section
txtBox(flds) = ctl.Name
ctl.Locked = True
End Select
Next
'Set ComboBox Default Value
'Change Form Properties
oFrm.cboName.DefaultValue = "=[cboName].[column](0,0)"
oFrm.RecordSelectors = False
oFrm.NavigationButtons = False
oFrm.ScrollBars = 0
'Load the Table/Query Records into Collection Object
ReDim Rec(1 To flds) As Variant
Set db = CurrentDb
Set rst = db.OpenRecordset(strTable, dbOpenSnapshot)
'------------------------------
vKeyName = oFrm!KeyField 'Collection Key Field Value
'------------------------------
Do While Not rst.EOF
For k = 1 To flds
Rec(k) = rst.Fields(txtBox(k)).Value
Next
'Key Field Name in the Form Fields
'=========================================
strKey = rst.Fields(vKeyName).Value
'=========================================
Coll.Add Rec, strKey 'Save Rec() Array
rst.MoveNext
Loop
Set rst = Nothing
Set db = Nothing
End Sub
Private Sub cbo_Click()
Dim strKy As String, Record As Variant
Dim j As Long, L As Long, H As Long
'Get Selected Collection Key from ComboBox
strKy = cbo.Value
'Retrieve the record using Key from Collection
'and load into Variant Array Record
Record = Coll(strKy)
L = LBound(Record)
H = UBound(Record)
'Add Field Values into corresponding Text Boxes
For j = L To H
oFrm(txtBox(j)) = Record(j) 'Display in Unbound TextBox
Next
oFrm.Requery
End Sub
Private Sub cmd_Click()
DoCmd.Close acForm, oFrm.Name
End Sub
Private Sub Class_Terminate()
Do While Coll.Count > 0
Coll.Remove 1
Loop
End Sub
Data_View VBA Code Segment-wise Review.
Note: You can instantiate the single DATA_View Class Module across multiple data display forms within the same project. If needed, you can even keep several of these forms open simultaneously and work with them independently. There’s no need to duplicate the Class Module or its VBA code—one module efficiently serves them all.
The Global Declarations.
The ComboBox and Command Button controls are declared with the WithEvents keyword to capture their events when triggered on the form. Following this, a Form object (oFrm) and a Form Section object (frmSec) are declared.
A Collection object (Coll) is then instantiated, along with the txtBox() array (a string array with an unspecified number of elements) and the string variable strTable, which stores the name of the form’s record source (table or query).
The next section defines the Form Get and Set property procedures used to capture the active form object passed from the Form_Load() Event procedure. In the Set property procedure, after assigning the vFrm parameter (the received form object) to the oFrm property, the Employees form’s Detail section reference is assigned to the frmSec object, and after that the Class_Init() subroutine is called.
At the beginning of the Class_Init() Subroutine the statement:
strTable = oFrm.RecordSource
reads the Form's Record Source Property value and retains it in the strTable Variable.
The Command Button and Combobox Object References from the Form are assigned to the cmd and cbo Objects, respectively, and are enabled with the click Events.
'Make the Data Field TextBoxes Unbound
'Save the Field Names on the Form into the txtBox() Array
flds = 0
For Each ctl In frmSec.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.ControlSource = ""
flds = flds + 1
ReDim Preserve txtBox(1 To flds) As String
'Get the selected Field Names from the
'TextBoxes on the Form's Detail Section
txtBox(flds) = ctl.Name
ctl.Locked = True
End Select
Next
'Set ComboBox Default Value
'Change Form Properties
oFrm.cboName.DefaultValue = "=[cboName].[column](0,0)"
oFrm.RecordSelectors = False
oFrm.NavigationButtons = False
oFrm.ScrollBars = 0
The For…Next loop scans through the Detail section of the form, retrieves the names of all TextBox controls (which correspond to the source data field names), and loads them into the txtBox() array. At the same time, it calculates the total number of TextBox controls and stores this value in the Flds variable.
Each TextBox is then made unbound by setting its ControlSource property to an empty string (ctl.ControlSource = ""). The txtBox() array is dynamically redimensioned at each iteration, increasing its size by one element while preserving the data already stored. The final count of fields is captured in the Flds variable.
This approach ensures that the code automatically adapts to any changes—fields added or removed from the form by the user—without requiring manual adjustments. The ComboBox's default Value is set with the Statement: =[cboName].[column](0,0).
The next four statements change the Form Properties.
vKeyName = oFrm!KeyField
The expression value, such as '="[Last Name]"', is read and the KeyField name (Last Name) is assigned to the variable vKeyName. The field used as the Collection Object key must contain unique values, and the same field data must also be present in the ComboBox cboName. This ensures that selecting an item from the ComboBox can be used to randomly retrieve the corresponding record from the Collection Object.ReDim Rec(1 To flds) As Variant
Set db = CurrentDb
Set rst = db.OpenRecordset(strTable, dbOpenSnapshot)
'----------------------------
vKeyName = oFrm!KeyField
'----------------------------
Do While Not rst.EOF
For k = 1 To flds
Rec(k) = rst.Fields(txtBox(k)).Value
Next
'Key Value Field Name in the Form Fields
'=========================================
strKey = rst.Fields(vKeyName).Value
'=========================================
Coll.Add Rec, strKey
rst.MoveNext
Loop
Rec() The array is redimensioned to match the number of data fields on the form. The field values are then read from the source table or query, one record at a time, and stored in the corresponding elements of the Rec() array. Each completed Rec() array is added as a single item in the Collection object, with the Last Name field used as the collection item key (the second parameter of the Collection object’s Add() method). Through this process, source data records are loaded efficiently into the Collection Object.
Note: The source table or query may contain more fields than those placed on the form by the Form Wizard. However, the program only processes the fields whose names appear on the form; any additional fields in the source are ignored. You may freely add or remove fields from the form or rearrange their positions as needed. Just ensure that the Name property of each field remains unchanged and matches a corresponding field in the record source table or query. No changes to the VBA code are required.
The cbo_Click() Event Subroutine.
Private Sub cbo_Click()
Dim strKy As String, Record As Variant
Dim j As Long, L As Long, H As Long
'Get Selected Collection Key from ComboBox
strKy = cbo.Value
'Retrieve the record using Key from Collection
'and load into Variant Array R
Record = Coll(strKy)
L = LBound(Record)
H = UBound(Record)
'Add Field Values into corresponding Text Boxes
For j = L To H
oFrm(txtBox(j)) = Record(j)
Next
oFrm.Requery
End Sub
When a user selects an item from the ComboBox, the selected value is used as the Collection Object Item Key to retrieve the corresponding record and load it into the Record() array.
The array element values are read in the same order they were stored in memory, and the unbound TextBoxes are populated accordingly, based on the order in which their names were read from the form. The TextBoxes are locked and cannot be edited.
The Data_View Class Module and accompanying Form Module code can be reused for any form created using this procedure, without modification. Ensure that the ComboBox is named cboName, and the Command Button is named cmdClose.
Data field names are taken directly from the Name property of the TextBoxes created by the Form Wizard in the Detail section of the form.
This approach allows you to create a fully functional Data View Form in a few minutes, complete with ready-to-use code.
Demo Database Download
Data_InColl.zipStreamlining Form Module Code in Standalone Class Module.
- Reusing Form Module VBA Coding for New Projects.
- Defining Custom Events in Microsoft Access Part Two
- Objects and Their Built-in Events Part 3.
- Standalone Class Module and Events - Part Four
- Several TextBoxes and Event Capturing Part Five
- Class Objects and Wrapper Classes - Part Six
- Form Module vs. Reusable Class Module Coding Demo - Part Seven
- Form VBA Structured Coding ACCESS USERS-GROUP.ORG Europe Presentation-7A
- Collection Object Replaces Class Object Array - Part Eight
- Reusability of Streamlined VBA Code - Part Nine
- Organizing Wrapper Classes for Different Forms - Part Ten
- ComboBox and Option-Group Wrapper Classes - Part Eleven
- Report Module Code in Class Module - Part Twelve
- Hiding Report Lines Conditionally - Part 13
- Form Report Detail Sections Event Handling - Part 14
- The Event Firing Mechanism in Access Objects-22
- One TextBox and Three Wrapper Class Instances-23
- Class for All Data Entry Editing Forms-30
- Wrapper Class Module Creation Wizard-v1
- Wrapper-Class-Template-Wizard-v2 - Final
- New Custom-Made Form Wizard VBA - Part 15
- New Custom-Made Report Wizard - Part 16
- Streamlining VBA External Files List in Hyperlinks-17
- Streamlining Event Procedures 3D-Text Wizard-18
- Streamlining Form Module VBA RGBColor Wizard-19
- Form VBA Structured Coding Numbers to Words Converter-20
- Streamlining Code Synchronized Floating Popup Form-24
- Streamlining Code Compacting/Repair Database-25
- Streamlining Code Remainder Popup Form-26
- Streamlining Code Editing Data in Zoom-in Control-27
- Streamlining Code Filter By Character and Sort-28
- Table Query Records in Collection Object-29
