Display Records from Dictionary to Form ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

We will do a similar exercise, that we did for Collection Object, for displaying Table Records retrieved from Collection Object on Form, based on the Key value selected from a Combo-box on the Form.

We will add Employee Records to Dictionary Object, with the Last Name as the Key. Design a sample Form with a Combo box in the Header Section of the Form and a few TextBoxes in the Detail Section, to display the employee information from Dictionary Object, when the Last Name Key value is selected from the Combo Box.

We need Form-based Event Procedure Programs, in the Form’s Class Module, first to load the data records from the Table / Query into Dictionary Object and to retrieve a specific record, as dictated by the Combo-box selection on the Form, and display it in Text Boxes on the Form.

Note: You can download a Demo Database, with the Form and VBA Code, from the bottom of this page.

Let us start with the preparation steps so that you will know what it takes to complete this Project. You will be better informed of the whole process if you plan to implement this method in one of your own projects.

The Employees Table for Sample Data.

We need some data to load into the Dictionary Object.

Import the Employees Table from the Northwind sample database.
Copy and Paste the following SQL String into the SQL editing window and save it with the name: EmployeesQ
```
SELECT Employees.[Last Name], Employees.[First Name], Employees.[E-mail Address], Employees.[Job Title], Employees.[Business Phone], Employees.[Home Phone]
FROM Employees;
```
The Sample Form with Few TextBoxes and a Combo Box.
We will take only a few fields of data from the Employees table. We can quickly design a Form with the field names from EmployeesQ, but without attaching the EmployeeQ to the Form, as a record source. The next steps will be needed to add Text Boxes with correct data field names, without typing them into the Name Property of the TextBoxes.
NB: You can give any name to the TextBoxes, it works with any name.
Select the Design Form option from the Forms Group of Create Menu. It will open a Blank Form.
Right-Click on the Form and select Form Header/Footer to insert Header and Footer sections to the Form.
The Sample Design of the Form is given below.
Click on the Detail Section of the Form to make it an active Section.
Now, we will add six TextBoxes with Employee record field names as TextBox Names.
Click on Add Existing Field Button from the Tools Buttons Group in Design Menu.
Find the Employees Table and Click on the [+] Symbol to show the Employees Table Fields.
Double-Click on the following list of Fields, one by one, to insert them into the Detail Section of the Form:
- Last Name
- First Name
- E-mail Address
- Job Title
- Business Phone
- Home Phone
NB: This is an Unbound Form and the inserted Field controls also must be Unbound Text Boxes.

Keep the Text Box's Name Property Value (Field Name) and remove the Control Source Property Value.
Click on the first Text Box to select it.
Display the Property Sheet (F4) of the selected Text Box.
Remove the data Field Name from the Control Source Property to make the text box Unbound. Ensure that the Name Property Value remains intact.
Remove other Text Box's Control Source Property Values.
Next, we need a Combo Box on the header of the Form with the list of the Last Name of Employees.
Select the Combo-Box control from the Controls Group under the Design Menu and place the Combo-box Control in the Header Section of the Form. If the Control Wizard is active, then follow steps 14 to 18, others go to step 19
If Control Wizard is On, then select the first option and click Next.
Select the Query Option on the next screen, select EmployeeQ and click Next.
In the next Screen double-click on the Last Name to select and insert it into the right panel and click Next.
Select the Last Name in the first text box to sort Last Names in Ascending Order and Click Next.
On the next screen, click Finish.
Change the Name Property Value of the Combo-Box to cboLastName.
Find the Limit to List Property of the Combo Box and change the Value to Yes.
Copy and paste the following SQL into the Row Source Property of the Combo-Box:
```
SELECT EmployeesQ![Last Name] FROM EmployeesQ;  
```
Insert a Command Button in the Footer Section of the Form.
Change the Caption of the Command Button to Exit and Name Property value to cmdClose.
Select the View Code Button from the Tools Group to display the Class Module of the Form.

Highlight the entire VBA Code below, Copy and Paste it into the Form’s Class Module, overwriting the existing lines of Code:

The Form's Class Module VBA Code.

Option Compare Database
Option Explicit

Private D As Object
Dim txtBox() As String

Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset
Dim Rec() As Variant
Dim fldCount As Long, ctl As Control
Dim k As Long, frm As Form, Sec As Section
Dim strKey As String

'Restore the Form to it's actual design size
DoCmd.Restore

'instantiate Dictionary Object
   Set D = CreateObject("Scripting.Dictionary")
   

'Open Recordset Source to save in Dictionary
   Set db = CurrentDb
   Set rst = db.OpenRecordset("EmployeesQ", dbOpenDynaset)
'get recordset fields count
   fldCount = rst.Fields.Count - 1

'Redimension Field Names Array (Rec) for number of fields in Table
   ReDim Rec(0 To fldCount) As Variant

   'Add records to Dictionary Object
   Do While Not rst.EOF
     'Get current record field values into Rec Variant Array
     For k = 0 To fldCount
        Rec(k) = rst.Fields(k).Value
     Next
     'Last Name as Dictionary Key
     strKey = rst.Fields("[Last Name]").Value
     
    'Add record to Dictionary Object with 'Last Name' Key
     D.Add strKey, Rec
     rst.MoveNext
   Loop
   
   'Set current Form
   Set frm = Me
   
'Set Detail Section of Form to look for Text Boxes
   Set Sec = frm.Section(acDetail)
   
'Redim txtBox Array to save Textbox Names on the Form
'To display field values
   ReDim txtBox(0 To fldCount) As String
  
  'Get Text Box Names,from Detail Section of Form, and save them into Array.
  'this will be used in the ComboBox AfterUpdate Event
  k = 0
  For Each ctl In Sec.Controls
     If TypeName(ctl) = "TextBox" Then
        txtBox(k) = ctl.Name
        k = k + 1
     End If
  Next
   
   rst.Close
   Set rst = Nothing
   Set db = Nothing
End Sub


Private Sub cboLastName_AfterUpdate()
  Dim strD As String, R As Variant
  Dim j As Long
  Dim L As Long
  Dim H As Long

'Get Selected Key from ComboBox
 strD = Me![cboLastName]
  
  'Retrieve the record from Dictionary
  'using KEY and load the field
  'Values into the Variant Array
  R = D(strD)
  L = LBound(R)
  H = UBound(R)
'Read Field Values from Array and display
'them into it's corresponding Textbox names on the Form
  For j = L To H
    Me(txtBox(j)) = R(j)
  Next
  Me.Refresh

End Sub

Private Sub cmdClose_Click()
  DoCmd.Close
End Sub


Private Sub Form_Unload(Cancel As Integer)
  'Clear Dictionary Object from Memory
  Set D = Nothing
End Sub

Save the Form with the name Dict_Employees or any other name you prefer.

How it All Works Together.

There are four Subroutines in the above Code.

Private Sub Form_Load() Event Procedure.
Private Sub cboLastName_AfterUpdate()
Private Sub cmdClose_Click()
Private Sub Form_Unload(Cancel As Integer)

In the Declaration area of the Module defined an Object variable D for Dictionary Object. The txtbox() array variable is declared for storing the Text Box names from the Form’s Detail Section and will be used for displaying the selected record’s field values.

The Dictionary Object is created In the Form_Load Event Procedure,

Immediately after instantiating the Dictionary Object the EmployeesQ Recordset is open for adding records to the Dictionary Object. A Select Query is created to pick only selected fields of the Employees Table, rather than using the Employees Table directly.

The Rec() Variant Array is Re-dimensioned for the number of fields in the record. Each field value is added to the Rec Variant Array element and the whole array is inserted into the dictionary Object as a single Item (a record), with the Last Name field value as a unique Dictionary Key.

In the next stage of the code, the Dict_Employees Form’s Detail Section area is scanned to look for Text Boxes and their names are saved into the textbox() Array, for use in the cboLastName_AfterUpdate() Event Procedure. The txtbox() Array was declared in the Global area of the Module.

When the user selects a name from the Combo Box the Private Sub cboLastName_AfterUpdate() Event Procedure is executed. The Form's Normal View image is given below:

When the user selects the Last Name from the cboLastName Combo Box it is saved into the String Variable strD. The statement R=D(strD) reads the corresponding employee record field values array into the Variant Variable R.

We have not explicitly defined the Variant Variable R as an Array. But when we read an Array of values from Dictionary Object Item into it, it automatically re-dimensions itself for the number of required elements and loads each field value into its elements.

The next two-step determines the Array Index range values.

Within the For. . .Next Loop the record values are displayed in the Form Text Boxes, using the test box names we have saved in the Form_Load() Event Procedure.

You may select other names from the Combo box to display their details on the Form. When you are ready to close the Form, click on the Exit Command Button.

When the user clicks on the Exit Command Button the Form is closed. Before closing the Form the Form_Unload Event is triggered and the Dictionary Object is cleared from Memory.

Download the Demo Database.

Download Dictionary2003.zip

Download Dictionary2007.zip

MS-ACCESS CLASS MODULE

grovelliMarch 12, 2019 at 4:47 PM
When I try to download Dictionary2007.zip from https://drive.google.com/file/d/1OQcQ7PtKMHFRQ1tc7SMvUDjQVtTCSYSO/view I get a message, "Can't connect securely to this page. This might be because the site uses outdated or unsafe TLS security settings. If this keeps happening try contacting the website's owner."
Could you post a link to OneDrive?
grovelliMarch 14, 2019 at 7:53 PM
Now the download works :-)