Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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
  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
  17. Update Class Object Dictionary Item on Form
Share:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations Class Module msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Event Menus and Toolbars Collection Object Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Form Command Button Data Dictionary Object Emails and Alerts Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Custom Functions Item Object Reference msaccessprocess security advanced Access Security Add Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler 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 hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Call Function From MouseMove Event Property

Introduction. This is about running a User-Defined Function (say myFunction(Parameter) ) on the Mouse Move Event Property of Text Boxes.  W...

Labels

Blog Archive

Recent Posts