Assigning Images to Tree View Nodes-2

Introduction.

I hope you’ve enjoyed the past few tutorial sessions on Microsoft TreeView and ImageList Control, and that you’re ready to dive into the next installment. If you haven’t had a chance to go through the earlier articles yet, you’ll find the links to the previous episodes below.

• Microsoft Tree View Control Tutorial
• Creating an Access Menu with a Tree View Control
• Assigning Images to Tree View Nodes

This post continues from last week’s episode.

In the previous session, we created a set of images and uploaded them into the ImageList Control using VBA. The ImageList reference was then assigned to the TreeView Control’s ImageList property, allowing us to directly use either the image Key Names or Index Numbers as the [Image] and [SelectedImage] parameters in the Nodes.Add() method of the TreeView object.

With these steps, we were able to design a visually appealing Microsoft Access Project Menu featuring a hierarchical structure, tree lines, and custom node images.

Last Week’s Trial Run: Form with Node Images.

Below is the sample Access Project Menu created last week, showing the node images alongside the tree lines:

VBA Code for Uploading Images into the ImageList Object.

The following VBA procedure was used to upload images into the ImageList Control and make them available for use in the TreeView nodes:

Dim tvw As MSComctlLib.TreeView
Const KeyPrfx As String = "X"
Dim objimgList As MSComctlLib.ImageList


Private Sub CreateImageList()
Dim strPath As String

'Initialize Tree View Object
Set tvw = Me.TreeView0.Object
'Clear the Tree View Nodes, if any.
tvw.Nodes.Clear

'Initialize ImageList Object
Set objimgList = Me.ImageList0.Object
’the images must be saved in database path
strPath = CurrentProject.Path & “\”

With objimgList.ListImages
'Key Names are Case sensitive.
    .Add , "FolderClose", LoadPicture(strPath & "folderclose2.jpg")
    .Add , "FolderOpen", LoadPicture(strPath & "folderopen2.jpg")
    .Add , "ArrowHead", LoadPicture(strPath & "arrowhead.bmp")
    .Add , "LeftArrow", LoadPicture(strPath & "LeftArrow.bmp")
    .Add , "RightArrow", LoadPicture(strPath & "RightArrow2.bmp")
End With

With tvw
    .ImageList = objimgList
End With

End Sub

The Purpose of Using Meaningful Images

In our last example, we did not just use random icons. Instead, we assigned purpose-driven images that represent the actual behavior of the menu items.

• Root-level Nodes (Folder Images):

  • FolderClose → Used for the normal view of the Root Node. This indicates that its child items are currently hidden.

  • FolderOpen → Displayed when the Root Node is clicked, showing that its child items are now visible.

  • Note: Once expanded, clicking again will not revert the image back to FolderClose while the Node is in focus. However, depending on your TreeView0_NodeClick() event procedure, the child nodes may collapse and hide again.

• Child Nodes (Arrow Images):

  • LeftArrow → A small, left-facing arrow for the default (unclicked) state of child nodes.

  • RightArrow → A slightly larger arrow pointing right, displayed when a child node is clicked. This provides a clear visual cue that the item is active.

The highlighted words above are the Key Names that were defined in the ImageList control and then referenced in the Nodes.Add() method.

When the user clicks a child node, the action not only updates the image but also triggers the opening of the linked object (Form, Report, or Macro), depending on which child node is selected.

The ImageList control on the Form.

The ImageList Control on the frmMenu Form, highlighted in Design View, is shown below.

The ImageList Control Property Sheet.

The ImageList’s Property Sheet Image is given below for reference:

Review of Last Week’s Exercise and Preparations.

In last week’s session, we worked with 16 x 16 pixel images and uploaded them into the ImageList Control using VBA code. After loading the images, we assigned the ImageList Object reference to the TreeView Control’s ImageList property.

This setup allowed us to use either the Index Number or the Key Value of each image in the Add() method parameters of TreeView Nodes.

A few important points from last week’s method:

• We did not specify the first (Index) parameter of the ImageList’s Add() method. Instead, this method itself automatically generates the Index Numbers sequentially.

• When you use Key Values in the Nodes.Add() methods [Image] and [SelectedImage] parameters, remember that Key values are case-sensitive.

• For consistency, it is a good practice to enter Key values in the ImageList Control’s Key text box in lowercase letters.

---

The Easy Way: Loading Images Without VBA

As promised, this week we’ll look at the simpler, no-code approach. Instead of writing VBA code to load images, you can:

1. Open the ImageList control on your form.

2. Manually adding images one by one, directly from your disk.

3. Assign a Key value (lowercase preferred) or rely on the automatically assigned Index number.

That’s it! No VBA required.

---

Advantages of the Manual Method

• Quick setup: Ideal when your image set is finalized and not expected to change frequently.

• Portability: You can copy and paste the ImageList control into another project form, and all the images remain intact. This makes it easy to reuse your ImageList across multiple Access applications.

• Direct referencing: Just like in the VBA method, you can still use either the Key value or the Index number in the Nodes.Add() method of the TreeView Control.

---

Preparing for This Week’s Demo.

If you have already downloaded last week’s demo database (ProjectMenuV21.accdb), open it now. Inside, you’ll find a saved copy of the form frmMenu renamed as frmMenu2.

We’ll be using frmMenu2 this manual image loading method.

Adding an ImageList Control to the frmMenu2 Form.

1. Open the form frmMenu2 in Design View.

2. Insert the Microsoft ImageList Control from the ActiveX Controls List, somewhere on the empty space on the frmMenu2 Form.

3. Change the Name Property value to ImageList0.

4. Right-click on the Image List Control, highlight ImageListCtrl Object Option, on the displayed menu, and select Properties.

5. Select the preset Image Size 16 x 16 Pixels Option on the Properties General Tab.  You must select one of these options on the General Tab first, before adding any Image to the Images Tab.

6. Select the Images Tab. The Properties Images Tab looks like the following Image:

   

7. Click the Insert Picture command button, then browse to locate the folder_closed sample image (or use any image you created during last week’s trial run). Select the file and click the Open button.

   After this step, the ImageList control will display the newly inserted image, as shown in the screenshot below.

   

8. Once the image is inserted, three text box controls—Index, Key, and Tag—will become active. The Index text box will automatically display the value 1 for the first image.

   In the Key text box, type a unique string value such as folder_close (or any meaningful key name you prefer). This key name will later be used in the Add() method's parameters of the TreeView control. Remember, key values must be unique and are case-sensitive, so keep them simple and easy to recall.

   The Tag property is optional but useful. You can enter information like the file path of the image or any other descriptive note.

   Repeat the process for all other images:

   • Insert each image one by one from your disk.

   • Assign a meaningful key value in the Key text box.

   • Optionally, add a descriptive note in the Tag box.

   If you need to remove an image, simply select it in the control and click Remove Picture.

   When you finish uploading all required images, click Apply, then click OK to close the ImageList control.

   ⚠️ Important: Ensure that the image size (e.g., 16 x 16 pixels) is selected on the General tab before uploading images. Once images are added, the size option becomes locked. To change the image size later, you must remove all images, select the new size, and then re-upload them.

   The ImageList control with multiple images will appear similar to the example shown below:

   

9. If you are unsure of the Key-Value assigned to a particular image, simply click on the image in the control. The Key text box will immediately display its assigned value.

   Once all required images are uploaded, they remain stored within the ImageList control itself. This means you can easily reuse them in other projects—just copy the ImageList control to a new form, or share the form/database with others. You can also add more images later from a new location if needed.

   After the images are ready, the next step is to pass the ImageList control reference to the TreeView control’s ImageList property, just as we did earlier when uploading images with VBA code.

   Here is a sample snippet that demonstrates how to assign the ImageList reference to the TreeView control in a new project:

   Private Sub Form_Load()
       ' Pass ImageList reference to the TreeView control
       Set Me.TreeView0.ImageList = Me.ImageList1.Object
   End Sub
   

   In this example:

   • TreeView0 is the name of the TreeView control.

   • ImageList1 is the name of the ImageList control containing the uploaded images.

   With this link established, you can now use either the Index numbers or the Key values of the images directly in the Nodes.Add() method of the TreeView.

Expanding/Collapsing Nodes with One Command Button.

1. Open the frmMenu2 form in Design View.

2. Select the Collapse All command button, open its Click event procedure, and remove the code.

3. Delete the Collapse All button from the form (we will replace both actions with one button).

4. Select the Expand All command button, and open its Click event procedure.

   

5. Copy and paste the following VBA code, overwriting any existing lines between cmdExpand_Click() and End Sub:

Private Sub cmdExpand_Click()
Dim Nodexp As MSComctlLib.Node

If cmdExpand.Caption = "Expand All" Then
    cmdExpand.Caption = "Collapse All"
    
    For Each Nodexp In tvw.Nodes
        Nodexp.Expanded = True
    Next Nodexp
Else
    cmdExpand.Caption = "Expand All"
    
    For Each Nodexp In tvw.Nodes
        Nodexp.Expanded = False
    Next Nodexp
End If

End Sub

1. Save and Open frmMenu2 in normal view.

2. The Command Button Caption text is now shown as Expand All, and all the Nodes are in the collapsed state.

3. Click on the Command Button to open up all the Nodes.  All the nodes and their Child Nodes will be in expanded form. The Caption of the Command Button changes to 'Collapse All'.

4. Click on the same Command Button again, and all the Nodes are now closed except the Root-level Nodes, and the Caption Text changes back to 'Expand All' again.

Next week, we will explore the CheckBoxes on Nodes to learn how we can identify checked Nodes and work with them.

Download ProjectMenuV22.zip

CLASS MODULE

1. MS-Access Class Module and VBA
2. MS-Access VBA Class Object and Arrays
3. MS-Access Base Class and Derived Objects
4. VBA-Base Class and Derived Object-2
5. Base Class and Derived Object Variants
6. MS-Access Recordset and Class Module
7. Access Class Module and Wrapper Classes
8. Wrapper Class Functionality

Read More

Auto-Numbers in Query Column Version-2

Introduction

In January 2010 I published a Function: QrySeq() - Auto-Numbering in Query Column on this website and was well-received by the readers all through these years.  While going through it again, I thought it can be written with less Code and improve its performance by using a better approach, other than a variant Array.

When the function QrySeq() is called from a query record the program searches through the Array of Unique Keys and looks for the matching key, passed from the record as a parameter, finds it, and returns the sequence number, from the Array element to the calling record.

If the Query has a large volume of records this process may take more time because every time the program looks for the key value from the beginning of the Array.

The New Version is with the Name: QryAutoNum()

Using Collection Object instead of Array.

You can find a detailed discussion of Collection Object, on Ms-Access and Collection Object Basics Page.

Here we will have a brief introduction to know what it is and how it is used in VBA.  The Collection Object is a versatile Object that can hold, in general terms, any Values, Numeric or String Values, Class Module Objects, or a collection of other Objects.  The Collection Object is instantiated in VBA programs in the following manner:

'declare a Collection Object.
Dim ABC as Collection
'create an instance of Collection Object in Memory
Set ABC = New Collection 

'We can Add built-in data types: Numeric, Strings etc 
‘or Objects like Class Module Objects, 
‘or other Collection Object as Items to the Collection Object.
'Use the Add method to add a Collection Item to the Object.

ABC.Add 25
ABC.Add "ms-accesstips"

'When Collection Object Items added this way, 
‘it can be retrieved only in the added order.

For j = 1 to ABC.Count 'gets the count of Items
	
Debug.Print ABC(J)’ retrieve in Item index Order.

Next

'When ADDed an Item with a String Key 
'we can use the Key value to retrieve the Items Randomly.
'But, usage of Key is optional.

ABC.Add 25, "1"
ABC.Add "ms-Accesstips", "2"

x = "2"

Debug.Print ABC(x)

Result: ms-accesstips

So, we will use the Collection Object to add the Query Auto-Numbers with the Unique Key Values as Collection Object Key.  With this approach, we can retrieve the Auto-Numbers directly, rather than struggling with Arrays and complicated storing/retrieving steps.

The QryAutoNum() Function Code.

Option Compare Database
Option Explicit

Dim C As Collection

Public Function QryAutoNum(ByVal KeyValue As Variant, ByVal KeyfldName As String, ByVal QryName As String) As Long
'-------------------------------------------------------------------
'Purpose: Create Sequence Numbers in Query Column Ver.-2
'Author : a.p.r. pillai
'Date : Dec. 2019
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
'Parameter values
'-------------------------------------------------------------------
'1 : Column Value - must be UNIQUE Numeric/String Type Values from Query Column
'2 : Column Name  - the Field Name in Quotes from where Unique Values taken
'3 : Query Name   - Name of the Query this Function is Called from
'-------------------------------------------------------------------
'Limitations - Function must be called with Unique Field Values
'            - as First Parameter
'            - Need to Save the Query, if changes made, before opening
'            - in normal View.
'-------------------------------------------------------------------
Static K As Long, Y As Long, fld As String
On Error GoTo QryAutoNum_Err

Y = DCount("*", QryName) ' get count of records for control purpose

'If KeyfldName Param is different from saved name in variable: fld
'or Value in K more than count of records in Variable: Y
'then it assumes that the QryAutoNum() is called from a different Query
'or a repeat run of the same Query. In either case the Control Variable
'and Collection Object needs re-initializing.
If KeyfldName <> fld Or K > Y Then
'initialize Control Variable
'and Collection Object
    K = 0
    Set C = Nothing
    'save incoming KeyfldName
    fld = KeyfldName
End If

'if KeyValue parameter is Numeric Type then convert
'it to string type, Collection Object needs it's Key as String Type.
If IsNumeric(KeyValue) Then
    KeyValue = CStr(KeyValue)
End If

K = K + 1
If K = 1 Then
Dim j As Long, db As Database, rst As Recordset
Dim varKey As Variant

Set C = New Collection

Set db = CurrentDb
Set rst = db.OpenRecordset(QryName, dbOpenDynaset)

'Add recordlevel AutoNumber with Unique KeyValue
'to Collection Object, in AutoNumber, KeyValue Pair
While Not rst.BOF And Not rst.EOF
    j = j + 1 ' increment Auto Number
    
    'Get key value from record
    varKey = rst.Fields(KeyfldName).Value
    
    'if numeric key convert it to string
    If IsNumeric(varKey) Then
      varKey = CStr(varKey)
    End If
    
    'Add AutoNumber, KeyValue pair to Collection Object
    C.Add j, varKey
    
    rst.MoveNext
Wend
    rst.Close
    Set rst = Nothing
    Set db = Nothing

'Retrieve AutoNumber from Collection Object
'using the KeyValue.  Works like Primary Key of Table
    QryAutoNum = C(KeyValue)
Else
    QryAutoNum = C(KeyValue)
End If

If K = Y Then 'All record level AutoNumbers are Returned
    K = K + 1 ' increment control variable
End If

QryAutoNum_Exit:
Exit Function

QryAutoNum_Err:
MsgBox Err & " : " & Err.Description, , "QryAutoNum"
Resume QryAutoNum_Exit

End Function

Sample Source Query SQL.

With the Northwind Products Table.

SELECT Products.ID, 
Products.Category, 
Mid([Product Name],18) AS PName, 
Sum(Products.[Standard Cost]) AS StandardCost, 
QryAutoNum([ID],"ID",
"Product_AutoNumQ") AS QrySeq
FROM Products
GROUP BY Products.ID, Products.Category, Mid([Product Name],18)
ORDER BY Products.Category, Mid([Product Name],18);

Review of VBA Code Line-By-Line.

On the Global Area of the Module, we have declared a Collection Object with the Object Variable C.

The QryAutoNum() Function declaration is the same as our earlier QrySeq() with three parameters.

1. Unique Key-Value either Numeric or String as the first Parameter.
2. The Key-Value Field’s Name in String Format.
3. The Query Name in String Format.

The returned Auto-Number is in a Long Integer format.

Three Static Variables,  K and Y declared as Long Integers, and fld was declared as String Variable.

All three Variables control the Code execution paths and determine when to initialize Collection objects and control variables.

The DCount() Function takes a count of records in the Query in Variable Y.

If the KeyFldName is different from the saved name fld, then it assumes that the function call is from a new Query Record.  If the field name is the same, but the value in variable K is greater than Y  then the earlier Query is calling the function QryAutoNum() for a repeat of the earlier run.   In either case, the control variable K is reset to zero, and the Collection Object with existing Items is cleared from memory.  The new Key field name received in the KeyFldName variable is saved in the fld variable for later validation check.

Next, if the KeyValue parameter value is numeric then it is converted to String format in the statement: KeyValue = Cstr(KeyValue). The Collection Object Item Key must be in string format.

Next, the variable K is incremented by one.  When the value in K=1 it assumes that this is the first call of this function, from the first record of a Query.  When this is the case the main process of this function starts.

The local temporary Variables are declared here and their values are not preserved between calls of this function from different records of the query.

The Collection Object declared in Standard Module’s  Global area is instantiated in memory, with the statement Set C = New Collection.

The Query record set is opened to read records one by one. The local variable J is for creating Auto-numbers and adding to the Collection Object for each record.  The Unique Key-Value, read from the recordset,  into variable varKey, is added to the Collection Object as its Key Value.

If the varKey variable value is Numeric Type then it is converted to String format.

The Auto-Number Value in Variable J and the string value in variable varKey are added to the Collection Object in the following statement, as its Item value, Key pairs:

C.Add J, varKey

This process is repeated for all the records in the Query.  The Auto-Numbers are generated for all the records and added to the Collection Object, one after the other.  All this work is done during the first call of the function from the first record of the query.

Did you notice that we are reading the Unique Key value of each record directly from the record set within the While . . . Wend Loop to add them to the Collection Object?  After adding the Auto-Numbers for all records, the record set and Database Objects are closed.

Remember, we are still on the first call of the function from the first record of the query and the first parameter variable KeyValue still holds the first record Key Value.

The next statement QryAutoNum = C(KeyValue) retrieves Collection Object’s first Item Auto-Number Value 1, using the Unique Key Value in parameter variable KeyValue, and returns it to the function calling record. This will happen only once because the variable K will be greater than one on subsequent calls of this function.

So, the Function calls from the second record onwards will take the ELSE path of the If K=1 Then statement and retrieve the Auto-Numbers from Collection Object, using the KeyValue passed as Parameter, and returns it to respective records in the Query.

It works very fast because we can directly pick the item value, using the Collection Object Key, rather than searching for the Key, through the Array from the beginning to find the one we want.

When all the record Auto-Number values are returned, the value in the control variable K = Y. We have already taken the count of records of the Query, in Variable Y at the beginning of the program. At this point we increment the value in variable K, by 1 to make it more than the value in Variable Y.  Since, K and Y are Static Variables their values are not lost after the last record call is over and remains in memory.  If the same Query is run a second time the test for these variable values can determine whether we need to reset the variable values and clear the earlier Collection Object from memory for a fresh start of the process all over again.

If the QryAutoNum() function is called from the same Query again the Static Variables and Collection Object is cleared from memory, preparing for a fresh run of the Function for the same Query or for a different Query.

The sample Report Image using the above Query as Source is given below for reference. 

You can use the Query as Source for Report or Form. 

A sample demo database, with all the Objects and VBA Code, is attached for Downloading and for trying out the Code.

Download AutoNumQuery2007.zip

1. Auto-Numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Preparing Rank List.
4. Auto-Number with Date and Sequence Number.
5. Auto-Number with Date and Sequence Number-2.

Read More

Update Class Object Dictionary Item through Form

Introduction

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 the Class or Dictionary objects; it is a general best practice that should be followed whenever adding objects to a Collection, Dictionary, or an 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 can’t use only one instance of the Class Object to add several Items to the Dictionary Object, visit the page Add Class Object as Dictionary Object Items?

Class Object Instances as Dictionary Items

Now, let us get ourselves prepared for a trial run to add, edit, update, and delete Class Object Items in the Dictionary Object, through an MS-Access Form.  We worked with 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 with the name ClsArea with the Field Names and Field Types shown in the Table Design Image shown below. For Numeric Data Fields, select Double Precision Number Type.
   
   

   Right-click to open Large Image in New Tab/Window.

2. Next, we need a Form with the following design to manage the data of the Dictionary Object.

   

   Right-click to open Large Image in New Tab/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 Large Image in New Tab/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 a duplicate key is entered, 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 Command Buttons are disabled because the Form will be in data entry mode by default.  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 or not.  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, Add Command Button is disabled.

If 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 transferred into temporary variables and checked to see whether any text box is empty or not.

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

Adds Class Object to 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 item retrieved from the Dictionary and displayed on the Form can be edited by clicking the Edit command button. When you click it, the button’s caption changes to Update.

Make the required changes to the fields (including the Description, if needed), and then click the Update button again to save the changes back into 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 will remain listed in the Combo Box 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.

There are also 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 given below in the Form's Class Module of your Form. Save the Form with the name 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 back into the Dictionary object. If you open the Combo Box afterward, 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 with 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

Sorting Dictionary Object Keys and Items

Introduction.

Sorting or indexing table records is a commonly used 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) were manually entered 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 achieve sorting, we must first copy the keys and their corresponding item values from the Dictionary into two separate arrays. These arrays can then be passed to a sorting routine to return the data in the desired order.

The Coding Steps.

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

1. Take a Listing of Unsorted Data from the Dictionary Object.

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

3. Read Dictionary Keys and Item Values and load them into separate Arrays.

4. Pass the Arrays to the Sort Routines as ByRef Parameters.

5. Remove the existing Dictionary Object and instantiate it as a new dictionary Object, with the same name.

6. Read the Sorted Keys and Items from the Array and Add them to the new Dictionary Object.

7. Take the listing of sorted data 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 on 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 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 the Dictionary Object contains a large volume of data, then Bubble-Sort may not be as efficient as the QuickSort method.  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 working with a large volume of data, the QuickSort method completes the task in only a fraction of the time taken by 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 their Keys, it is sufficient to sort only the Keys and hold them in an Array. You can then use the sorted Keys 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

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