Access 2016中VBA设置子窗体后RecordsetClone提示数据源选择问题

1. 绑定的ADO记录集被提前关闭：在XYZ窗体的Form_Open事件中，你将rsF绑定到Me.Recordset后立刻执行了rsF.Close，导致窗体关联的记录集对象处于关闭状态，调用RecordsetClone自然会触发“对象关闭时不允许操作”的错误。
2. 记录集函数未正确返回对象：OpenMyRecordset函数没有将创建好的记录集赋值给函数返回值，导致get_rsData无法获取到有效的打开状态记录集。
3. 存储过程调用未指定正确命令类型：调用存储过程sp_F时未使用adCmdStoredProc命令类型，可能导致SQL解析异常。

1. 修正记录集函数的返回逻辑 #

修改OpenMyRecordset函数，确保返回创建并打开的记录集：

Public Function OpenMyRecordset(rs As ADODB.Recordset, strSQL As String, Optional rrCursor As rrCursorType, Optional rrLock As rrLockType, Optional bolClientSide As Boolean) As ADODB.Recordset
    Debug.Print strSQL
    On Error GoTo ConnectionError
    If con.State = adStateClosed Then
        con.ConnectionString = conConnection
        con.Open
    End If

    On Error GoTo RecordsetError
    Set rs = New ADODB.Recordset
    With rs
        .ActiveConnection = con
        .CursorType = IIf((rrCursor = 0), adOpenStatic, rrCursor)
        .LockType = IIf((rrLock = 0), adLockReadOnly, rrLock)
        ' 调用存储过程时指定命令类型为adCmdStoredProc
        .Open strSQL, , , , adCmdStoredProc
        ' 如果需要客户端游标，可断开与连接的关联（适合只读场景）
        If bolClientSide Then
            .ActiveConnection = Nothing
        End If
        If .EOF And .BOF Then
            NoRecords = True
            Exit Function
        End If
    End With

ExitProcedure:
    ' 关键：将创建的记录集赋值给函数返回值
    Set OpenMyRecordset = rs
    Exit Function

RecordsetError:
    MsgBox Err.Description
    Set rs = Nothing
    Exit Function

ConnectionError:
    MsgBox "No connection to server"
    Set rs = Nothing
    Exit Function
End Function

2. 调整get_rsData函数的返回方式 #

改用参数化方式调用存储过程（避免SQL注入风险），并直接返回有效的ADO记录集：

Function get_rsData(x As Integer, y As Integer, z As Integer) As ADODB.Recordset
    Dim rs As ADODB.Recordset
    OpenMyRecordset rs, "sp_F", rrOpenDynamic, rrLockOptimistic, False
    ' 给存储过程传递参数
    rs.Parameters("@x").Value = x
    rs.Parameters("@y").Value = y
    rs.Parameters("@z").Value = z
    rs.Open
    Set get_rsData = rs
End Function

3. 修改XYZ窗体的事件代码 #

• 在Form_Open中绑定记录集后，不要手动关闭记录集，让窗体管理其生命周期：

Private Sub Form_Open(Cancel As Integer)
    Dim rsF As ADODB.Recordset
    Set rsF = get_rsData(10, 20, 30)
    Set Me.Recordset = rsF
    ' 不要执行rsF.Close或Set rsF = Nothing
End Sub

• 在Form_Close事件中统一关闭记录集，释放资源：

Private Sub Form_Close()
    If Not Me.Recordset Is Nothing Then
        If Me.Recordset.State = adStateOpen Then
            Me.Recordset.Close
        End If
        Set Me.Recordset = Nothing
    End If
End Sub

4. 验证RecordsetClone调用 #

此时再执行以下代码即可正常获取子窗体的RecordsetClone：

Dim rst As ADODB.Recordset
Set rst = Me![SubForm1].Form.RecordsetClone
' 后续操作...
rst.Close
Set rst = Nothing

内容的提问来源于stack exchange，提问作者sai mahesh