Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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