Update Class Object Dictionary Item through Form

Manage Dictionary Item Through Form.

Last week, we conducted a trial run to add and retrieve Class Objects from Dictionary Object Items.
One important point we emphasized was that each Class Object added to the Dictionary must be created as a new temporary instance of the Class Object every time it is added.

This practice is not exclusive to Class or Dictionary objects; it is a general best practice followed when adding objects to a Collection, Dictionary, or Object Array, to prevent accidental overwriting of existing instances in memory. A brief review of the correct procedure is given below:

1. Create a temporary Object instance.

   Set tmpObj = New Object
   

2. Assign values to the temporary object’s Properties.

3. Add the temporary object instance as an Item to the Dictionary, Collection, or as an element in the Object Array.

4. Release the temporary object instance from memory so that it can be reused:

   Set tmpObj = Nothing
   

Repeat steps 1 to 4 for each new object you want to add.

If you would like to know why we need a temporary Class Object to add several Class Object Instances to the Dictionary Object, visit the page Add Class Object as Dictionary Object Items.

Class Object Instances as Dictionary Items

Now, let us try adding, editing, updating, and deleting Class Objects as Dictionary Items from an MS Access Form.  We worked with the Form and Dictionary Object about two weeks back, by adding and retrieving simple items in the Dictionary Object. 

This time, we are dealing with Objects and Properties in Dictionary Objects, not simple Items, with added data management functions.  Besides that, when the Form is closed, the Dictionary Item Values (ClsArea Class Object Property Values) are saved to a Table.

We will use our simple ClsArea Class Object, with only three Property Values, to fill in (Description, Length, and Width Values) for our trial run.

Even though the Table is used only at the end of the session, we will design a simple table first with the fields: Description (strDesc), Length (dblLength), and Width (dblWidth), matching the Class Module ClsArea Object Property Names in brackets.

1. Create a new Table named ClsArea with the Field Names and Field Types shown in the Table Design Image below. For Numeric Data Fields, select Double Precision Number Type.

   

   Right-click to open the large image in a New Tab or Window.

2. Next, create a Form with the following design to manage the Dictionary Object data.

   

   Right-click to open the Large Image in a New Tab or Window.

3. Design a Form with the following Controls (not bound to the Table):

   • Combo Box on the Header:

     Name Property Value: cboKey

     Row Source Type = Value List

   • Four Unbound Text Boxes in the Detail Section of the Form with the following names:

     strDesc

     dblLength

     dblWidth

     Area

   • Create another TextBox to the right of the last TextBox with the Name:

     ItemCount

   • Create three Command Buttons below the Text Boxes, on the Detail Section, with the Names:

     cmdAdd and Caption: Add

     cmdEdit and Caption: Edit

     cmdDelete and Caption: Delete

   • Create two Command Buttons in the Footer Section of the Form with the Names:

     cmdSave and Caption: Save to Table

     cmdExit and Caption: Exit

4. The Form’s Normal View Image, with some sample data, is given below:

   

   Right-click to open the Large Image in a New Tab or Window.

As the Form control names indicate, you can enter the Property values of the ClsArea Class Object and add the Class Object as an Item to the Dictionary Object.
The value entered in the strDesc Property will be used as the Key for the Dictionary Item.

Important: Ensure that the Description (strDesc) values are unique for each item. If there is a duplicate key, the Dictionary Object will reject it and trigger an error.
(Extensive validation checks have been intentionally omitted in the code to keep it simple.)

You can also retrieve a specific Class Object’s property values from the Dictionary by selecting its Key from the cboKey Combo Box, then:

• Edit the values directly on the Form and update them back into the Dictionary, or

• Delete the unwanted item from the Dictionary Object entirely.

The Form Module Event Procedures.

There are several Event Procedures defined in the Form’s Class Module.

A brief explanation of each procedure is provided below.

Option Compare Database
Option Explicit

Dim C As ClsArea, xC As ClsArea
Dim D As Object, Desc As String
Dim editFlag As Boolean, saveFlag As Boolean
Dim strV As String

All important Objects and Variables are declared in the Global declaration area.

Private Sub Form_Load()

Private Sub Form_Load()
    Set D = CreateObject(“Scripting.Dictionary)
    Me.cmdEdit.Enabled = False
    Me.cmdDelete.Enabled = False
    saveFlag = False
End Sub

At the Form Load Event, the Dictionary Object is instantiated.

Edit and Delete CommandButtons are disabled because the Form is in data entry mode by default.  The data save detection flag saveFlag is set to False. The flag will be set to True when the Dictionary Data is saved to the Table, from one of two possible selections of Command Buttons at the Footer of the Form.

Private Sub Form_Current()

Private Sub Form_Current()
Dim o As String
    o = cboKeys.RowSource
    If Len(o) = 0 Then
       Me.cboKeys.Enabled = False
    Else
       Me.cboKeys.Enabled = True
    End If
End Sub

Checks whether the Combo box's Row Source property has any Value in it (when the Form is open, it will be empty); if not, disable the Combo box Control.

Private Sub strDesc_GotFocus()

Private Sub strDesc_GotFocus()
 If editFlag Then
  Me.cmdAdd.Enabled = False
  Me.cmdEdit.Enabled = True
  Me.cmdDelete.Enabled = True
 Else
  Call initFields
  Me.cmdAdd.Enabled = True
  Me.cmdEdit.Enabled = False
  Me.cmdDelete.Enabled = False
  editFlag = False
 End If

End Sub

Checks whether the form is in Edit mode.  Edit Mode Flag is set when the user clicks the Edit Command Button.  This happens after the user selects an item from the ComboBox.

If in edit mode, the data entry fields are not cleared, the Edit and Delete Command Buttons are enabled, and the Add Command Button is disabled.

If it is in Add mode, then data fields are emptied in preparation for a new Item, the Add Command Button is enabled, and the Edit and Delete Command Buttons are disabled.

Private Sub dblWidth_LostFocus()

Private Sub dblWidth_LostFocus()
'Area is displayed for info purpose only
  Me!Area = Nz(Me!dblLength, 0) * Nz(Me!dblWidth, 0)
End Sub

On the dblWidth Text Box's LostFocus Event, the product of dblLength * dblwidth is displayed in the Area Text Box.

Private Sub cmdAdd_Click()

Private Sub cmdAdd_Click()
'--------------------------------------------------
'1. Add Class Object as Item to Dictionary Object
'2. Update Combobox RowSource Property
'--------------------------------------------------
Dim tmpstrC As String, tmpLength As Double, tmpWidth As Double
Dim flag As Integer, msg As String
Dim cboVal As String, cbo As ComboBox

editFlag = False
Set cbo = Me.cboKeys

tmpstrC = Nz(Me!strDesc, "")
tmpLength = Nz(Me!dblLength, 0)
tmpWidth = Nz(Me!dblWidth, 0)

flag = 0
If Len(tmpstrC) = 0 Then flag = flag + 1
If tmpLength = 0 Then flag = flag + 1
If tmpWidth = 0 Then flag = flag + 1
If flag > 0 Then
   msg = "Invalid Data in one or more fields, correct them and retry."
   MsgBox msg, , "cmdAdd()"
   Exit Sub
End If

Desc = ""
Set C = New ClsArea 'create a new instance
'add Property Values
    C.strDesc = tmpstrC
    C.dblLength = tmpLength
    C.dblWidth = tmpWidth
    
'add Class Object instance to Dictionary
    
    D.Add tmpstrC, C 'Description is the Key
    
    Call comboUpdate(tmpstrC) 'update description as combobox item
       
    Me.ItemCount = D.Count 'display dictionary Items count
    
    'Call initFields 'set all fields to blanks
    Me.strDesc.SetFocus 'make description field current
    
'Clear Class Object
    Set C = Nothing

End Sub

The TextBox values are moved into temporary variables, and checks are made to see whether any text box is empty.

The ClsArea Class Object Properties are assigned with strDesc, dblLength & dblwidth TextBox values from temporary variables.

Adds the Class Object to the Dictionary Object.

Updates the Combo Box with the Dictionary Object Key from the Class Object's Description (strDesc) Value.

The Dictionary Items Count is displayed on the ItemCount Text Box.

The focus is set in the strDesc field. The text boxes are cleared for entry of new values.

Private Sub cboKeys_AfterUpdate()

Private Sub cboKeys_AfterUpdate()
On Error Resume Next

strV = Me!cboKeys
Set xC = D(strV)
If Err > 0 Then
   MsgBox "Item for Key: " & strV & " Not Found!"
   Exit Sub
End If

Me!strDesc = xC.strDesc
Me!dblLength = xC.dblLength
Me!dblWidth = xC.dblWidth
Me!Area = xC.Area

Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True
Me.strDesc.Enabled = False

On Error GoTo 0

End Sub

Retrieving the Dictionary Item

After adding several items to the Dictionary Object, you can retrieve any item by selecting its Key from the Combo Box. The corresponding details will be displayed on the Form. In this mode, the Add command button and the strDesc field will remain disabled. The strDesc field will be unlocked only when you click the Edit button.

If you select an invalid Key (for example, a Key value that was changed during earlier edit operations) from the Combo Box, an error message — ‘Item for Key: XXXX not found’ — will be displayed, indicating that the specified item does not exist in the Dictionary Object.

Private Sub cmdEdit_Click()

Private Sub cmdEdit_Click()
'Edit the displayed item properties
Dim cap As String
Dim mDesc As String

editFlag = True

strV = Me!cboKeys

cap = Me.cmdEdit.Caption
Select Case cap
   Case "Edit"
      Me.strDesc.Enabled = True
      Me.cmdAdd.Enabled = False 'when editing Add button is disabled
      Me.cmdEdit.Caption = "Update" 'Edit Button Caption is changed
      
   Case "Update" 'Button clicked when Caption is Update
   'directly replace the property value in the Item
      xC.strDesc = Me!strDesc
      xC.dblLength = Me!dblLength
      xC.dblWidth = Me!dblWidth
      mDesc = Me!strDesc 'changed Description is copied to mDesc
      
   If mDesc <> strV Then 'checks with key in combobox value if not same then
      D.Key(strV) = mDesc 'Change Dictionary Key of Item
      
      'update new desc to Combobox
      'old desc also remains in combobox
      Call comboUpdate(mDesc)
   End If
      Call initFields
      Me.strDesc.SetFocus
      Me.cmdAdd.Enabled = True 'Enable Add button to add new item
      Me.cmdEdit.Caption = "Edit" 'change caption from Update to Edit
      Me.cmdEdit.Enabled = False 'disable Edit Button
      Me.cmdDelete.Enabled = False 'disable Edit Button
End Select

End Sub

Editing Dictionary Item

The record is retrieved from the Dictionary and placed on the Form controls. If the data needs to change, then click the Edit CommandButton. The CommandButton click changes its Caption to Update.

Make the required changes to the fields (including the Description, if needed), then click the Update button again to save the changes back to the Dictionary Object.

If the strDesc value (the Dictionary Key) is modified, the corresponding old Key in the Dictionary is replaced with the new Description value. The Combo Box will also be updated to reflect the new Key, though the old Key appears in the ComboBox until it is refreshed.

After the update, the data fields are cleared, the Add button is enabled again, and the Edit and Delete buttons are disabled.

Private Sub cmdDelete_Click()

Private Sub cmdDelete_Click()p
Dim txtKey As String, msg As String

txtKey = Me!cboKeys
msg = "Delete Object with Key: " & txtKey & " ..?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbCritical) = vbYes Then
   D.Remove (txtKey) 'Remove the Item matching the Key
   
   MsgBox "Item Deleted."
   
   Call initFields
   
   Me.strDesc.Enabled = True
   Me.strDesc.SetFocus 'make description field current
   Me.ItemCount = D.Count 'display items count
   Me.cmdAdd.Enabled = True
   Me.cmdEdit.Enabled = False
   Me.cmdDelete.Enabled = False
   
End If

End Sub

Deleting Dictionary Item

When the Delete Command Button is clicked, the current Item on the Form is deleted from the Dictionary, after the user reconfirms it.

The data entry fields are cleared, and the item count control is updated with the reduced number of items.

Private Sub cmdSave_Click()

Private Sub cmdSave_Click()
Dim msg As String

msg = "Form will be closed After Saving Data,"
msg = msg & vbCr & "Proceed...?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbInformation, "cmdSave_Click()") = vbNo Then
  Exit Sub
Else
  Call Save2Table
  DoCmd.Close
End If

End Sub
 

Saving Data from the Dictionary Object.

When you have finished working with the Form and want to save the Dictionary Data Class’s property values from the Dictionary to the temporary Table (clsArea) and to close the Form, click on the 'Save to Table' Command Button.

Private Sub cmdExit_Click()

Private Sub cmdExit_Click()
Dim msg As String
If saveFlag Then
    DoCmd.Close
Else
    msg = "Dictionary Data Not saved in Table!"
    msg = msg & vbCr & vbCr & "Click Cancel to Go back"
    msg = msg & vbCr & vbCr & "Click OK to discard Data and close the Form!"
    If MsgBox(msg, vbOKCancel + vbDefaultButton2 + vbQuestion, "cmdExit()") = vbOK Then
        DoCmd.Close
    Else
        Call Save2Table
        DoCmd.Close
    End If
End If
End Sub

If you choose to click the Exit command button instead of the Save to Table button, you will be prompted to confirm whether you want to save the data. If you decide not to save, you can select the appropriate option to close the form without writing the data to the table.

A few common subroutines that are called from multiple event procedures. Their code is included in the full listing provided below:

• Private Sub initFields()
  Clears all text boxes on the form when called from event procedures.

• Private Sub comboUpdate(ByVal stDesc As String)
  Called from the cmdAdd_Click() and cmdEdit_Click() event procedures to update the Combo Box items.

• Private Sub Save2Table()
  Called from the cmdSave_Click() and cmdExit_Click() event procedures to save the Dictionary Object data to the table.

The Form's Class Module Code.

Highlight, Copy, and Paste the Entire Code below into the Form's Class Module. Save the Form as frmDictionary.

Option Compare Database
Option Explicit

Dim C As ClsArea, xC As ClsArea
Dim D As Object, Desc As String
Dim editFlag As Boolean, saveFlag As Boolean
Dim strV As String

Private Sub Form_Load()
    Set D = CreateObject(“Scripting.Dictionary”)
    Me.cmdEdit.Enabled = False
    Me.cmdDelete.Enabled = False
    saveFlag = False
End Sub

Private Sub Form_Current()
Dim o As String
o = cboKeys.RowSource
If Len(o) = 0 Then
  Me.cboKeys.Enabled = False
Else
  Me.cboKeys.Enabled = True
End If
End Sub


Private Sub cboKeys_AfterUpdate()
On Error Resume Next

strV = Me!cboKeys
Set xC = D(strV)
If Err > 0 Then
   MsgBox "Item for Key: " & strV & " Not Found!"
   Exit Sub
End If

Me!strDesc = xC.strDesc
Me!dblLength = xC.dblLength
Me!dblWidth = xC.dblWidth
Me!Area = xC.Area

Me.cmdAdd.Enabled = False
Me.cmdEdit.Enabled = True
Me.cmdDelete.Enabled = True
Me.strDesc.Enabled = False

On Error GoTo 0

End Sub

Private Sub cmdAdd_Click()
'--------------------------------------------------
'1. Add Class Object as Item to Dictionary Object
'2. Update Combobox RowSource Property
'--------------------------------------------------
Dim tmpstrC As String, tmpLength As Double, tmpWidth As Double
Dim flag As Integer, msg As String
Dim cboVal As String, cbo As ComboBox

editFlag = False
Set cbo = Me.cboKeys

tmpstrC = Nz(Me!strDesc, "")
tmpLength = Nz(Me!dblLength, 0)
tmpWidth = Nz(Me!dblWidth, 0)

flag = 0
If Len(tmpstrC) = 0 Then flag = flag + 1
If tmpLength = 0 Then flag = flag + 1
If tmpWidth = 0 Then flag = flag + 1
If flag > 0 Then
   msg = "Invalid Data in one or more fields, correct them and retry."
   MsgBox msg, , "cmdAdd()"
   Exit Sub
End If

Desc = ""
Set C = New ClsArea 'create a new instance
'add Property Values
    C.strDesc = tmpstrC
    C.dblLength = tmpLength
    C.dblWidth = tmpWidth
    
'add Class Object instance to Dictionary
    
    D.Add tmpstrC, C 'Description is the Key
    
    Call comboUpdate(tmpstrC) 'update description as combobox item
       
    Me.ItemCount = D.Count 'display dictionary Items count
    
    'Call initFields 'set all fields to blanks
    Me.strDesc.SetFocus 'make description field current
    
'Clear Class Object
    Set C = Nothing

End Sub

Private Sub cmdDelete_Click()
Dim txtKey As String, msg As String

txtKey = Me!cboKeys
msg = "Delete Object with Key: " & txtKey & " ..?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbCritical) = vbYes Then
   D.Remove (txtKey) 'Remove the Item matching the Key
   
   MsgBox "Item Deleted."
   
   Call initFields
   
   Me.strDesc.Enabled = True
   Me.strDesc.SetFocus 'select description field current
   Me.ItemCount = D.Count 'display items count
   Me.cmdAdd.Enabled = True
   Me.cmdEdit.Enabled = False
   Me.cmdDelete.Enabled = False
   
End If

End Sub

Private Sub cmdEdit_Click()
'Edit the displayed item properties
Dim cap As String
Dim mDesc As String

editFlag = True

strV = Me!cboKeys

cap = Me.cmdEdit.Caption
Select Case cap
   Case "Edit"
      Me.strDesc.Enabled = True
      Me.cmdAdd.Enabled = False 'when editing Add button is disabled
      Me.cmdEdit.Caption = "Update" 'Edit Button Caption is changed
      
   Case "Update" 'Button clicked when Caption is Update
   'directly replace the property value in the Item
      xC.strDesc = Me!strDesc
      xC.dblLength = Me!dblLength
      xC.dblWidth = Me!dblWidth
      mDesc = Me!strDesc 'changed Description is copied to mDesc
      
   If mDesc <> strV Then 'checks with key in combobox value if not same then
      D.Key(strV) = mDesc 'Change Dictionary Key of Item
      
      'update new desc to Combobox
      'old desc also remains in combobox
      Call comboUpdate(mDesc)
   End If
      Call initFields
      Me.strDesc.SetFocus
      Me.cmdAdd.Enabled = True 'Enable Add button to add new item
      Me.cmdEdit.Caption = "Edit" 'change caption from Update to Edit
      Me.cmdEdit.Enabled = False 'disable Edit Button
      Me.cmdDelete.Enabled = False 'disable Edit Button
End Select

End Sub

Private Sub cmdExit_Click()
Dim msg As String
If saveFlag Then
    DoCmd.Close
Else
    msg = "Dictionary Data Not saved in Table!"
    msg = msg & vbCr & vbCr & "Click Cancel to Go back"
    msg = msg & vbCr & vbCr & "Click OK to discard Data and close the Form!"
    If MsgBox(msg, vbOKCancel + vbDefaultButton2 + vbQuestion, "cmdExit()") = vbOK Then
        DoCmd.Close
    Else
        Call Save2Table
        DoCmd.Close
    End If
End If
End Sub

Private Sub cmdSave_Click()
Dim msg As String

msg = "Form will be closed After Saving Data,"
msg = msg & vbCr & "Proceed...?"
If MsgBox(msg, vbYesNo + vbDefaultButton2 + vbInformation, "cmdSave_Click()") = vbNo Then
  Exit Sub
Else
  Call Save2Table
  DoCmd.Close
End If

End Sub

Private Sub Save2Table()
Dim db As Database, rst As Recordset
Dim recCount As Long, j As Long, item

On Error GoTo Save2Table_Error

recCount = D.Count

Set db = CurrentDb
Set rst = db.OpenRecordset("ClsArea", dbOpenTable)
For Each item In D.Items
   With rst
        .AddNew
         !strDesc = item.strDesc
         !dblLength = item.dblLength
         !dblWidth = item.dblWidth
        .Update
    End With
Next

rst.Close
Set rst = Nothing
Set db = Nothing

saveFlag = True
MsgBox "Data Saved  to Table: ClsArea"

Save2Table_Exit:
Exit Sub

Save2Table_Error:
MsgBox Err & ":" & Err.Description, , "Save2Table_Click()"
Resume Save2Table_Exit

End Sub

Private Sub Form_Unload(Cancel As Integer)
Set D = Nothing
End Sub

Private Sub strDesc_GotFocus()
    If editFlag Then
        Me.cmdAdd.Enabled = False
        Me.cmdEdit.Enabled = True
        Me.cmdDelete.Enabled = True
    Else
        Call initFields
        Me.cmdAdd.Enabled = True
        Me.cmdEdit.Enabled = False
        Me.cmdDelete.Enabled = False
        editFlag = False
    End If

End Sub


Private Sub dblWidth_LostFocus()
'Area is displayed for info purpose only
  Me!Area = Nz(Me!dblLength, 0) * Nz(Me!dblWidth, 0)
End Sub

Private Sub initFields()
'Empty all fields
    Me!strDesc = Null
    Me!dblLength = Null
    Me!dblWidth = Null
    Me!Area = Null
    Me.cmdAdd.Enabled = True
End Sub

Private Sub comboUpdate(ByVal stDesc As String)
Dim cbo As ComboBox, cboVal As String
Set cbo = Me.cboKeys
    cboVal = cbo.RowSource
    cboVal = cboVal & ";" & stDesc
    cbo.RowSource = cboVal
    cbo.Requery
    If Len(cboVal) > 0 Then
      Me.cboKeys.Enabled = True
    End If
End Sub

Perform a Demo Run.

1. Open the frmDictionary form, and try adding a few entries to the Dictionary object.

2. Select an item from the Combo Box to view the property values of the corresponding class object.

3. Click the Edit command button and modify the Description, Length, and Width values as needed.

4. Click the Edit button again (now showing the caption Update) to save the changes in the Dictionary object. If you open the Combo Box afterwards, you will see both the updated item with the new description and the old item with its previous description.

5. Select the old description from the Combo Box. You will receive an error message indicating that the item no longer exists, as its Dictionary key has been updated to the new description.

6. To delete an entry, select an item from the Combo Box and click the Delete command button. The selected item will be removed from the Dictionary object.

7. The event procedure code has been kept simple, without validation checks, error-handling routines, or safeguards against unintended user actions on the form.

8. You may enhance the code with these features, if needed, and reuse it in your own projects.

Downloads

You may download this demo database from the link given below.

Download Dictionary_2003.zip
Download Dictionary_2007.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

Read More

Display Records from Dictionary to Form

Employee Records from Dictionary to Form.

We will now perform a similar exercise to what we did earlier with the Collection Object—displaying Table records on a form based on a key value selected from a ComboBox.

This time, we will use a Dictionary Object to store Employee Records, using the Last Name as the Key.

Design a sample form:

• Create a ComboBox in the Header Section (to list all employees' last names).

• Create a few TextBox controls in the Detail Section (to display the selected employee’s information).

We then write the Form-based event procedures in the Form Class Module.

1. Load data records from a Table or Query into the Dictionary Object.

2. Retrieve a specific record from the Dictionary, based on the selected key (Last Name) from the Combo Box.

3. Populate the TextBoxes on the form with the retrieved record values.

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 a Few Textboxes and a Combo Box.

   We will use only a few fields of data from the Employees table.  We can quickly design a Form with the field names from EmployeesQ, but will not attach EmployeesQ to the Form as a record source.  The next few steps will add TextBoxes with the correct data field names, without typing them into the Name Property of the Text Boxes.

   NB: You may assign any name to the Text Boxes; it works with any name.

3. Select the Design Form option from the Forms Group of the 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 into the Form.

   The Sample Design of the Form is given below.

   

   Right-click to open the large image in a New Tab or Window.

5. Click the Form Detail Section to make it the active Section.

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

6. Click on the 'Add Existing Field' Button from the Tools Buttons Group in the 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 TextBoxes Control Source Property Values.

    In the Form Header Section, we need the Employees LastNames list in a ComboBox.

13. Select the ComboBox control from the Controls Group Menu, in the Design Menu, and create it in the Form Header Section.  If the Control Wizard is active, then follow steps 14 to 18, and go to step 19.

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

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

16. Double-click 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 ComboBox to cboLastName.

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

21. Copy and paste the following SQL into the Row Source Property of the ComboBox:

    SELECT EmployeesQ![Last Name] FROM EmployeesQ;  

22. Insert a CommandButton in the Form Footer Section.

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

24. Select the View Code Button from the Tools group to display the Form's Class Module.

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 named 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, an Object variable D is defined for the Dictionary Object.  The txtbox() array variable is declared for the TextBox Names from the Form’s Detail Section and will display 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 opened for adding records to the Dictionary Object.  A Select Query is created to select a few fields of the Employees Table.

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 identify 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 Module Global area.

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

Right-click to open the Large Image in a New Tab or Window.

When the user selects Last Name from the cboLastName ComboBox, 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 a Dictionary Object Item into it, it automatically re-dimensions itself with the required number of elements and loads each field value into them.  

The next two steps determine the Array Index range values. 

Within the For ... Next Loop, the record values are displayed in the Form TextBoxes, using the TextBox names saved in the Form_Load() Event Procedure.

You may select other names from the ComboBox to display their details on the Form.  When you are ready to close the Form, click on the Exit CommandButton.

When the user selects the Exit CommandButton, the Form closes. 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

Read More

Dictionary Object Basics-2

Dictionary Object Properties and Methods.

Last week’s introduction to the Dictionary Object was, I hope, a useful and informative start for this week’s topic, helping you grasp the fundamentals of its usage in programs. The sample VBA code presented there demonstrated a few of its properties and methods in their simplest form, and I assume you have already tried them out.

If not, I recommend visiting last week’s article, Dictionary Object Basics, before continuing.

The list of properties and methods shown below was introduced in last week’s demonstration programs. You may revisit that page for a quick review or to take a fresh look at it before proceeding.

The list of Properties and Methods we tried out in last week's VBA programs.

1.  Property

• CompareMode

2.  Methods

• Add
• RemoveAll
• Exists
• Keys
• Items

Now, let us try out the following Properties and Methods, which remain to be tried out, and find out how they work in the programs:

1.  Properties

• Item  -  Retrieve/Replace/Add Item Value of the specified Key.
• Key - Replaces the Item Key with the specified new Key-Value.
• Count – Returns the item count

2.  Method

• Remove – Removes the specific Dictionary Item that matches the Key.

There are some similarities in the property names and methods: Key and Keys, Item and Items, Remove and RemoveAll. They do different things but are easier to remember because of name similarities.

The Item Property.

First, we will try out the Item Property.  The item is a multipurpose Property Procedure.

1. It retrieves the Item value for output purposes.  Key-Value is an Item Parameter.

   Example:

Debug.Print d.Item("Canada")
OR
x = d.Item("Canada")
Result: x = "Ottawa"

2. It replaces an Item value that matches the specified Item Key.

   Example:

d.Item("Canada") = "Alberta" ‘ Ottawa replaced with Alberta

3. If the specified Key is not found, then add the given Key and Value pair to the Dictionary Object as a new Item.  It works like the  d.Add  statement if the Key provided doesn’t match any Key in the Dictionary Object.

   Example:

d.Item("Kanada") = "Ottawa" ‘the Key misspelled as Kanada

We will bring the sample code used earlier for demo runs here.

The Sample Code with the Item Property Procedure.

Public Sub Dict_Test0_3()
Dim d As Dictionary
Dim mkey, j As Long
Dim strheader As String

Set d = New Dictionary

'Set Key-Text Compare Mode
d.CompareMode = 1 'Text Compare(nancy = NANCY = Nancy = NaNCy)
 
'Syntax: obj.Add "Key", "Content"

'Countries and Capitals
d.Add "Australia", "Canberra"
d.Add "Belgium", "Brussels"
d.Add "CANADA", "ALBERTA"
d.Add "Denmark", "Copenhagen"
d.Add "France", "Paris"
d.Add "Italy", "Rome"
d.Add "Saudi Arabia", "Riyadh"
d.Add "USA", "Washington D.C."

strheader = "*** LISTING BEFORE CHANGE ***"
GoSub print_section

'Replace ALBERTA with Ottawa
d.item("Canada") = "Ottawa"

'Trying to replace Item Value of
'non-existend Key: India
'This action will ADD a new Item to Dictionary.
d.item("India") = "New Delhi"

strheader = "*** LISTING AFTER CHANGE ***"
GoSub print_section

d.RemoveAll 'clear Dictionary Object from memory

Exit Sub

print_section:

Debug.Print
Debug.Print strheader
Debug.Print "S.No.", "Country", "Capitals"
Debug.Print "-----", "-------", "---------"
j = 1
For Each mkey In d.Keys
   Debug.Print j, mkey, d.item(mkey) 'RETRIEVES Value
   j = j + 1
Next
Return

End Sub

All three actions: Retrieval, Replace & Add are demonstrated in the above code.

We have added eight Items to the Dictionary Object.  I have changed the Capital City of Canada to ALBERTA, in place of the actual Capital, Ottawa, to try out the Replace action.

Immediately after the series of eight Add statements, we call the print_section subroutine, which takes a listing of the unchanged items in the Debug Window from Dictionary Object d. 

In the print_section subroutine, within the For ... Next Loop, find the expression d.item(mkey)  that calls the Item Property Procedure with the Key parameter (Country Name) to retrieve the Capital name of each country and print it, along with the serial number and Country Name, in the Debug Window.

The next executable statement d.Item(“Canada”) = “Ottawa” searches through the list of Keys for  Canada, finds the item, and replaces Alberta with Ottawa. When we call the print_section a second time, the change will appear in the list of Countries and Capitals.

The next executable statement d.Item(“India”) = “New Delhi”  is to watch for its effect.  This statement works similarly to the one we used earlier to replace Canada’s capital, Ottawa. If the search for the key "India" fails, it will not generate an error to indicate the missing key. Instead, it will silently add the unmatched key "India" along with the item value "New Delhi" to the Dictionary object as a new entry.

In the next step, we call the print_section subroutine again to display the updated list of items in the Debug Window. The d.RemoveAll statement then clears all entries from the Dictionary object and releases it from memory before the program stops.

Look for the following changes in the new listing of items in the Debug Window:

1. Canada’s Capital has changed from Alberta to Ottawa.
2. India and the New Delhi pair are added as a new Item in the Dictionary Object, as Key and Item Value, respectively.

Note: If you make a spelling mistake in the Key value, you can easily add a wrong item to the list, and you will not know about it either. 

A workaround is a validation check on the Key-Value before attempting to run the value replacement statement, like the sample code below:

strKey="India"
If d.Exists(strKey) Then
   
   d.Item(strKey)="New Delhi"

Else
   MsgBox "Key: " & strKey & " Not Found!"
End If

Remove, Key, and Count Property Procedures.

In the next Program, we will try out the usage of the Remove Method, Key, and Count Property Procedures. 

The Remove method deletes an Item from the Dictionary Object. Searches for the Key, finds it, and deletes the Item from the Dictionary.

Usage Example:  d.Remove "Canada"

The Key property procedure is useful for editing a key. It accepts a key value as a parameter, locates the existing key, and replaces it with the new one.

Usage Example: d.Key("Kanada")="Canada"

The Count Property Procedure returns the total number of items in the Dictionary Object.

Usage example: i = d.Count

Let us make a copy of the first VBA Code and try out the above method and property procedures in the VBA Code.

Modified Code.

Public Sub Dict_Test0_4()
Dim d As Dictionary
Dim mkey, j As Long
Dim strheader As String

Set d = New Dictionary

'Set Key-Text Compare Mode
d.CompareMode = 1 'Text Compare(nancy = NANCY = Nancy = NaNCy)
 
'Syntax: obj.Add "Key", "Content"

'Countries and Capitals
d.Add "Australia", "Canberra"
d.Add "Belgium", "Brussels"
d.Add "KANADA", "Ottawa"
d.Add "Denmark", "Copenhagen"
d.Add "France", "Paris"
d.Add "Italy", "Rome"
d.Add "Saudi Arabia", "Riyadh"
d.Add "USA", "Washington D.C."

'the d.Count statement adds Item count to the header
strheader = "BEFORE CHANGE - " & "TOTAL ITEMS: " & d.Count
GoSub print_section

'Correction to Key value Kanada changes to Canada
   d.key("Kanada") = "Canada"

'With the following two statement
'an Item is replaced with a new Key.
'This has the same effect as deleting an Item
'and Adding another item in the same place

mkey = "France"
If d.Exists(mkey) Then
   d.key(mkey) = "India"
   d.item("India") = "New Delhi"
Else
   MsgBox "Key: " & mkey & " Not Found!"
End If

'Remove the Item Denmark from Dictionary Object
mkey = "Denmark"
If d.Exists(mkey) Then
   d.Remove mkey
Else
   MsgBox "Remove Key: '" & mkey & "' Invalid." & vbCr & "Item not Removed!"
End If

'the d.Count statement adds Item count to the header
strheader = "AFTER CHANGE - " & "TOTAL ITEMS: " & d.Count
GoSub print_section

d.RemoveAll 'clear Dictionary Object from memory

Exit Sub

print_section:

Debug.Print
Debug.Print strheader
Debug.Print "S.No.", "Country", "Capitals"
Debug.Print "-----", "-------", "---------"
j = 1
For Each mkey In d.Keys
   Debug.Print j, mkey, d.item(mkey) 'RETRIEVES Value syntax: d.item(mkey)
   j = j + 1
Next
Return

End Sub

The d.Remove method requires a valid Key parameter with/without brackets. If the Key is not found, then it will show Errors. We perform a validation check to ensure that the Key exists in the Dictionary Object before attempting to delete the Item.

The d.key statement also requires a valid key value; otherwise, it will result in an error. Therefore, in the following code segment, a validation check ensures that the specified key exists in the Dictionary before attempting to replace it with another value. If the key is not found, a message is displayed to inform the user, preventing the program from crashing.

mkey = "France"
If d.Exists(mkey) Then
   d.key(mkey) = "India"
   d.item("India") = "New Delhi"
Else
   MsgBox "Key: " & mkey & " Not Found!"
End If

By using the d.Key() and d.Item() combined statements, we can fully replace an unwanted item in the Dictionary by updating both its key and its associated value.

Unlike the Collection Object, the true Dictionary Object's strength lies in its use of Keys. Keys allow us to retrieve items quickly and at random from the Dictionary. In addition, the Dictionary Object offers a rich set of methods and property procedures to efficiently manage its contents.

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

Read More