Can't use disconnected RecordSet as a ListBox RecordSource

Question

I have a disconnected RecordSet function I an using in a MS-Access project based off of one I saw on the Internet in VBA.

Public Function rs_Nums(ByVal Lower As Long, _
                        ByVal Upper As Long) _
                        As ADODB.RecordSet
    'Purpose:       Creates a RecordSet of Sequential numbers
    'Parameters:    Lower As Long - Starting point of the sequence
    '               Upper As Long - Ending point of the sequence
    'Returns:       A sequence of numbers from Lower to Upper as a RecordSet
    On Error GoTo rs_Nums_Err
    Dim rstADO As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim I As Long
Set rstADO = New ADODB.Recordset
With rstADO
    .Fields.Append "NumID", adInteger, , adInteger
    '.Fields.Append "FirstName", adVarChar, 10, adFldMayBeNull
    '.Fields.Append "LastName", adVarChar, 20, adFldMayBeNull
    '.Fields.Append "Email", adVarChar, 64, adFldMayBeNull
    '.Fields.Append "Include", adInteger, , adFldMayBeNull
    '.Fields.Append "Selected", adBoolean, , adFldMayBeNull
    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Open
End With
    
For I = Lower To Upper
    rstADO.AddNew
    rstADO.Fields(0) = I
    'rstADO.Fields(1) = rstDAO!FirstName
    'rstADO.Fields(2) = rstDAO!LastName
    'rstADO.Fields(3) = rstDAO!Email
    'rstADO.Fields(4) = Null
    'rstADO.Fields(5) = Null
    rstADO.Update
    rstADO.MoveNext
Next I

Debug.Print ("Count: " & rstADO.RecordCount)
rs_Nums_Exit:
    On Error Resume Next
    'rstADO.Close
    Exit Function
    
rs_Nums_Err:
    Resume rs_Nums_Exit
End Function

I am populating the RecordSet with just numbers(1-60) and it seems to be working, because the Debug.Print line is showing a record count of 60. However, when it exits the function I get this error:

Can someone tell me why and how to fix it?

Thanks,

---

MRM256

Answer 1

In your function, you never set your function's result value to the recordset.  You need to say

    Set rs_Nums = rstADO

before exiting.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html