Weird collection processing results - expert needed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone fathom why this code fails? I'm totally stumped

Global glbcolSA As New Collectio
Global glbcolSADesc As New Collectio
Global glbcolSATel As New Collection
......
Do While Not rs.EO
MsgBox rs("SupportArea") <------ Displays ite
glbcolSA.Add rs("SupportArea"
glbcolSADesc.Add rs("Description"
glbcolSATel.Add rs("CustSuppTel"
MsgBox glbcolSA.Item(glbcolSA.Count) <--- Displays ite
rs.MoveNex
Loo
MsgBox glbcolSA.Count <------- Displays
For x = 1 To glbcolSA.Coun
MsgBox glbcolSA.Item(x) <------ Fails
Next
 
Collections can hold null values..since they are of variant type.

Likely, one of the fields in the recordset was null.

You cannot go

msgbox null

So, either prevent placing of null values in the collection, or go:

MsgBox nz(glbcolSA.Item(x),"")
 
David said:
Can anyone fathom why this code fails? I'm totally stumped.

Global glbcolSA As New Collection
Global glbcolSADesc As New Collection
Global glbcolSATel As New Collection
......
Do While Not rs.EOF
MsgBox rs("SupportArea") <------ Displays item
glbcolSA.Add rs("SupportArea")
glbcolSADesc.Add rs("Description")
glbcolSATel.Add rs("CustSuppTel")
MsgBox glbcolSA.Item(glbcolSA.Count) <--- Displays item
rs.MoveNext
Loop
MsgBox glbcolSA.Count <------- Displays 5
For x = 1 To glbcolSA.Count
MsgBox glbcolSA.Item(x) <------ Fails!
Next
Click to expand...

Interesting. There seems to be some serious confusion going on as to what is
being
added to the collections. There are three ways I can make your code work.

One is to add parenthesis around the .add arguments as in
glbcolSA.Add rs(("SupportArea")), but if you also try to add a key it blows
up.

Two is to assign rs("SupportArea") value to a string variable before adding
it to
the collection.

Three is to force rs("SupportArea") to be seen as a string using
CStr(rs("SupportArea").

Like you said, weird.
 
It appears that the eof condition does something with the collection. Somehow Access thinks the collections are connected to the recordset
Looks like a bug in the compiler to me

Thanks for your tips!
 
See my post in the Modulues (DAO & VBA) newsgroup. This
is why cross posting is generally considered bad form.

Change: glbcolSA.Add rs("SupportArea")
To: glbcolSA.Add rs("SupportArea").Value


Chris
 
Back
Top