Runtime Error opening a report

[majhl]

Access 2003 ADP, with unbound controls and stored procedures on SQL Server.

I'm getting various errors trying to open a report an setting it's recordset property. The error occurs when I use 'Set Me.Report.Recordset = rst'. I've tried various different variations of this line (recordsource instead etc, etc) and can't seem to get beyond this line.

The stored procedure is tested and works fine. The same code (or something very similar) will work with no problems in a form. So what is it about using a report that raises an error? Anyone have any ideas?

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim paramSY As ADODB.Parameter

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

With cmd

.CommandType = adCmdStoredProc
.CommandText = "SPMissingData"
.ActiveConnection = cnn

End With

Set paramSY = cmd.CreateParameter("@sy", adSmallInt, adParamInput)
paramSY.NAME = "@sy"
paramSY.Direction = adParamInput
If IsNull(Forms!frmMissingDataAll!txtYear) _
Then paramSY.Value = Null Else paramSY.Value = Forms!frmMissingDataAll!txtYear
cmd.Parameters.Append paramSY

rst.Open cmd, , adOpenStatic, adLockOptimistic

If rst.RecordCount <> 0 Then

Set Me.Report.Recordset = rst
    
End If

rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing

 

[majhl]

Just to add some more information:

I'm beginning to think that this is not possible with ADO as whatever I try (e.g. 'Set Me.Report.Recordsource = rst.Source', etc, etc), I get an error message.

Strangely enough, it works if you use DAO and then 'Me.Report.RecordSource = strSQL'.

I think it's specifically with ADO and/or ADO with a stored procedure. Has anyone come across anything like this with reports?