LEARN MS-ACCESS TIPS AND TRICKS: Item

Showing posts with label Item. Show all posts

Update Class Object Dictionary Item through Form

a.p.r. pillaiMarch 29, 2019Add, Class Module, Collection Object, Dictionary Object, Item, Key, Objects 1 comment

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:

Create a temporary Object instance.
```
Set tmpObj = New Object
```
Assign values to the temporary object’s Properties.
Add the temporary object instance as an Item to the Dictionary, Collection, or as an element in the Object Array.
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.

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

Open the frmDictionary form, and try adding a few entries to the Dictionary object.
Select an item from the Combo Box to view the property values of the corresponding class object.
Click the Edit command button and modify the Description, Length, and Width values as needed.
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.
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.
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.
The event procedure code has been kept simple, without validation checks, error-handling routines, or safeguards against unintended user actions on the form.
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

Sorting Dictionary Object Keys and Items

a.p.r. pillaiFebruary 21, 2019Array, Data, Dictionary Object, Item, Key, Objects, Time Difference, VBA No comments

Sorting Dictionary Object Keys and Items.

Sorting or indexing table records is a common and important task of organizing data in the correct order, enabling faster retrieval of information through index keys.

In the case of the Dictionary Object, however, there is already a built-in mechanism to directly retrieve information using its unique keys.

Still, if you would like to learn how to sort the values stored in a Dictionary Object, let’s try a simple demonstration. We will reuse the sample data created in earlier example programs as input for our sorting routine. The sample VBA code with the test data is shown below.

The DSort_Test() Main Procedure.

Public Sub DSort_Test()
Dim d As Dictionary
Dim mkey
Dim Title As String
Dim i As Long
Dim vKey() As Variant, vItem() As Variant

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 "Belgium", "Brussels"
d.Add "Italy", "Rome"
d.Add "Canada", "Ottawa"
d.Add "USA", "Washington D.C."
d.Add "Denmark", "Copenhagen"
d.Add "Australia", "Canberra"
d.Add "France", "Paris"
d.Add "Saudi Arabia", "Riyadh"

Title = "UNSORTED LISTING"
GoSub Output_Section

ReDim vKey(1 To d.Count) As Variant
ReDim vItem(1 To d.Count) As Variant

'Load Key,Item pairs into two Variant Arrays
i = 1
For Each mkey In d.Keys
  vKey(i) = mkey
  vItem(i) = d(mkey)
  i = i + 1
Next

'Pass the Array to Bubble Sort Program
Call DBubbleSort(vKey, vItem)

d.RemoveAll 'Remove existing Dictionary Object
Set d = New Dictionary 'instantiate new Dictionary Object

'Re-create Dictionary Object with Sorted Array contents
For i = 1 To UBound(vKey)
   d.Add vKey(i), vItem(i)
Next

Title = "LISTING AFTER SORT"
GoSub Output_Section

Exit Sub

Output_Section:
'Print Sorted Dictionary Object contents
Debug.Print
Debug.Print Title
Debug.Print "---------------------"

For Each mkey In d.Keys
   Debug.Print mkey, d(mkey)
Next
Return

End Sub

In our earlier programs, the Dictionary keys (country names) are being entered manually in alphabetical order. However, in this example, we have intentionally mixed up their order. We will pass this unsorted data to a sorting routine and retrieve it back in alphabetical order.

Unfortunately, the Dictionary Object does not provide a built-in way to rearrange its data directly. To sort Dictionary Data, first copy the keys and their corresponding item from the Dictionary into two separate arrays. The Keys Array can be sorted by a VBA routine and return the data in the desired order. A VBA Sorting Function can take the Dictionary Keys Array and rearrange them in the order you prefer.

Since the dictionary items can be accessed at random by their Keys, sorting the Keys is an unnecessary exercise unless you need some output similar to an Alphabetical Order Printout of Dictionary Items.

The Coding Steps.

The Algorithm of the Code segment, after creating the Dictionary Data items in the above program, is given below.

Take the Listing of Unsorted Data from the Dictionary Object.
Define two Array Variables: One for Keys and another for Item Values (if Items are Objects, then the second declaration must be for an Object of the Item’s Type).
Read Dictionary Keys and Item Values and load them into separate Arrays.
Pass the Arrays to the Sort Routines as ByRef Parameters.
Remove the existing Dictionary Object and instantiate a new dictionary Object, with the same name.
Read the Sorted Keys and Items from the Array and 'Add' them to the new Dictionary Object in sorted order.
Take the sorted data listing from the recreated Dictionary Object.

BubbleSort() Routine.

The Bubble-Sort VBA Code is given below:

Public Sub DBubbleSort(varKey() As Variant, varItem() As Variant)
Dim j As Long, k As Long
Dim tmp1 As Variant, tmp2 As Variant

For j = 1 To UBound(varKey) - 1
   For k = j + 1 To UBound(varKey)
      If varKey(k) < varKey(j) Then 'change < to > for Descending Order
      
'save first Key, Item value pairs in temporary variable
          tmp1 = varKey(j)
          tmp2 = varItem(j)

'replace first set of values with second value set
          varKey(j) = varKey(k)
          varItem(j) = varItem(k)
          
'replace second value set with saved values
          varKey(k) = tmp1
          varItem(k) = tmp2
      End If
   Next k
Next j

End Sub

The Unsorted and Sorted listing dumped in the Debug window image is given below:

UNSORTED LISTING
---------------------
Belgium       Brussels
Italy         Rome
Canada        Ottawa
USA           Washington D.C.
Denmark       Copenhagen
Australia     Canberra
France        Paris
Saudi Arabia  Riyadh

LISTING AFTER SORT
---------------------
Australia     Canberra
Belgium       Brussels
Canada        Ottawa
Denmark       Copenhagen
France        Paris
Italy         Rome
Saudi Arabia  Riyadh
USA           Washington D.C.

The Dictionary Keys, with Item Values, are sorted in Ascending Order.

Sorting in Reverse Order (Z-A).

With a slight change in the Key comparison statement, we can make the program sort the items in Descending Order. Replace the Less Than Symbol (<) with the Greater Than Symbol (>) in the DBubbleSort program to sort the items in Descending Order, as shown below.

Existing comparison statement:

If varKey(k) < varKey(j) Then

change to

If varKey(k) > varKey(j) Then

The QuickSort() Sorts The Data Quickly.

If a Dictionary Object contains a large volume of data, Bubble Sort is not suitable, as it takes more time compared to QuickSort. We have the QuickSort program too for sorting Dictionary Data.

Sample QuickSort VBA Code is given below:

Public Function DictQSort(DxKey As Variant, DxItem As Variant, lngLow As Long, lngHi As Long)
Dim tmpKey As Variant, tmpItem As Variant, midKey As Variant
Dim t_Low As Long, t_Hi As Long

midKey = DxKey((lngLow + lngHi) \ 2)
t_Low = lngLow
t_Hi = lngHi

While (t_Low <= t_Hi)
   While (DxKey(t_Low) < midKey And t_Low < lngHi)
      t_Low = t_Low + 1
   Wend
  
   While (midKey < DxKey(t_Hi) And t_Hi > lngLow)
      t_Hi = t_Hi - 1
   Wend

   If (t_Low <= t_Hi) Then
      tmpKey = DxKey(t_Low)
      tmpItem = DxItem(t_Low)
      
      DxKey(t_Low) = DxKey(t_Hi)
      DxItem(t_Low) = DxItem(t_Hi)
      
      DxKey(t_Hi) = tmpKey
      DxItem(t_Hi) = tmpItem
      
      t_Low = t_Low + 1
      t_Hi = t_Hi - 1
   End If
   
  If (lngLow < t_Hi) Then DictQSort DxKey, DxItem, lngLow, t_Hi 'recursive call
  If (t_Low < lngHi) Then DictQSort DxKey, DxItem, t_Low, lngHi 'recursive call
Wend
End Function

You may run the DictQSort() Program from the main Program DSort_Test(), by replacing the statement that calls the DBubbleSort() Sub-Routine, with a Call to the DictQSort() Function, as shown below:

Replace:

Call DBubbleSort(vKey, vItem)

with

Call DictQSort(vKey, vItem, LBound(vKey), UBound(vKey))

You may not notice any significant difference in execution time between the two programs with this small dataset. However, when data volume is huge, the QuickSort routine completes the task faster than the Bubble Sort program.

In these sorting procedures, the Keys and their corresponding Item values are first copied into two separate arrays before being passed to the sorting routine. Once the data is sorted, it is added back into a new Dictionary Object, and the old one is removed.

We can achieve the same result using a simpler approach. We only need to sort the Keys in the desired order—either ascending or descending. Using these sorted Keys, we can retrieve the corresponding Item values from the original Dictionary and add them to a new Dictionary Object in the sorted order. Finally, the old unsorted Dictionary Object can be discarded.

The modified version of the top program, with a built-in Bubble Sort Code, is given below.

Public Sub DSort_Test2()
Dim d As Dictionary
Dim y As Dictionary
Dim mkey, j As Long, k As Long
Dim Title As String
Dim i As Long
Dim vKey() As Variant

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 "Belgium", "Brussels"
d.Add "Italy", "Rome"
d.Add "Canada", "Ottawa"
d.Add "USA", "Washington D.C."
d.Add "Denmark", "Copenhagen"
d.Add "Australia", "Canberra"
d.Add "France", "Paris"
d.Add "Saudi Arabia", "Riyadh"

Title = "UNSORTED LISTING"
'Print Unsorted Dictionary Object contents
Debug.Print
Debug.Print Title
Debug.Print "---------------------"

For Each mkey In d.Keys
   Debug.Print mkey, d(mkey)
Next

ReDim vKey(1 To d.Count) As Variant
'Load Keys into Variant Array
i = 1
For Each mkey In d.Keys
  vKey(i) = mkey
  i = i + 1
Next
'Bubble Sort the Keys in Ascending Order
For j = 1 To UBound(vKey) - 1
   For k = j + 1 To UBound(vKey)
       If vKey(k) < vKey(j) Then 'Ascending Order
          mkey = vKey(j)
            vKey(j) = vKey(k)
          vKey(k) = mkey
       End If
    Next k
Next j
'end of Sort

'create sorted Data in a new Dictionary Object
Set y = New Dictionary
For j = 1 To UBound(vKey)
  y.Add vKey(j), d(vKey(j))
Next

'Delete old unsorted Dictionary Object d
d.RemoveAll

Debug.Print
Title = "LISTING AFTER SORT"
Debug.Print Title
Debug.Print "---------------------"
For Each mkey In y.Keys
   Debug.Print mkey, y(mkey)
Next

End Sub

In this example, the Dictionary Keys are first loaded into the vKey() Variant Array. The Bubble Sort procedure then rearranges the Keys in the desired order.

Using these sorted Keys, the corresponding Item values are retrieved from the original Dictionary Object and written into a new Dictionary Object, maintaining the order of the sorted country names.

In the subsequent printing section, the sorted country names and their capitals are printed in the Debug Window from the new Dictionary Object.

However, do we really need to recreate a new Dictionary Object after sorting the Keys? Not necessarily. Since Dictionary Items can be accessed randomly using dictionary Keys, sort only the Keys and hold them in an Array. You can then use the sorted Keys in sequence to retrieve the Items from the existing Dictionary in the desired order (A–Z or Z–A). I’ll leave this approach as an exercise for you to try on your own.

MS-ACCESS CLASS MODULE

Dictionary Object Basics-2

a.p.r. pillaiFebruary 09, 2019Add, Dictionary Object, Item, Key, Remove No comments

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.

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

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

It replaces an Item value that matches the specified Item Key.
Example:

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

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:

Canada’s Capital has changed from Alberta to Ottawa.
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.

LEARN MS-ACCESS TIPS AND TRICKS

Update Class Object Dictionary Item through Form

Manage Dictionary Item Through Form.

Class Object Instances as Dictionary Items

The Form Module Event Procedures.

Retrieving the Dictionary Item

Editing Dictionary Item

Private Sub cmdDelete_Click()

Deleting Dictionary Item

Saving Data from the Dictionary Object.

The Form's Class Module Code.

Perform a Demo Run.

Downloads

MS-ACCESS CLASS MODULE

COLLECTION OBJECT

DICTIONARY OBJECT

Sorting Dictionary Object Keys and Items

Sorting Dictionary Object Keys and Items.

The DSort_Test() Main Procedure.

The Coding Steps.

BubbleSort() Routine.

Sorting in Reverse Order (Z-A).

The QuickSort() Sorts The Data Quickly.

MS-ACCESS CLASS MODULE

COLLECTION OBJECT

DICTIONARY OBJECT

Dictionary Object Basics-2

Dictionary Object Properties and Methods.

The Item Property.

The Sample Code with the Item Property Procedure.

Remove, Key, and Count Property Procedures.

Modified Code.

MS-ACCESS CLASS MODULE

COLLECTION OBJECT

DICTIONARY OBJECT

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Useful-Links

Popular Posts

About Me

Blog Archive

Labels

Report Abuse

Statcounter

**The Item Property.**