Beware of scope when adding items to Collection objects
When adding items to a Collection object, remember not to add something
The following code will add a particular field's value from an ADODB.RecordSet object to a Collection. However, the scope of the RecordSet object is only within this function. After it completes, each item within the Collection object will have a null value.
Public Function GetPeriods() As Collection
Dim rs As ADODB.Recordset
Dim col As Collection
Set rs = New ADODB.Recordset
Set col = New Collection
rs.Open "select distinct period from table order by period desc", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
While Not rs.EOF
col.Add rs!period
rs.MoveNext
Wend
Set GetPeriods = col
End Function
In the above example, after the function executes, the reference to the RecordSet will be severed.
To fix this, add a string value to the Collection instead of a property of the RecordSet. Change the col.Add line from:
col.Add rs!period
to:
col.Add CStr(rs!period)