Microsoft Access Development

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)