Can't use disconnected RecordSet as a ListBox RecordSource RRS feed

  • 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
    End With
    For I = Lower To Upper
        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
    Next I
    Debug.Print ("Count: " & rstADO.RecordCount)
        On Error Resume Next
        Exit Function
        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?



    Monday, May 13, 2019 5:30 PM

All replies

  • 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

    Monday, May 13, 2019 7:28 PM