Adding field value to a collection, directly from recordset, does not work (1 Viewer)

[braleyman]

Good morning. I’m an intermediate VBA programmer with a question about the behavior of collections.
Scenario: I’m playing around with collections and decided to add items to my collection from an open recordset that has two test fields (LAB_CODE and LAB_RATES).
Problem: I’m opening the “tblLabs” recordset, and using a Do Loop to loop through all records and add the LAB_CODE field to a collection, named colLabs. The collection contains 11 new items after completing the loop, which is perfect, but they all show “value <No current record>” in the locals window of the VBA module. While still inside the same procedure (so everything is still in scope), If I try to call an item from the collection I get an error code 3021: No current record.
My Question: I’m not so much looking for a solution to this problem, because I’ve already found a work-around. I am just looking to find an answer to this phenomena. I’ve provided the original code and the modified code that solved the problem, as well as a description of what’s occurring. Thank you so much for your time.
ORIGINAL CODE:

Public Sub LoadLabs()
Dim colLabs As Collection
Dim db As DAO.Database
Dim rs As DAO.Recordset
On Error GoTo ErrorHandler

Set colLabs = New Collection
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLabs")
Do
    
    colLabs.Add rs!LAB_CODE
    rs.MoveNext
    
Loop Until rs.EOF
    
Stop
GoTo Done
ErrorHandler:
    ErrorMessage "Lab Collection"
    
Done:
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Result: the colLabs collection contains 11 items, all of which contain a value of “<No current record>”.
Fix: I declared a new string variable, called ‘dummy’, that is assigned the value of the current record. ‘dummy’ is then added to the colLabs collection. My collection now populates with the values of the 11 items in the recordset. The modified code is as follows:
MODIFIED CODE:

Public Sub LoadLabs()[/FONT]
[FONT=Calibri][/FONT] 
[FONT=Calibri]Dim colLabs As Collection
Dim db As DAO.Database
Dim rs As DAO.Recordset[/FONT]
[FONT=Calibri]Dim dummy as string[/FONT]
[FONT=Calibri][/FONT] 
[FONT=Calibri]On Error GoTo ErrorHandler[/FONT]
[FONT=Calibri]
Set colLabs = New Collection
Set db = CurrentDb[/FONT]
[FONT=Calibri]Set rs = db.OpenRecordset("tblLabs")[/FONT]
[FONT=Calibri][/FONT] 
[FONT=Calibri]Do
    [/FONT]
[FONT=Calibri]    dummy = rs!LAB_CODE
    colLabs.Add dummy
    rs.MoveNext
    
Loop Until rs.EOF
    
Stop[/FONT]
[FONT=Calibri][/FONT] 
[FONT=Calibri]GoTo Done[/FONT]
[FONT=Calibri][/FONT] 
[FONT=Calibri]ErrorHandler:
    ErrorMessage "Lab Collection"
    
Done:
    rs.Close
    Set rs = Nothing
    Set db = Nothing[/FONT]
[FONT=Calibri][/FONT] 
[FONT=Calibri]End Sub

 

[AOB]

I'm guessing it's because you are not adding the value for that current record to the collection, but adding the field itself?

As an aside, when adding items to a collection, I believe you have to add a key as well (but if your code is not erroring that would suggest otherwise...) I thought it was mandatory...

Why not try :

Set colLabs = New Collection
Set db = CurrentDb
Set rs = db.OpenRecordset("tblLabs")
 
With rs
 
  If .RecordCount > 0 Then
 
    .MoveFirst
 
    Do Until .EOF
 
      colLabs.Add .Fields("LAB_CODE").Value, .Fields("LAB_CODE").Value
      .MoveNext
 
    Loop
 
  End If
 
End With

If all the records in your dataset have a unique LAB_CODE then this should work; if there are duplicates but you only want a collection of unique values, you'll need to stick an On Error Resume Next just before you add the item to the collection, then revert back to your standard handler immediately afterwards...

 

[Frothingslosh]

AOB, you can add an item to a collection without a key - collections are always numbered, and that number can be used instead. Keys are just normally a better option.

 

[braleyman]

AOB, thank you for the suggestion though. It would appear that after adding the field to the collection, it's still tied to the memory location where the data is stored for that record field. When you move to the next record, it empties out the data that was just added to the collection. I just haven't found any documentation on this yet. Take care.

 

[MarkK]

Yeah, your "problem" code adds an ADODB.Field object to the collection, whereas your solution code adds a string. Use the Typename() function to find out what datatype an object is, if you aren't sure.

debug.print typename(colLabs(1))

You could fix your problem code by using the ADODB.Field.Value property . . .

    colLabs.Add rs!LAB_CODE[COLOR="Blue"][B].Value[/B][/COLOR]

 

[braleyman]

I'll be darned. That's good stuff! I'll have to try that tomorrow. Thank you so much.