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:
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:
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:
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
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:
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