The object you entered is not a valid recordset property

[anniebai]

I have a subform, whose data source is a parametered stored procedure.
The following code is added in Form_open, but I always got the error msg 'the object you entered is not a valid recordset property'. I've read the disconnection problem that posted on MS website, however it seems not for my case. I tried cursor too, not work.
Any suggestions are very much appreciated~~

Private Sub WhoFinishedSubFormRequery()
Dim sql As String
    Dim cmd As ADODB.Command, rst As ADODB.Recordset
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "EXEC dbo.spWhoFinishedBySurveyAndYear '" & Me.surveyName & "'," & Me.surveyYear
    cmd.CommandType = adCmdText
    Set rst = cmd.Execute
    Set Me![frmTrackingWhoFinished].Form.Recordset = rst //where the error is
    Set rst = Nothing
    Set cmd = Nothing
End Sub

[ADezii]

I have a subform, whose data source is a parametered stored procedure.
The following code is added in Form_open, but I always got the error msg 'the object you entered is not a valid recordset property'. I've read the disconnection problem that posted on MS website, however it seems not for my case. I tried cursor too, not work.
Any suggestions are very much appreciated~~

Private Sub WhoFinishedSubFormRequery()
Dim sql As String
    Dim cmd As ADODB.Command, rst As ADODB.Recordset
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "EXEC dbo.spWhoFinishedBySurveyAndYear '" & Me.surveyName & "'," & Me.surveyYear
    cmd.CommandType = adCmdText
    Set rst = cmd.Execute
    Set Me![frmTrackingWhoFinished].Form.Recordset = rst //where the error is
    Set rst = Nothing
    Set cmd = Nothing
End Sub

I think that you need a change in Syntax. The following code has been tested and is fully operational:

Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
    
Set cmd = New ADODB.Command

With cmd
  .ActiveConnection = CurrentProject.Connection
  .CommandText = "spEmployees"
  .CommandType = adCmdStoredProc
End With

Set rst = cmd.Execute()

With rst
  Do While Not .EOF
    Debug.Print ![FirstName] & " " & ![LastName]
      .MoveNext
  Loop
End With

DoCmd.OpenForm "frmEmployees", acNormal, , , acFormEdit, acHidden
Set Forms!frmEmployees.Recordset = rst
DoCmd.OpenForm "frmEmployees"
    
Set rst = Nothing
Set cmd = Nothing