Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Sorting Dictionary Object Keys and Items

Sorting/Indexing of Table Records is a frequently used important exercise to organize the data in proper order, for faster retrieval of information, using Index Keys.

As far as Dictionary Object is concerned there is already a perfect built-in mechanism  to retrieve information directly, using it’s unique Keys.

But, if you would like to know how to sort the Dictionary Object values then let us go for a trial run. We have some sample data created in the earlier demonstration programs and  will use those sample values as input to our sort program.  The sample VBA Code with sample data is given below.

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 created in Alphabetical Order.  But, here I have re-arranged them into mixed order.  We will pass these data to the Sorting routine and get it back in Alphabetical order.

Unfortunately, there is no way we can re-arrange data directly within the Dictionary Object.  We have to copy the data (Keys and Item Values) from Dictionary into two separate Arrays before passing them to the sort routine to get them back in arranged order.  

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 Dictionary Object.
  2. Define two Array Variables: One for the Keys and the other 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 Array and Add them to the new Dictionary Object.
  7. Take listing of  sorted data from recreated Dictionary Object.

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

With a slight change on 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

If Dictionary Object contains large volume of data then Bubble-Sort may not be as efficient as 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 find any major differences in both program’s execution time here.  But, when there is large volume of data in Dictionary Quick-Sort method takes only a fraction of the time taken by Bubble Sort program.

For the above sorting procedures we have loaded the Keys and Item Values into two separate Arrays, before passing them to the sorting program.  After getting the data sorted they are added into a new Dictionary Object and the old one is deleted.

We can do this in a different way and get the same result.  We need only the Keys to be sorted in required order, either in Ascending or Descending.  With the sorted keys we can retrieve the Item values from the original Dictionary and write them into a new Dictionary Object in the sorted order and delete the old unsorted Dictionary Object..

The modified version of the top program, with 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 loaded into the vKey Variant Array.  Bubble Sort procedure rearranges the Keys in required Order.

With the sorted Keys the corresponding Item Values are retrieved from the old Dictionary Object and writes the Key and Item values into a new Dictionary Object, in the order of the sorted country names.

In the next printing section the sorted Country names and Capitals are printed on the Debug Window, from the new Dictionary Object.


  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form
  11. Dictionary Object Basics
  12. Dictionary Object Basics-2
Share:

Dictionary Object Basics-2

Last week’s introduction to Dictionary Object was a good start and informative for this weeks topics and to learn the fundamentals of this Object’s usage in Programs.  The sample VBA Code presented there demonstrates the usage of few Properties and Methods in the simplest form and I presume that you have already tried them out.

If not, visit last week’s page by following this link: Dictionary Object Basics before continuing.

The list of Property and Methods given below were familiarized through last week’s demonstration programs.  You may click the above link for a review, if needed, or for a fresh look at it before continuing with this page.

The list of Property and Methods we have tried out last week in VBA programs.

1.  Property

  • CompareMode

2.  Methods

  • Add
  • RemoveAll
  • Exists
  • Keys
  • Items

Now, let us try out the following Properties and Method, which remains to try out and find out how they work in programs:

1.  Properties

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

2.  Method

  • Remove – Removes 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 easier to remember them because of their similarities in names.

First, we will take the Item Property to try out.  Item is a multipurpose Property Procedure and works for more than one function. 

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

    Example:

  2. Debug.Print d.Item("Canada")

    OR

    x = d.Item("Canada") Result: x = "Ottawa"
  3. It replaces an Item value, matching the specified Item Key.

    Example:

  4. d.Item("Canada") = "Alberta" ‘ Ottawa replaced with Alberta
  5. If the specified Key not found, then adds  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 with any Key in Dictionary Object.

    Example:

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

We will bring the sample Code, we have used earlier for demo runs, here.

The Sample Code with the usage of Item Property Procedure is given below:

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 the three actions: Retrieval, Replace & Add demonstrates in the above Code.

We have added eight Items into 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 are calling the print_section sub-routine to take a listing of the unchanged items in Debug Window, from Dictionary Object d

In the print_section sub-routine, 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 prints it, along with 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 the wrong capital Alberta with Ottawa. When we call the print_section again the change will appear in the listing of Countries and Capitals.

The next executable statement d.Item(“India”) = “New Delhi” is to watch for it’s effect.  This statement is similar to the replace statement we have used for replacing Canada’s capital Ottawa.  When the search for the Key India fails it will not generate any error, indicating that the search for the Key failed,  but it silently adds the unmatched Key India and Item value New Delhi to the Dictionary Object as a new item.

In the next step we call the print_section sub-routine again to take the changed listing of items in the Debug Window.  The d.RemoveAll statement deletes the Dictionary Object from memory before stopping the program.

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

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

Note: The negative aspect of this particular behavior is that if you make a spelling mistake in the Key value you can easily add a wrong item into the list and you will not know about it either. 

A work-around is that run 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

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

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

Usage Example:  d.Remove "Canada"

The Key Property procedure is good for editing a Key, accepts a key value as Parameter, finds and replaces the old key with the new one.

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

The Count Property Procedure returns a count of 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.

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 Property Procedure require a valid Key parameter with/without brackets. If not found among list of Keys then it will end up with Errors. We perform a validation check, to ensure that the Key exists among the Key Values in the Dictionary Object, before attempting to delete the Item.

The d.Key statement also need a valid Key value otherwise it will end up with errors.  So, we perform a validation check in the following Code segment to ensure that the Key exists in the Dictionary before attempting to replace the Key with another value.  If the specified key is not found then gives a message on that effect, to safeguard against the crash of the program.

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

With the combination of d.Key() and d.Item() statements,  in the above Code snippet, we can completely replace an unwanted item, by over-writing it’s Key and Item Value.

Unlike the Collection Object, the power lies in the Keys of dictionary Object. The Key enables us to retrieve  an Item randomly and quickly from Dictionary Object.  Besides that the Dictionary Object have a rich collection of Methods and Property Procedures to manage it’s contents.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form
  11. Dictionary Object Basics
  12. Dictionary Object Basics-2
Share:

Dictionary Object Basics

Hope you have gone through the last few Articles on Collection Object usage in Microsoft Access, by now.  If not, there will not be any difficulty in understanding Dictionary Object and it’s usage.  But, Collection and Dictionary Objects have lot of similarities, in their usage, and knowing them better will give you more advantage in choosing one of them for a particular task. 

In either case the  links are given below for easy access.  

The Dictionary Object is not part of Microsoft Access VBA.  It is part of VBScripting Language used on Web Pages.  To use Dictionary Object in Microsoft Access we must directly create an object in VBA Programs.  There are two ways we can do this in Microsoft Access VBA:

A.   with the use of Ms-Access Function CreateObject().

Dim d As Object

Set d = CreateObject("Scripting.Dictionary")

This method has a small disadvantage, as far as beginners are concerned, it will not display the Dictionary Object's Methods and Properties by intellisense. Because, we have declared a generic type Object to hold the Dictionary Object.

B.  But, there is a better method. Add the Microsoft Scripting Runtime Library to the existing selected list of Libraries in Microsoft Access.

When we do that we can declare and use the Dictionary Object, like we did for Collection Object. 

  1. Select References. . . from Tools Menu in VBA Window. 
  2. The sample display of Library Files is given below. 
  3. The check marked item (Microsoft Scripting Runtime) is the Library File you have to look for in your System.  The unchecked items are in alphabetical order.
  4. Move the Scroll bar down till you find  the file Microsoft Scripting Runtime, put a check mark and click OK Button to come out.

Now, you can Declare and Instantiate an Dictionary Object with Intellisense support.

Dim d As Dictionary

Set d = New Dictionary

OR

Dim d As New Dictionary

Dictionary Object have the following List of Methods and Properties:

    Method    Description

    Add     Adds an item to the object with the specified Key.  Always added an Item with a Key Value.

    Exists  Verifies that the specified key Exists.

    Items   Returns an array of Item (Element) Values.

    Keys    Returns an array of Keys.

    Remove  Removes the Item specified by the Key.

    RemoveAll  Removes the Dictionary Object from Memory.


    Property         Description

    Count      Gives a count of Items in the dictionary.

    Item         Retrieve/Replace/Add the item with the specified key.  If the specified key doesn’t exists then the Item value is added to the Dictionary with the specified key.

    Key           Replaces the specified Key with a new Key.

    CompareMode  Mode for comparing string keys.

    0  -  Binary (default) : A <> a, A<a

    1  -   Text: A=a, Aa=aa, AA=aa

  1. Copy and Paste the following sample Code into your VBA Standard Module:
    Public Sub Dict_Test0()
    Dim d As Dictionary
    Dim mkey, mitem
    Dim strKey As String
    Dim msg As String
    Dim Title 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", "Ottawa"
    d.Add "Denmark", "Copenhagen"
    d.Add "France", "Paris"
    d.Add "Italy", "Rome"
    d.Add "Saudi Arabia", "Riyadh"
    d.Add "USA", "Washington D.C."
    
    For Each mkey In d.Keys
       msg = msg & mkey & vbCr
    Next
    
    msg = msg & vbCr & "Select a Country, Q=Quit."
    Title = "Dict_Test0()"
    strKey = ""
    
    Do While strKey = "" And strKey <> "Q"
       strKey = InputBox(msg, Title, "")
       If strKey = "Q" Then
          Exit Do
       End If
    
    If d.Exists(strKey) Then
    mitem=d(strKey)
        MsgBox "Country: " & UCase(strKey) & vbCr & vbCr & " Capital:  " & UCase(mitem), , Title
    Else
        MsgBox "Country: " & UCase(strKey) & vbCr & vbCr & "Doesn't exists.", , Title
    End If
    
       strKey = ""
    Loop
    
    'Remove Dictionary from memory
    d.RemoveAll
    
    End Sub
    
  2. Put a Stop statement immediately below  the USA, Washington D.C. Add statement.
  3. Select Locals Window option from View Menu.
  4. Click somewhere within the Code and press F5 to Run the Code.

The Program pauses at the Stop statement.

Check the Locals Window and Click on the plus symbol in [+]d  and view the contents.  It shows only the Key Values and Item values are not visible. 

Press F5 Key again to continue executing the Code.

Enter a Country Name from the displayed list and then press Enter Key or Click OK Command Button to display the selected Country’s Capital.

You can type the country name in Upper-Case/Lower-Case or in mixed form.  The CompareMode setting at the beginning of the Code will take care of comparing the Key value entered by you with the list of  keys in Dictionary Object.

Enter the letter Q to Exit from the Do . . .Loop and stop the Program.

Let us go through the code.  Since, I have already added the Microsoft Scripting Runtime File into my selected list of VBA Library files, I could declare the variable d as a Dictionary Object, as we did with the Collection Object. Declared few other required Variables as well.

The statement Set d = New Dictionary instantiates the Dictionary in memory as Object d.

The d.CompareMode  determines how the given Key Value is compared with the existing list of Keys in memory for retrieving/replacing Item (Element) or Key Value.

The Syntax Comment line indicates as how to Add an item into the Dictionary Object as it’s Element.

In Dictionary Object, the Key is first parameter and Item second. Both Key, Item Parameters are mandatory  and separated by a Comma. 

In Collection Object the order of both these parameters are reversed.  First Parameter is Item and second Parameter Key is Optional.

The d.Add statement, checks whether the given Key already exists in the Dictionary Object first, if it does then gives out an error message:  ‘This key is already associated with an element of this Collection’. The Key values must be unique.

If CompareMode=1  then the variants of the name ‘Nancy’, ‘nancy’, ‘NaNcY’ are all referring to the same Key NANCY or nancy. 

If CompareMode=0 (Binary Compare) then all the above three names are different Keys.

When the Add method finds that the Key value given doesn’t match with the existing Keys the new Key is added with the Item Value to the Dictionary Object.

The Key Value can be of any Data Type except Variant, Array or Object.  Stick with one type of key value for all Items, not a mix of different data types.

We have added eight country names and their capitals. 

The For Each…Next statement reads the list of Keys from Dictionary Object and prepares a menu for the Inputbox() Function. 

The conditional Do While . . . Loop runs until the User enters the letter Q or q (Quit) in the InputBox() function.

The user types a Country name through the InputBox() Function to display the Country’s Capital in a message box. 

The entered country name in the strKey Variable is validated, using the d.Exists() Method to ensure that the entered Key exists in the Dictionary, reads the corresponding Item value and displays it in the Message Box,

When User enters the letter Q in the Inputbox() function the program stops after executing the statement d.RemoveAll that clears the Dictionary Object from memory.

We have read the Key Values alone using the For Each mKeys In d.Keys statement to create a list of Keys for the InputBox Menu.  The d.Keys statement creates an 0 based Array of Key Values.  You can create a separate Array of Key Values with the following statement:

myKeys = d.Keys

Determine the LBound and UBound Array elements Range to work with the list.

The same way we can read all Items (elements) into an Array, away from the Dictionary Object, to work with it if needed.

Let us try to take a listing of all Items, with the method explained above.  We will make a copy of the above Code and make some changes, to retrieve the Items into an Array and print them into the Debug Window.

Here is the Code:

Public Sub Dict_Test0_1()
Dim d As Dictionary
Dim mitem, j As Long

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

mitem = d.Items

Debug.Print "Country Capitals"
Debug.Print "----------------"
For j = LBound(mitem) To UBound(mitem)
   Debug.Print j, mitem(j)
Next

'Remove the Dictionary from memory
d.RemoveAll

End Sub

Copy and Paste the Code into a Standard Module. Display the Debug Window (CTRL+G).

Run the Code to get a listing of Country Capitals in the Debug window as shown below.

Country Capitals
----------------
 0            Canberra
 1            Brussels
 2            Ottawa
 3            Copenhagen
 4            Paris
 5            Rome
 6            Riyadh
 7            Washington D.C.

You may modify the mitem=d.Items statement to mitem=d.Keys to take a listing of all Countries.

Will Continue Next Week.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form
  11. Dictionary Object Basics
Share:

Table Records in Collection Object and Form

Data records from a Table will be Added as the Collection Object Items and Description field values goes into the Collection Object as Key Parameter.

The source values for the Combo Box on the Form are also taken from the Description Field of the Table.   Selection of an item from the Combo Box, will be used as Collection Object Key, to pick the corresponding record from the Collection Object and displays the field values in text boxes on the Form.  The Form and Text Controls are unbound to the Table.

We have created a small table Table1 with few records, for experimenting with the Collection Object

The Table image is given below:

The Table Structure image is given below for reference.

We have designed a small form with a Combo box on the header of the Form.  The Row Source Property of the Combo box is set with the following SQL

SELECT [Table1].[Desc] FROM Table1 ORDER BY [Desc]; 

to pick the Desc field value as Row Source of the Combo Box.

Four text boxes with their Child Labels are added in the Detail Section of the Form.  The Text Box Name Property values are set with the same name of each field on the Table, for easier reference in the Program, in the same order they are appearing on the Table.

The design image of the frmtable1 is given below:

The Normal View of the Form frmTable1, with data displayed from Collection Object is given below for reference.  The Combo box contents also displayed on the Form.


The following Code runs in the frmTable1 Form’s Class Module.  If you have already designed the above Form, ensure that the Text Boxes are set with the field name of the Table structure shown above.  The Combo box name is cmbDesc.  You can download a database with the Code from  link given at the end of this Page. 

Copy and Paste the following Code into the frmTable1’s Class Module:

Option Compare Database Option Explicit Private Coll As Collection Dim txtBox() As String Private Sub Form_Load() Dim db As Database Dim rst As Recordset Dim flds As Long, k As Long

Dim frm As Form, Sec As Section, ctl As Control Dim Rec() As Variant, strKey As String 'Open Table1 to upload records into Collection Object Set db = CurrentDb Set rst = db.OpenRecordset("Table1", dbOpenDynaset) 'get record fields count flds = rst.Fields.Count - 1

'Set Detail Section of Form to scan for Text Boxes Set frm = Me Set Sec = frm.Section(acDetail) 'Redim txtBox() to save Textbox names from Form 'to display field values ReDim txtBox(0 To flds) As String 'Get Text Box Names & save into txtBox() Array from Detail Section of Form 'this will be used in ComboBox AfterUpdate Event Procedure k = 0 For Each ctl In Sec.Controls If TypeName(ctl) = "TextBox" Then txtBox(k) = ctl.Name k = k + 1 End If Next

'instantiate Collection Object Set Coll = New Collection

'Redimension Rec Array for number of fields in Table ReDim Rec(0 To flds) As Variant 'Add each record into the Collection Object Do While Not rst.EOF 'Get current record field values into Rec Variant Array For k = 0 To flds Rec(k) = rst.Fields(k).Value Next

'Description Field Value as Key strKey = rst.Fields("Desc").Value 'Add record to the Collection Object with Key Coll.Add Rec, strKey rst.MoveNext Loop rst.Close Set rst = Nothing Set db = Nothing End Sub

Private Sub cmbDesc_AfterUpdate() Dim strD As String, R As Variant Dim j As Long, L As Long, H As Long 'Get Selected Collection Key from ComboBox strD = Me![cmbDesc] 'Retrieve the record from Collection 'using Collection KEY and save the field 'Values into the Variant Variable R = Coll(strD) L = LBound(R) H = UBound(R) 'Add Field Values into corresponding Text Boxes For j = L To H Me(txtBox(j)) = R(j) Next Me.Refresh End Sub Private Sub Form_Unload(Cancel As Integer) 'Remove Collection from Memory on Form Close Set Coll = Nothing End Sub

This is how it works:

  1. On the Form_Load() Event Procedure each record from Table1 is added as the Collection Object Item.  The record description Field (Desc) value is used as Key parameter value of the Item.
  2. The Desc field values are also used as Combo Box List values on the Form.
  3. When the user selects an Item from the Combo Box, the cmbDesc_AfterUpdate() Event Procedure runs, retrieves the record from Collection Object, using the Key value selected from the combo box, and displays the record field values in the Text Boxes on the Form.
  4. The Objects are cleared from memory when the Form is closed.

At the declaration area of the Module the Collection Object Coll and an empty txtBox() Array are declared.

In the Form_Load Event Procedure after the Database (db) and recordset (rst) object declarations the flds variable is declared to store the count of record fields.

The next line declares Form, Section and Control Objects. They are used for scanning for text boxes  on the Detail Section of the Form, collect their Name Property Values and store them in the txtBox() Array for later use. 

The Rec() Variant Array is to store the record field values, before moving the record into the Collection Object as a single Item.

The strKey String Variable is declared to assign the record Desc field value and use it as Key of current record added to the Collection Object.  The Collection Object Key must be unique values.

Note: Each area of code is commented suitably to understand what they do.  Go through the code line by line to understand them.

The Form_Load() event procedure does the following:

  1. Opens Table1 and reads the first record fields count into the variable flds.
  2. The Form’s Detail Section is set into the sec Section Object Variable.
  3. The Detail Section of the frmTable1 Form is scanned for Text Box controls and collects their Names into the txtBox() Array.
  4. Next the Collection Object is instantiated as the Object Variable Coll.
  5. At the beginning of the Do While . . . Loop the first record field values are added into the Variant Variable Rec Array.
  6. The Description (Desc) field value is saved into the string Variable strKey.
  7. The statement Coll.Add Rec, strKey adds the first record values from the Rec Array as first Item, with the value in strKey as Item KEY of the Collection Object.
  8. The rst.MoveNext advances the Record Pointer to the next Record and repeats this action till all the records in the table are added to the Collection.
  9. The recordset is closed.

At the Form Load Event Procedure all the records in the Table are loaded into the Collection Object.  The Combo Box at the Header Section of the Form is loaded with the Description field values of the Table.

When the User selects an item from the Combo Box the cmbDesc_AfterUpdate() Event Procedure runs. 

The Combo Box value is stored into strD and used in the statement R = Coll(strD) to retrieve the Record Array Values from the Collection, using the Key value in strD variable, into the Variant Array Variable R.  The statement R = Coll.Item(strD) is equally valid.

Here, you might have noticed that we have not declared the Variant Variable R as an Array.  VBA automatically dimensions for correct number of elements and data type, when the record is read from the Collection Object Item. 

Next two steps calculates the Array dimension range (Low and High values) and uses them in the For Next . . .  Loop as control values.  The record field values are copied to the Text Boxes on the Form, using the text box names collected in the txtBox Array.


Download TableColl2003.zip

Download TableColl2007.zip


  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form
  11. Dictionary Object Basics
Share:

Ms-Access Class Module and Collection Object

Last week we had a brief introduction to the Collection Object Basics.  We have learned as how to Add items to a Collection Object and the data item retrieval methods.  We have used mixed data types as Collection Items in our examples and they were intended for demonstration purposes only. 

Normally, Collection Object Items will be one type of values or Objects with their Properties.

We have familiarized with the following Collection Object Methods earlier:

  • Add – to add an Item to the Collection Object
  • Count – returns the Count of Total Items within the Collection Object
  • Item – Takes item sequence number as parameter and returns a specific Item from the Collection.
  • Remove – Takes item number as parameter and deletes the item from Collection.

The Add method have four parameters: Item, Key, Before, After and we have learned their usages too.

Now, let us proceed further with the real world usage of Collection Object with few examples.

The sample VBA Code given below uses the first Ms-Access Class Object ClsArea, we have created earlier, for calculating Area of rectangular Rooms, Objects etc.  We have created Array of Class Module Object ClsArea and worked with them to calculate area of several Rooms.

Now, it is time to learn the usage of Collection Object, instead of Arrays, to create and add several room details into the Collection Object. The items can be retrieved, when needed, and print the details in Debug Window.

  1. Open your database and display the VBA Window (ALT+F11).
  2. Copy and paste the following VBA Code into a Standard Module:
    Public Sub Coll_TestOne()
    Dim Arr As ClsArea
    Dim objColl As Collection, obj As Object
    Dim j As Integer, iMax As Integer
    Dim strName As String
    
    iMax = 5
    
    'instantiate the Collection object in memory
    Set objColl = New Collection
    
    'Create a Collection of five ClsArea Object
    'with it's Property values filled-in
    For j = 1 To iMax
        Set Arr = New ClsArea
        
        strName = "Bed Room" & j
        Arr.strDesc = strName
        Arr.dblLength = 20 + j
        Arr.dblWidth = 15 + j
        
      objColl.Add Arr 'Add current Class Object to Collection
      
        Set Arr = Nothing
    Next
    
    'Printing Section
    Debug.Print "Description", "Length", "Width", "Area"
    For Each obj In objColl
        With obj
            Debug.Print .strDesc, .dblLength, .dblWidth, .Area
        End With
    Next
    
    Set objColl = Nothing
    
    End Sub
    

    Before running the Code, let us take a look at each line and see what it does.

    First line declares Arr as ClsArea Object.  Next line declares ObjColl as a Collection Object and Obj as a generic Object type.  Next line declares two Integer type variables and the line next to that  declares a String variable.

    The iMax variable is initialized with the value 5 for For . . . Next Loop control purposes.  We will create five ClsArea Objects and their property values will be assigned before adding it to the Collection Object, one by one.

    Next executable line instantiates the Collection Object ObjColl in memory.

    Next, a For . . . Next Loop is set up to run for 1 to iMax (or 5) times.  The Arr is instantiated as ClsArea Class Object in memory, in preparation for assigning the first ‘Bed Room1’ Property Values.  The room Description will be created with the constant value Bed Room and with  the control variable j value to the name of the Rooms, as ‘Bed Room1’ to ‘Bed Room5’.

    Like-wise dblLength Property value of the room will be 20+j (or 21 to 25) and dblWidth Property value will be 15+j (16 to 20).

    After assigning of Arr property values the Class Object is added to the Collection ObjColl with the Add  Method.

    The Set Arr = Nothing statement removes the current Class object from memory.

    The For . . . Next Loop repeats to Add four more items into the Collection.

    Next, the For. . . Next Loop, in the Printing Section, uses the Obj Object variable as control to read each item from the Collection and prints the ClsArea Object Property Values in the Debug Window.

  3. Click somewhere in the middle of the Code.
  4. Press CTRL+G to display the Debug Window.
  5. Click somewhere in the code and press F5 Key to run the Code.

The output on the debug window will be as shown below:

Description   Length        Width         Area
Bed Room1      21            16            336 
Bed Room2      22            17            374 
Bed Room3      23            18            414 
Bed Room4      24            19            456 
Bed Room5      25            20            500 

We have not entered the second parameter Key Value in the above example.  The printing section uses an object variable to retrieve each ClsArea Class Object item, in the same order, as they were added to the Collection, and prints it’s property values into the Debug Window.

The following Code snippet can be replaced, in the above Code, to retrieve each item from the Collection Object.  This will use the item sequence number, as parameter to the Item Method to retrieve each object, in the same order as they were added to the Collection.

'Printing Section
Debug.Print "Description", "Length", "Width", "Area"
For j = 1 To objColl.Count
    With objColl.item(j)
        Debug.Print .strDesc, .dblLength, .dblWidth, .Area
    End With
Next

The number of items in the collection is determined by the expression ObjColl.Count in the For . . ..Next Loop.

You may insert a Stop statement above the Printing Section and run the above code again.  When the program pauses execution at the Stop statement, select Locals Window from View Menu. 

Click on the plus symbol [+]ObjColl to display ClsArea Object Items within the Collection Object.

Click on the plus symbol [+]Item 1 to exand the item and display the ClsArea Object Properties and their Values.

Note the dblLength property value is showing as 21.  We will try to change this value by  typing an expression in the Debug Window.  Type the following expression directly in Debug Window and press Enter Key:

ObjColl.Item(1).dblLength = 50

Check the Locals Window now, the value 21 now changed to 50.  You can overwrite the Item Property Values but cannot replace the object item directly. 

If you would like to overwrite an object item, then you must Remove the wrong item first from Collection, with item sequence number as parameter (say Remove 1 ), and Add a new object with whatever changes required to the new item.  You may use the After or Before parameter with proper item number to position the item to the exact area in the Collection Object, from where you have removed the old item.  Check last week’s Article: Ms-Access and Collection Object Basics for clarity on Before or After parameter usage of Add Method.

Let us try another example, with few changes to the above code, to Add items with KEY value into the Collection Object.  In the printing section we will retrieve ClsArea Class Object items from Collection with the use of Key values.

Copy and Paste the following Code into a Standard Module:

Public Sub Coll_TestTwo()

Dim Arr As ClsArea
Dim objColl As Collection
Dim j As Integer, obj As Object
Dim iMax As Integer
Dim Desc As String, strKey As String

iMax = 5

Set objColl = New Collection

For j = 1 To iMax
    Set Arr = New ClsArea
    
    Desc = "Bed Room" & j
    
    Arr.strDesc = Desc
    Arr.dblLength = 20 + j
    Arr.dblWidth = 15 + j
    
    objColl.Add Arr, Desc 'Add Item to Collection with KEY parameter
  
    Set Arr = Nothing
Next

'Print items in reverse order
Debug.Print
For j = objColl.Count To 1 Step -1
    strKey = "Bed Room" & j
    
    Set obj = objColl.item(strKey) 'access items with KEY value
  
    With obj
         Debug.Print .strDesc, .dblLength, .dblWidth, .Area
    End With
Next

Set objColl = Nothing

End Sub

We have declared two more String Variables (Desc) to store the Item Description for ClsArea Object instance property strDesc. The same description will be used as Key value, in the Add method too. 

The strKey variable will be used to create the Key Value in the printing section for retrieving each item object from the Collection.  We can re-use the Desc variable in the printing Section also, for creating the Key Value, but a separate variable strKey is used for clarity.

The  objColl.Add Arr, Desc statemment adds the ClsArea Object instance Arr into the Collection Object Item, with the Description (Bed Room1) in Desc Variable as Key Value to the Collection Object, as first Item.

Other Syntax variants of the above statement are given below for reference.

objColl.Add item:=Arr, key:=Desc 'statement with Parameter Names
  'OR
objColl.Add key:=Desc, item:=Arr 'values in improper order with Parameter Names
  '

In the Printing Section we have set the For . . . Next Loop’s control sequence in reverse order to retrieve the items using the Key Values for a change, from ‘Bed Room5’ to ‘Bed Room1’ order.  The Set ObjColl = Nothing statement clears the Collection Object from memory.

There are few problems with the Key Value of Collection Object:

  1. It accepts only String data type as Key Value.
  2. Once the Key Value is assigned to a Collection item you cannot retrieve the Key Value itself, or create a list of the Keys from the Collection Object.
  3. If you already know the Key Value (like Employee Code) you can retrieve the actual Object Item belongs to that Key from the Collection.
  4. If you attempt to Add an Item with an existing Key Value in the Collection it will trigger an Error.

A better option is the Dictionary Object.  It has much more flexible options to store and retrieve values or Object Items.

We will explore and experiment with the Dictionary Object and it’s methods next week.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Ms-Access and Collection Object Basics
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form
  11. Dictionary Object Basics
Share:

Ms-Access and Collection Object Basics

VBA Array is more popular and commonly used, for storing several set of related values (rows and columns), than Collection Object.  We have used Arrays to store User-Defined Types and Class Module Object.  I think it is time to learn something new and different, the usage of Collection and Dictionary Objects. The Collection Object is very convenient to store related items as a group.  Why Dictionary Object, we will take it up at an appropriate time?

To use Arrays, we need to Dimension a Variable, User-Defined Type or Class Module Object for required number of elements in advance, or Re-dimension to increase or decrease the size of an array, before we are able to store value(s) into them. But this procedure is not required for Collection Object.  After instantiating the Collection Object in memory we can add any number of items into it. The Collection members can be of any data type, built-in Objects, Class Module Object or another Collection Object, with it’s own item members.

Let us write a simple program to demonstrate the usage of Collection Object.

Public Sub CollTest1() Dim C As Collection Dim j As Integer

‘instantiate the Object Set C = New Collection C.Add 5 C.Add 15 C.Add "iPhone" C.Add "Disk 2TB" C.Add 35.75

'Print the items in debug window GoSub Listing

C.Remove 3 'Remove 3rd item

GoSub Listing

Set C = Nothing

Exit Sub

Listing: Debug.Print For j = 1 To C.Count     Debug.Print C.Item(j) Next Return

End Sub

The first two lines of code declares the Variable C as a Collection Object. Next line declares the Variable j as Integer type, as control variable for For. . . Next Loop.  The third line instantiates the Collection Object C in memory.

The Collection Object have four built-in methods: Add, Count, Item, Remove, for managing the Collection items in memory.

An image of the Collection Object instance displaying it's methods list below:

  • The Add method adds value or object as Collection member to the Collection Object and the Remove method is for deleting an item from the Collection.
  • The Item method combined with an item number as Index we can retrieve a particular item from Collection. The Count method gives the total items count in the Collection.
  • In the above example we are not using any Object type items but few simple mixed data type values: integer, String and Double Precision Number, as Collection members.
  • By using the Add method of C Collection Object we have added five items into the collection.  First two items are integer numbers, next two items are String data type and the last item is a double precision number. 
  • The Collection Object instance name C and the Add method is joined with a dot separator, followed by a space then the actual value to be added to the Collection.
  • The Add method have four parameters, check the image given below:

  • The parameters: Item, [Key], [Before], [After].  The first parameter Item is mandatory; the Value to be added to the Collection.
  • The next three Parameters are optional. 
  • When any of the optional parameter is used with the .Add method other parameter places must be skipped with comma separator, except for the right side items.  By  explicit usage of parameter names with their values enables us to give the parameter values in any order.  We will learn their usage in another example VBA Code.

    Note: Do not get confused with the Item Parameter of Add Method with the Item() Method of Collecction Object.

We have added five items as members of the Collection Object with the Add method.  Two Integer Type values, two String data Type Values and one double precision number. 

It simply demonstrates that you can add any data type, except User-Defined Type (UDTs) into the Collection.  When you want to add UDTs into a Collection, convert your UDTs into a Class Module Object.

Next, we are calling a printing sub-routine, within the program, that prints the Collection members to the debug window.  The sub-routine have a For . . . Next Loop to run for 1 to the number of items ( C.Count) in the Collection.  The value in the j control variable is used as index number parameter to the Item() method of the Collection Object, to retrieve the value and print it in the debug window.

Next Line removes the third item (iPhone) from the item members, by calling the Remove method.

The printing sub-routine is called one more time to display the changed list of items, after removal of the third item in the old list.

The Exit Sub statement prevents the control of the program from dropping into the internal subroutine lines and stops the program.  The listing will appear in the Debug Window as shown below.

5 15 iPhone Disk 2TB 35.75

5 15 Disk 2TB 35.75

We can insert a value Before a particular item member by specifying the item number with the Before  Parameter Name.

C.Add 2, Before:=1 ‘add value 2 Before existing first item

OR

C.Add 2,,1

The above statement will add value 2 as the first item in the above program, pushing all existing items down.

C.Add 20, After:=3 ‘Add value 20 After existing item number 3

OR

C.Add 20,,,3

This statement inserts the value 20 After the third item, after value 15,  in the list.

The Code below demonstrates the Before:= and After:= Parameter usages.

Public Sub CollTest2() Dim C As Collection Dim j As Integer Set C = New Collection C.Add 5 C.Add 15 C.Add "iPhone" C.Add "Disk 2TB" C.Add 35.75 GoSub Listing C.Add 2, Before:=1 'Insert the item before the first item C.Add 20, After:=3 'Insert the item after first 3 items GoSub Listing

Set C = Nothing Exit Sub Listing: 'Print the items Debug.Print For j = 1 To C.Count Debug.Print C(j)

Next Return End Sub

Note: The advantage of using parameter names is that you can pass the values in any order you want, when you need to use more than one parameter in a statement.

C.Add After:=3,Item:=20

The second example without the use of Parameter Names and giving the parameter value in it’s proper position.

Public Sub CollTest2_2() Dim C As Collection Dim k As Integer

Set C = New Collection

C.Add 5 C.Add 15 C.Add "iPhone" C.Add "Disk 2TB" C.Add 35.75

GoSub Listing

C.Add 2, , 1 'Insert the item before the first item C.Add 20, , , 3 'Insert the item after first 3 items

GoSub Listing

Set C = Nothing

Exit Sub

Listing: 'Print the items Debug.Print      For k = 1 To C.Count          Debug.Print C(k)      Next: Debug.Print Return

End Sub

Sample printout on the Debug Window is shown below:

5 
 15 
iPhone
Disk 2TB
 35.75 

 2 
 5 
 15 
 20 
iPhone
Disk 2TB
 35.75 

In all our printing examples we have used the Item's Index number to retrieve the values to print them in the Debug Window.  When there are several items in the collection it is very difficult to keep track of particular Item's Index number to retrieve the specific value we want.  To overcome this problem we can store an easily memorable Key Value, along with the Item Value, like the First Name of  a Contact in Address Book, to retrieve item value randomly from the Address Book Collection members.

Let us write a new program to demonstrate the usage of Item Keys with Values in Collection.

Public Sub CollTest3() Dim C As Collection Dim strKey As String Dim strGet As String Set C = New Collection C.Add 5, Key:="FIVE" C.Add 15, Key:="FIFTEEN" C.Add "iPhone", "7+" 'you can omit the KEY param name C.Add "Disk 2TB", "DISK" ' Add method's 2nd Parameter is KEY C.Add 35.75, "999" 'add value 2 with Key "TWO" before the first item. 'this item will be the first item in the collection 'parameter names not in proper order – valid

C.Add Item:=2, Before:=1, Key:="TWO" 'add value 7 with Key "SEVEN" as third item in the collection 'parameter names not in proper order – valid

C.Add Key:="SEVEN", Item:=7, After:=2 'Retrieve value using it's KEY from collection strKey = "" Do While strKey = "" strKey = InputBox("Value Key: " & vbCr & vbCr & "Q - Quit", "Enter Key", "") Select Case strKey Case "Q" Exit Do Case "TWO", "FIVE", "SEVEN", "FIFTEEN", "7+", "DISK", "999" strGet = C(strKey) Case Else strGet = " Not Found!" End Select MsgBox "Key:<<" & strKey & ">> Value: " & strGet strKey = "" Loop Set C = Nothing End Sub

The KEY value must be of String Type.  The KEY value must be a unique identifier.

Refer the second image, on top of this Page, as reference for proper order of Parameters of Add Method of Collection Object, displayed by VBA Intellisense.

The above sample programs with mixed type of data items were  presented for demonstration purposes only.  The Collection Object will normally contains only one type of data and mostly used for adding Objects, like Forms, Reports, Class Module Objects, Database Objects and so on, with their properties and methods.


  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access Class Module and Collection Object
  10. Table Records in Collection Object and Form
  11. Dictionary Object Basics
Share:

Wrapper Class Functionality Transformation

Last week we have created a new Wrapper Class ClsTiles, using the  ClsArea Class twice in the new Class Module, one instance for Floor dimension values and the second instance for Floor-Tile dimension, to calculate number of Tiles for the room.

In the new Wrapper Class  Module we will transform  the Volume Class (ClsVolume2) to Sales (ClsSales) Class.  With some cosmetic changes we will give it a total face-lift in the Wrapper Class,  hiding it’s true identity as a Volume calculation Class and use it for calculating Selling Price of Products with Discount.

 That’s right, our ClsVolume2 Class have all the necessary properties to enter the required Sales data values like Description, Quantity, Unit Price and Discount Percentage, which will go into the Volume Class Properties strDesc, dblLength, dblWidth, dblHeight respectively. 

We should not forget that the ClsVolume2 Class is a Derived Class, built using ClsArea as Base Class.

But, first the VBA Code of ClsVolume2 Class Module  (the Base Class for our new ClsSales Class Module) is reproduced below for reference:

Option Compare Database
Option Explicit

Private p_Height As Double
Private p_Area As ClsArea

Public Property Get dblHeight() As Double
    dblHeight = p_Height
End Property

Public Property Let dblHeight(ByVal dblNewValue As Double)
    p_Height = dblNewValue
End Property

Public Function Volume() As Double
    Volume = CArea.dblLength * CArea.dblWidth * Me.dblHeight
End Function

Public Property Get CArea() As ClsArea
   Set CArea = p_Area
End Property

Public Property Set CArea(ByRef AreaValue As ClsArea)
  Set p_Area = AreaValue
End Property

Private Sub Class_Initialize()
Set p_Area = New ClsArea
End Sub

Private Sub Class_Terminate()
Set p_Area = Nothing
End Sub

The only problem that prevents us from using ClsVolume2 Class directly for the Sales data entry  is that the Property Procedure names dblLength, dblWidth, dblHeight doesn’t match for the Sales property values Quantity, UnitPrice, Discount Percentage.   The numeric data types of ClsVolume2 Class are all double precision numbers and they are suitable for our Sales Class and can be used without data type change.  The  public functions Area() and Volume()  names are also not suitable but their calculations formula can be used for Sales calculations without change. 

a) Area = dblLength * dblWidth is suitable for TotalPrice = Quantity * UnitPrice

b) Volume = Area * dblHeight is good for DiscountAmount = TotalPrice * DiscountPercentage

Here, we have two choices to make use of ClsVolume2 Class as ClsSales Class.

  1. The easiest way is to make a copy of the ClsVolume2 Class and save it in a new class Module with the name ClsSales.  Make appropriate changes to the Property Procedure and public Function names suitable for sales values and calculations.  Add more functions, if required, in the new class module.
  2. Create a Wrapper Class using ClsVolume2 as Base Class and create suitable property procedure and public function name changes, masking the Base Class’s Property Procedures and Function names.  Create new Functions in the Wrapper Class, if necessary.

The first option is somewhat straight forward and easy to implement.  But, we will select the second option to learn as how to address the Base Class’s Properties in the new wrapper Class and how to mask it’s original property names with new ones. 

  1. Open your Database and display the VBA Editing Window (Alt+F11).
  2. Select Class Module from Insert Menu, to insert a new Class Module.
  3. Change the Class Module’s Name property Value from Class1 to ClsSales.
  4. Copy and Paste the following VBA Code into the Module and Save the Code:
    Option Compare Database
    Option Explicit
    
    Private m_Sales As ClsVolume2
    
    Private Sub Class_Initialize()
        'instantiate the Base Class in Memory
        Set m_Sales = New ClsVolume2
    End Sub
    
    Private Sub Class_Terminate()
        'Clear the Base Class from Memory
        Set m_Sales = Nothing
    End Sub
    
    Public Property Get Description() As String
      Description = m_Sales.CArea.strDesc 'Get from Base Class
    End Property
    
    Public Property Let Description(ByVal strValue As String)
      m_Sales.CArea.strDesc = strValue ' Assign to Base Class
    End Property
    
    Public Property Get Quantity() As Double
    Quantity = m_Sales.CArea.dblLength
    End Property
    
    Public Property Let Quantity(ByVal dblValue As Double)
       If dblValue > 0 Then
         m_Sales.CArea.dblLength = dblValue ' Assign to clsArea, Base Class of ClsVolume2
       Else
         MsgBox "Quantity: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
         Do While m_Sales.CArea.dblLength <= 0
              m_Sales.CArea.dblLength = InputBox("Quantity:, Valid Value >0")
         Loop
       End If
    End Property
    
    Public Property Get UnitPrice() As Double
       UnitPrice = m_Sales.CArea.dblWidth
    End Property
    
    Public Property Let UnitPrice(ByVal dblValue As Double)
       If dblValue > 0 Then
          m_Sales.CArea.dblWidth = dblValue ' Assign to clsArea, Base Class of ClsVolume2
       Else
         MsgBox "UnitPrice: " & dblValue & " Invalid.", vbExclamation, "ClsSales"
           Do While m_Sales.CArea.dblWidth <= 0
              m_Sales.CArea.dblWidth = InputBox("UnitPrice:, Valid Value >0")
           Loop
       End If
    End Property
    
    Public Property Get DiscountPercent() As Double
       DiscountPercent = m_Sales.dblHeight
    End Property
    
    Public Property Let DiscountPercent(ByVal dblValue As Double)
    ' Assign to Class .dblHeight of ClsVolume2
    Select Case dblValue
        Case Is <= 0
           MsgBox "Discount % -ve Value" & dblValue & " Invalid!", vbExclamation, "ClsSales"
           Do While m_Sales.dblHeight <= 0
              m_Sales.dblHeight = InputBox("Discount %, Valid Value >0")
           Loop
        Case Is >= 1
           m_Sales.dblHeight = dblValue / 100
        Case 0.01 To 0.75
           m_Sales.dblHeight = dblValue
    End Select
    End Property
    
    Public Function TotalPrice() As Double
    Dim Q As Double, U As Double
    Q = m_Sales.CArea.dblLength
    U = m_Sales.CArea.dblWidth
    If (Q * U) = 0 Then
       MsgBox "Quantity / UnitPrice Value(s) 0", vbExclamation, "ClsVolume"
    Else
       TotalPrice = m_Sales.CArea.Area 'Get from Base Class ClsArea
    End If
    End Function
    
    Public Function DiscountAmount() As Double
       DiscountAmount = TotalPrice * DiscountPercent
    End Function
    
    Public Function PriceAfterDiscount()
       PriceAfterDiscount = TotalPrice - DiscountAmount
    End Function
     

What we did in the Wrapper Class? Created an instance of the ClsVolume2 Class and changed it's Property Names, Function Names and added Validation checks with appropriate error messages and prevented from dropping into the validation check of the Base class with inappropriate error messages, like 'Value in dblLength property is invalid' may popup from the Volume Class.

Check the lines I have highlighted in the above Code and I hope you will be able to figure out as how the property values are assigned/retrieved to/from the Base Class ClsVolume2.

You may go through the ClsArea Class Module first and next to the ClsVolume2 Class Module – the derived Class using ClsArea Class as Base Class.  After going through both these Codes you may take a second look at the Code in this Wrapper Class.

Let us write a Test Program to try out the Wrapper Class.

  1. Copy and Paste the following VBA Code into a Standard Module.
    Public Sub SalesTest()
    Dim S As ClsSales
    
    Set S = New ClsSales
    
    S.Description = "Micro Drive"
    S.Quantity = 12
    S.UnitPrice = 25
    S.DiscountPercent = 0.07
    
    Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
    With S
        Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
    End With
    End Sub
    
  2. Keep the Debug Window open (Ctrl+G).
  3. Click somewhere in the middle of the Code and press F5 key to Run the Code and to print the output in the Debug Window.
  4. You may test the Code further by entering any of the input value with a Negative number and running the code to trigger the new Error Message.  Disable any of the input line, with a comment symbol ('), run the code and see what happens.

The following test code creates an array of three Products and Sales Values by entering directly from Keyboard. 

Copy and Paste the following Code into a Standard Module and Run to test the Wrapper Class further.

Public Sub SalesTest2()

Dim S() As ClsSales
Dim tmp As ClsSales
Dim j As Long

For j = 1 To 3
Set tmp = New ClsSales
   tmp.Description = InputBox(j & ") Description")
   tmp.Quantity = InputBox(j & ") Quantity")
   tmp.UnitPrice = InputBox(j & ") UnitPrice")
   tmp.DiscountPercent = InputBox(j & ") Discount Percentage")
 ReDim Preserve S(1 To j) As ClsSales
 Set S(j) = tmp
 Set tmp = Nothing
Next

'Output Section
Debug.Print "Desccription", "Quantity", "UnitPrice", "Total Price", "Disc. Amt", "To Pay"
For j = 1 To 3
   With S(j)
       Debug.Print .Description, .Quantity, .UnitPrice, .TotalPrice, .DiscountAmount, .PriceAfterDiscount
   End With
Next

For j = 1 To 3
  Set S(j) = Nothing
Next

End Sub

After successful entry of correct values into the Array the product names and sales values are printed in the Debug window.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
Share:

Access Class Module and Wrapper Classes

There are times that a particular Class Module needs to be instantiated more than once, to use similar set of values for a particular Application. 

For example: our ClsArea Class Module, we have designed for calculating carpet area of Rooms is a candidate for similar applications.  Assume that we would like to find out how many floor tiles we need to lay in a room then we should be able to input the Length and Width values of Tile also, besides the dimension values of Floor.  Since, both Floor and Tile have similar Property values as input we can use two instances of ClsArea Class Module, one instance for FLOOR area and the second instance for TILE area.  Floor-Area / Tile-Area gives the total number of tiles for a particular room. 

We can do this by creating two different instances of ClsArea Class Module in the Standard Module Program, if there is only one Room.

Public Sub FloorTiles()
Dim FLOOR As ClsArea
Dim TILES As ClsArea
Dim flrArea As Double, tilearea As Double
Dim lngTiles As Long

Set FLOOR = New ClsArea
Set TILES = New ClsArea

FLOOR.strDesc = "Bed Room1"
FLOOR.dblLength = 25
FLOOR.dblWidth = 15
flrArea = FLOOR.Area()

TILES.strDesc = "Off-White"
TILES.dblLength = 2.5
TILES.dblWidth = 1.25
tilearea = TILES.Area()

lngTiles = flrArea / tilearea

Debug.Print FLOOR.strDesc & " Required Tiles: " & lngTiles & " Numbers - Color: " & TILES.strDesc

Set FLOOR = Nothing
Set TILES = Nothing

End Sub

But, what if we need to apply the above method for an array of several rooms with different sizes or color of tiles?  The answer to that is to create a new Class Module with two instances of the same ClsArea Class, one instance for Floor and the other for Tiles Properties.  Both instances are wrapped in a new Class Module.

Let us do that.

  1. Open your Database and display Code Editing Window (ALT+F11).
  2. Select Class Module from Insert Menu.
  3. Change the Name Property value to ClsTiles.
  4. Copy and Paste the following VBA Code into ClsTiles Class Module and save the Code:

    Option Compare Database Option Explicit Private pFLOOR As ClsArea Private pTILES As ClsArea Private Sub Class_Initialize() Set pFLOOR = New ClsArea Set pTILES = New ClsArea End Sub Private Sub Class_Terminate() Set pFLOOR = Nothing Set pTILES = Nothing End Sub

    Public Property Get Floor() As ClsArea Set Floor = pFLOOR End Property Public Property Set Floor(ByVal NewValue As ClsArea) Set pFLOOR = NewValue End Property Public Property Get Tiles() As ClsArea Set Tiles = pTILES End Property Public Property Set Tiles(ByVal NewValue As ClsArea) Set pTILES = NewValue End Property Public Function NoOfTiles() As Long NoOfTiles = pFLOOR.Area() / pTILES.Area() End Function

    Both instances, pFLOOR and pTILES, are declared as Private Properties of ClsTiles Class Object.

    The Class_Initialize() Subroutine instantiates both objects in memory when the ClsTiles Class Module is instantiated in the user program.

    The Class_Terminate() subroutine removes both instances (pFLOOR and pTILES) from memory, when the ClsTiles Class Module instance is set to Nothing in the user program.

    The Get and Set Property Procedures allows retrieval and assignment of values, from and to the pFLOOR instance respectively, in the ClsTiles Class Object.

    The next Get and Set Property Procedure allows the same operations in the pTILES instance of the ClsArea Class.

    We have added a new function NoOfTiles() in the new Class Module, to calculate the number of tiles, based on Floor-Area and Tile dimension.

    Let us write a Program and learn the usage of multiple instances of the same Class Object,  in a new Class Module: ClsTiles.

  5. Copy and paste the following VBA Code into a Standard Module:
    Public Sub TilesCalc()
    Dim FTiles As ClsTiles
    Dim TotalTiles As Long
    
    Set FTiles = New ClsTiles
    
    FTiles.Floor.strDesc = "Warehouse"
    FTiles.Floor.dblLength = 100
    FTiles.Floor.dblWidth = 50
    
    FTiles.Tiles.dblLength = 2.5
    FTiles.Tiles.dblWidth = 1.75
    
    TotalTiles = FTiles.NoOfTiles()
    
    Debug.Print "Site Name", "Floor Area", "Tile Area", "No. of Tiles"
    Debug.Print FTiles.Floor.strDesc, FTiles.Floor.Area, FTiles.Tiles.Area, TotalTiles
    
    
    End Sub
     
  6. Keep the Debug Window Open (CTRL+G) to print the test data.
  7. Click somewhere in the middle of the VBA Code and Press F5 Key to run the Code.  The result is printed on the Debug Window.

    If you want to calculate the tile requirements of several rooms, or may be rooms of several floors of a high-rise building then you should run the above program that number of times and note down the values, which is practically very difficult.

    Now, let us write another program to find the Tile requirements of several Rooms, with an  Array of ClsTiles Objects by inputting the Property values directly from keyboard.

  8. Copy and Paste the following VBA Code into a Standard Module..
    Public Sub TilesCalc2()
    Dim tmpFT As ClsTiles
    Dim FTiles() As ClsTiles
    Dim j As Long, L As Long, H As Long
    
    For j = 1 To 3
       Set tmpFT = New ClsTiles
          'Floor dimension
        With tmpFT.Floor
          .strDesc = InputBox(Str(j) & ") Floor Desc", , 0)
          .dblLength = InputBox(Str(j) & ") Floor Length", , 0)
          .dblWidth = InputBox(Str(j) & ") Floor Width", , 0)
        End With
        
        'Tile Dimension
        With tmpFT.Tiles
          .strDesc = InputBox(Str(j) & ") Tiles Desc", , 0)
          .dblLength = InputBox(Str(j) & ") Tile Length", , 0)
          .dblWidth = InputBox(Str(j) & ") Tile Width", , 0)
        End With
       
       ReDim Preserve FTiles(1 To j) As ClsTiles
       Set FTiles(j) = tmpFT
       
       Set tmpFT = Nothing
    Next
    
    'Take Printout
    L = LBound(FTiles)
    H = UBound(FTiles)
    
    Debug.Print "FLOOR", "Floor Area", "TILES", "Tile Area", "Total Tiles"
    For j = L To H
      With FTiles(j)
       Debug.Print .Floor.strDesc, .Floor.Area(), .Tiles.strDesc, .Tiles.Area(), .NoOfTiles
      End With
    Next
       
    'Remove all objects from memory
    For j = L To H
       Set FTiles(j) = Nothing
    Next
      
    End Sub
    
    
  9. Keep the Debug Window open to print the output there.
  10. Run the Code as before and input values for Floor and Tile dimensions for three Rooms.

As you can see from the above code that both values of Room and Tile dimensions goes into the same Class Object Array instance, side by side. 

The above program is a demon that runs only for three set of values within the For. . . Next loop.  It can be modified with a conditional loop that runs for required number of times till a conditional break code terminates the program. 

The program can be modified to save each set of data values and calculation results into a Table for future reference.

A Wrapper Class is a Container Class for instances of other Classes, Data Structures or instances collection of other objects.  Here we have used it to hold two instances of the same Class Object.


  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
Share:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Sorting Dictionary Object Keys and Items

Sorting/Indexing of Table Records is a frequently used important exercise to organize the data in proper order, for faster retrieval of inf...

Labels

Blog Archive

Recent Posts