Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Display Records from Dictionary to Form

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 selection from a Combo-box on the Form.

We will add Employee Records to Dictionary Object, with Last Name as Key.  Design a sample Form with a Combo box on the Header Section of the Form and few Text Boxes 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 Table/Query into Dictionary Object and to retrieve 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 go through 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 project.

We need some data to load into the Dictionary Object.

  1. Import the Employees Table from the Northwind.accdb 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;
    

    We will take only few fields of data from the Employees Table.  We can quickly design a Form with field names from EmployeesQ, but without attaching the EmployeeQ to the Form, as record source.  The next steps will be needed to add Text Boxes with correct data field names, without typing them in into the Name Property of the Text Boxes.

    NB: You can give any name to the Text Boxes, it works with any name.

  3. Select Design Form option from 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.

    Sample Design of the Form is given below.

  5. Click on the Detail Section of the Form to make it as active Section.

    Now, we will add six Text Boxes with Employee record field names as Text Box Names.

  6. Click on Add Existing Field Button from 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 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 Last Name of Employees.

  13. Select the Combo-Box control  from the Controls Group under Design Menu and place the Combo-box Control in the Header Section of the Form.  If the Control Wizard is on then follow step 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 in the next screen and select EmployeeQ and click Next.

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

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

  18. In 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 View Code Button from Tools Group to display Class Module of the Form.

  25. Highlight the entire VBA Code given below, Copy and Paste it into the Form’s Class Module, overwriting the existing lines of 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.

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 to add records into 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 number of fields in the record.  Each field value is  added into the Rec Variant Array element and the whole array is inserted into the dictionary Object as a single Item (record), with Last Name field value as unique Dictionary Key.

In the next stage of the code the Dict_Employees Form’s Detail Section area is scanned for Text Boxes and their names are saved into the txtbox  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 for the number of required elements and loads each field value into it’s elements.  

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 Dictionary2003.zip




Download Dictionary2007.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 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 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