Silent error adding to VBA.Collection

[tommycrock]

I was working with collections for the first time yesterday and came across a couple of oddities. I was adding 7 objects, each with a unique string key. The first five objects were added fine, the last two were silently ignored. These had keys with square brackets like "[key6]" and "[key7]". Not to be deterred, I changed the key I was using to a long ID and added like

col.add obj, CStr(obj.id)

Again, this worked for the first five, then it didn't add the sixth, but now did add the seventh.
Anyway, that obj had id = 1. I changed the code to

col.add obj, "0" & CStr(obj.id)

And now all seven get added.
But these seem pretty odd to me and I'm worried there's going to be other circumstances I haven't predicted that will cause future objects not to be added to the collection.
BTW. This needs to be ordered so unless there's a way to write an order and a key to a dictionary it won't work here.

 

[spikepl]

If you want somebody to look into this, then provide not "like" narratives but the specific actual data, or test database, so that the behaviour can be recreated.

 

[Galaxiom]

If the error was silent then it suggests you have disabled errors.

 

[tommycrock]

Okay, thanks. Well now I'm completely perplexed as I can't recreate the behaviour. All three versions are working as I would expect. I wasn't expecting a good old fashioned turn it off and turn it on to do the trick!

 

[tommycrock]

If the error was silent then it suggests you have disabled errors.

Thanks for the suggestion but I don't think so (do you mean by providing an On Error... or is this an option in the IDE?) I was getting errors elsewhere as a result of the items not being added. I'd been stepping through the code and hadn't provided error handlers. By error I just mean the item wasn't added when the line of code asking that was executed. Anyway, it's working now

 

[spikepl]

Access seldom dies without a squeek, so silence implies something unintentional.
Fx. a DoCmd.SetWarnings false disables a lot of stuff, until a DoCmd.SetWarnings true is issued.

 

[tommycrock]

Thanks, I'd forgotten about SetWarnings, but again I haven't got anything like that in my code, and just a few lines later in the same class it was bugging out. But I agree, it's really odd and that was what had me so confused! At the time it was reproducible, but now with no change, just closing and opening Access, it's working. I guess I also wanted to know this wasn't known behaviour - or if it was, what else I should expect.
Thanks again

 

[vbaInet]

It probably got confused as to whether the key being added is a number or a string, perhaps due to the CStr() being used on the same line as the Add method. Best thing to do is to always concatenate the number with a string, I use the pipe character at the end "1|".

BTW. This needs to be ordered so unless there's a way to write an order and a key to a dictionary it won't work here.

What do you mean? It's going to be added in the order that it was entered. A Dictionary object offers more options.

 

[tommycrock]

It probably got confused as to whether the key being added is a number or a string, perhaps due to the CStr() being used on the same line as the Add method. Best thing to do is to always concatenate the number with a string, I use the pipe character at the end "1|".

Thanks, useful suggestion if it goes wrong again.

BTW. This needs to be ordered so unless there's a way to write an order and a key to a dictionary it won't work here.

What do you mean? It's going to be added in the order that it was entered. A Dictionary object offers more options.

My understanding - could be wrong - was that a dictionary was like a bag, you couldn't know you'd get it back in the order you put it in (unless you put that order in the key).

 

[vbaInet]

You're thinking about a table or another type of data structure.

Perhaps you're not aware that you can iterate a dictionary in several ways:
1. using For Each - which starts from the first item added to the last and it will return in the order that it was entered
2. using the Key index - by key index I mean the index itself, not the key value. And again values returned will be in the order in which they were entered.

 

[tommycrock]

Thanks. I had been looking at the experts-exchange article Using-the-Dictionary-Class-in-VBA, which said you could only return items by key, not index and that when using .Items "There is no guarantee that the order of items in the array will match the order in which you added those items to the Dictionary"

 

[vbaInet]

Well I have no idea where the author got that from. I use the Dictionary object all the time and it's preferable to the Collection object for it's rich features and high performance (yes it's faster than a collection). My suspect is that s/he's talking about the Collection object because a Collection is similar to an array.

Let me see the article.

 

[tommycrock]

http://www.experts-exchange.com/articles/3391/Using-the-Dictionary-Class-in-VBA.html
This is my 10th post so wasn't able to post it in the last message!

 

[vbaInet]

No problem! To help speed things up so that I don't have to read the entire article, can you copy and paste the sentences where he mentioned the two things that we've been speaking of?

 

[tommycrock]

I expect he's just wrong, but here they are

In a Dictionary, an item can only be returned in reference to its key. In a Collection, and item can be returned in reference to its key, or in reference to its index value (i.e., ordinal position within the Collection, starting with 1).

Items method

The Items method returns a zero-based (even if you use a module declaration of Option Base 1), one-dimensional array of the various items stored in the Dictionary.

' Returns a concatenated list of the Items:   
MyArray = MyDictionary.Items 
MsgBox Join(MyArray, ";")

There is no guarantee that the order of items in the array will match the order in which you added those items to the Dictionary.

 

[gemma-the-husky]

maybe in the original version the keys WERE added, but you were reading them back wrongly. Hence no error

what made you think the items were not added to the collection?

 

[vbaInet]

Ok I see the confusion.

The first one is partially accurate, because strictly speaking you still need the key to retrieve the associated item in a Dictionary. The argument expected in Dictionary Item is a Key and a Key alone, whereas the argument in a Collection Item can be either a Key or an Index. However, it doesn't mean that you can't get around that.
This is how you retrieve a Dictionary Item using the index:

dict.Item(dict.Keys(x))

... where x is the index.

With the second one, the author was referring to the array returned by "Join()", not the dictionary itself.

 

[tommycrock]

maybe in the original version the keys WERE added, but you were reading them back wrongly. Hence no error

what made you think the items were not added to the collection?

I was F8 stepping through the code line by line and watching the collection in the Locals window. It was an iterating section of code (populating a custom object from a recordset and adding it to the collection). For the first five adds you could see the new item in the tree of the collection, but not for the last two (or not for the second last but for the last in my later attempt).

 

[vbaInet]

what made you think the items were not added to the collection?

I vaguely remember seeing this behaviour many years ago.