Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Display Records from Dictionary to Form

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.

  1. Import the Employees Table from the Northwind sample database.
  2. 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.

  3. Select the Design Form option from the Forms Group of Create Menu.  It will open a Blank Form.

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

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

  6. Click on Add Existing Field Button from the Tools Buttons Group in Design Menu.
  7. Find the Employees Table and Click on the [+] Symbol to show the Employees Table Fields.
  8. 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.

  9. Click on the first Text Box to select it.
  10. Display the Property Sheet (F4) of the selected Text Box.
  11. Remove the data Field Name from the Control Source Property to make the text box Unbound.  Ensure that the Name Property Value remains intact.
  12. 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.

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

  14. If Control Wizard is On, then select the first option and click Next.

  15. Select the Query Option on the next screen, select EmployeeQ and click Next.

  16. In the next Screen double-click on the Last Name to select and insert it into the right panel and click Next.

  17. Select the Last Name in the first text box to sort Last Names in Ascending Order and Click Next.

  18. On the next screen, click Finish.

  19. Change the Name Property Value of the Combo-Box to cboLastName.

  20. Find the Limit to List Property of the Combo Box and change the Value to Yes.

  21. Copy and paste the following SQL into the Row Source Property of the Combo-Box:

    SELECT EmployeesQ![Last Name] FROM EmployeesQ;  
  22. Insert a Command Button in the Footer Section of the Form.

  23. Change the Caption of the Command Button to Exit and Name Property value to cmdClose.

  24. Select the View Code Button from the Tools Group to display the Class Module of the Form.

  25. 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
    
      
  26. 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

  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

    COLLECTION OBJECT

  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form

    DICTIONARY OBJECT

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

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code