Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-19-2016, 04:18 PM   #1
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,236
Thanks: 92
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
vba ---Iterating over MS Access Dictionary

Note: This has also been posted at
http://www.accessforums.net/showthread.php?t=63480

I have searched but can not find a clear example for iterating over a dictionary object. The dictionary contains different object types.

There are 3 item(s) in the dictionary/object
DictItems(0) is a Collection called <items> with 3 element(s)
DictItems(1) is a Dictionary called <obj> with 2 element(s)
DictItems(2) is a String called <message>

My goal is to iterate over the dictionary and report/process each of the elements in each of the Items.
I'd like the solution to be sufficiently generic in that keys and values can be determined via the vba code.

I am attaching a jpg showing the watches related to the dictionary object I am testing with.

If you have a sample showing a vba procedure for iterating over a dictionary, or a link to same or a tutorial, or have some ideas on approach, I would appreciate any suggestions.

Thanks in advance.
Attached Images
File Type: jpg DictionaryWatches.jpg (83.5 KB, 105 views)

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 12-19-2016, 05:50 PM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,314
Thanks: 537
Thanked 940 Times in 891 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: vba ---Iterating over MS Access Dictionary

I found this:-

Using the Dictionary Class in VBA
https://www.experts-exchange.com/art...ss-in-VBA.html


I note:-
In a Dictionary, an item can only be returned in reference to its key.

Also you can enumerate the items in a Dictionary:

Code:
For Each x In MyDictionary
    MsgBox MyDictionary.Item(x)
Next
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 12-19-2016, 06:18 PM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,236
Thanks: 92
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: vba ---Iterating over MS Access Dictionary

Thanks Tony,
I saw that reference and went through it several times. I can not get it to display any data. My Dictionary is called Dict, and I had a variable x, so used xA here.

Code:
  'from Gizmo
        Dim xA As Variant
300     For Each xA In dict
310         MsgBox dict.Item(xA)
320     Next
This gives an error 450 on line 310

Most samples I found deal with simple strings.

The material I am placing in this dictionary are: (different object types)
- a 3 element collection
- a 2 element dictionary and a string.

The Collection and Dictionary have multiple elements, so the sample code doesn't address that.

I haven't used Dictionary before, so it may be something simple (hopefully).
I am trying to parse some JSON and make sense of the returned data.

Thanks.
Attached Images
File Type: jpg error450.jpg (16.6 KB, 75 views)

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 12-19-2016 at 06:24 PM.
jdraw is offline   Reply With Quote
Old 12-19-2016, 06:44 PM   #4
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
If this is a Scripting.Dictionary then there are a couple of functions that returns arrays, namely Items and Keys, so you could enumerate Items, and check the type of each object, like...
Code:
dim var
for each var in dict.items
   debug.print typename(var)
next
...so knowing that, you could act selectively on the type of object you found, like...
Code:
dim var
for each var in dict.items
   select case typename(var)
      case "Collection"
         dim c as vba.collection
         set c = var
         debug.print "collection: " & c.count & " elements"
      case "Dictionary"
         dim d as scripting.dictionary
         set d = var
         debug.print "dictionary: " & ubound(d.keys) + 1 & " entries"
      case else
         debug.print "object is: " & typename(var)
   end select
next
...but if you've put stuff into the dictionary by key, then you might want to enumerate the keys collection, which are all strings, and then get at the dictionary's contents by key, like....
Code:
dim var
for each var in dict.keys
   debug.print "Key: " & var, "Type: " & typename(dict.item(var))
next
I think.
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 12-19-2016, 07:11 PM   #5
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,236
Thanks: 92
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: vba ---Iterating over MS Access Dictionary

Thanks Markk,

I think your approach is where I should head next. I have tried to get the contents of the dictionary items
but did see reference to setting a variant to the Items and Keys and working with those. I'm finding it (dictionary and iterating) confusing because I can see the material in the watch window, but haven't
grasped some "concept or other" that lets me code to get it.

I have done some coding to look at the returned string and break it into pieces with some verbiage.
Code:
.....
 For r = 0 To dict.Count - 1
230         If (TypeName(dict.Items(r)) = "String") Then
240             Debug.Print "Dict.Items(" & r & ") is a " & TypeName(dict.Items(r)) & " <" & dict.keys(r) & ">"
250             Debug.Print "These are the contents of the dictionary " & vbCrLf & Join(dict.keys, vbCrLf)
260         Else
270             Debug.Print "Dict.Items(" & r & ") is a " & TypeName(dict.Items(r)) & " <" & dict.keys(r) & ">" _
                            & " with " & dict.Items(r).Count & " element(s)"
280         End If
290     Next r.....................
example:
We are dealing with a JSON object
There are 3 item(s) in the dictionary/object
Dict.Items(0) is a Collection <items> with 3 element(s)
Dict.Items(1) is a Dictionary <obj> with 2 element(s)
Dict.Items(2) is a String <message>
These are the contents of the dictionary
items
obj
message

Thanks for the info.

Update:
Markk-
I just added your sample code and it produced:

collection: 3 elements
dictionary: 2 entries
object is: String

Key: items Type: Collection
Key: obj Type: Dictionary
Key: message Type: String

Much cleaner than what I had. I will continue tomorrow.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 12-19-2016 at 07:25 PM.
jdraw is offline   Reply With Quote
Old 05-28-2017, 03:13 PM   #6
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,314
Thanks: 537
Thanked 940 Times in 891 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: vba ---Iterating over MS Access Dictionary

I don't know if this is relevant,

http://www.fmsinc.com/microsoftacces...nary_class.htm

but when I saw it, I thought of this post...
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 05-28-2017, 03:28 PM   #7
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,236
Thanks: 92
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: vba ---Iterating over MS Access Dictionary

Thanks Tony. I have bookmarked the page. I was trying to help someone with an Access/JSON issue, but that was a few months ago.
Parsing JSON with an Access class module would make a good youtube or series (but may have limited audience).

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 09-27-2017, 12:47 PM   #8
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,314
Thanks: 537
Thanked 940 Times in 891 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: vba ---Iterating over MS Access Dictionary

Not sure this is relevant, but it seems like the same tree to me, or at least a similar one. In other words I don't think I'm barking up the wrong tree, but I'm most probably wrong!

If you go down this page and find item "4" (four) I'm not sure how long it will remain item 4 so it's headed thus:-

With a little work, you can iterate over custom collections like this:

Now that's a collection, and I know your question was about dictionaries...

There's also some very interesting comments on that page, well worth a read.
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 09-27-2017, 01:43 PM   #9
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,236
Thanks: 92
Thanked 2,025 Times in 1,972 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: vba ---Iterating over MS Access Dictionary

Thanks Tony. The comments are good. I'm following Colin of the JSON parser stuff. He's into it in more depth than anything I have found via Google/Bing etc.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Iterating through 10! combinations John Big Booty Modules & VBA 4 06-17-2011 05:36 AM
Dictionary in access Allos General 3 12-22-2008 12:52 PM
iterating a table jacks Modules & VBA 6 06-24-2007 12:26 AM
Iterating thru ALL subfolders aziz rasul Modules & VBA 4 03-07-2006 11:35 AM
[SOLVED] Iterating through records srd General 5 07-10-2003 07:17 AM




All times are GMT -8. The time now is 09:12 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World