Collection Problem

Hi All

I have a small query related to the Collections:

In my program, I use the explosives name & a specific color to represent that color. Now what I want is that a collection is to be made in which the color of each explosive is stored so that it could be used .

The following is the code for that:



Dim colorcol As New Collection

qexp = "Select * from [br_explosive] where name = '" & cmbexprow.Text & "'"
Set rexp = contoRCB.Execute(qexp)

While Not rexp.EOF
gridexprow.TextMatrix(gridexprow.row, 1) = rexp.Fields(1)
gridexprow.TextMatrix(gridexprow.row, 3) = rexp.Fields(6)
gridexprow.TextMatrix(gridexprow.row, 7) = rexp.Fields(5)
colorcol.Add rexp.Fields(2), rexp.Fields(1)
MsgBox colorcol.Item(rexp.Fields(1))
rexp.MoveNext
Wend
MsgBox colorcol.Count
MsgBox colorcol.Item(1)



Using this, when I try to retreive the objects of the collection into the message box, the following error occurs:

First Message Box in the while loop works perfectly fine & gives the desired result

Second one works perfectly fine & gives the desired Item Count

The third message box is the one that creates the problem. It gives the following error message:

Either BOF or EOF is true or the current record has been deleted. Requested operation requires a current record.

Any Solutions is more than appreciated.

Thanks in Advance.

Try explicitly storing the value from the fields by referencing its .Value property.

Dim colorcol As New Collection

qexp = "Select * from [br_explosive] where name = '" & cmbexprow.Text & "'"
Set rexp = contoRCB.Execute(qexp)

While Not rexp.EOF
gridexprow.TextMatrix(gridexprow.row, 1) = rexp.Fields(1).Value
gridexprow.TextMatrix(gridexprow.row, 3) = rexp.Fields(6).Value
gridexprow.TextMatrix(gridexprow.row, 7) = rexp.Fields(5).Value
colorcol.Add rexp.Fields(2).Value, rexp.Fields(1).Value
MsgBox colorcol.Item(rexp.Fields(1).Value)
rexp.MoveNext
Wend
MsgBox colorcol.Count
MsgBox colorcol.Item(1)

First of all thanks

It did work

Now if you could please tell me why was the earlier method not working? Is there any specific reason behind it?

I believe that you were adding the Field object instance to the collection instead of the value of the Field object. You had moved to EOF in your loop, so when your message box tried to access the instance of the Field object, there wasn't a record.

I believe that you were adding the Field object instance to the collection instead of the value of the Field object. You had moved to EOF in your loop, so when your message box tried to access the instance of the Field object, there wasn't a record.

Thanks 00100b

I think I have learnt a new thing in VB today.

Hope to learn more of these things from you people. Its nice to be here

Thanks again