Set Subform's Recourdsource to Stored Procedure

[cbirknh]

Hi,
I am sorry if I am posting this on the wrong forum; if that is the case, please direct me to the appropriate one.
I am trying to use a SQL Server 2000 stored procedure ("dbo.spPatientLTComplications&quot as the data source to a subform in an access .adp.
I set the recordsource using the procedure below which works perfectly the first couple of tries, then eventually I get an error message (#2467: The expression you entered refers to an object that is closed or doesn't exist). When I go to the form's properties, I see stored under the form's record source this:
{ call dbo.spPatientLTComplications(?) }

What am I doing wrong?

Thank you,
Chris

Public Sub SubformRecordSource()

Dim intResponse As Integer

Dim cnn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim prm1 As ADODB.Parameter

Set cnn = Application.CurrentProject.Connection
Set cmd.ActiveConnection = cnn

'----- Input Parm
Set prm1 = cmd.CreateParameter("Input", adInteger, adParamInput)
cmd.Parameters.Append prm1
prm1.Value = gblPatientID

' Set up a command object for the stored procedure and execute
cmd.CommandText = "dbo.spPatientLTComplications"
cmd.CommandType = adCmdStoredProc

With rst
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open cmd, Options:=adCmdStoredProc
End With

If rst.EOF Then
Me.Visible = False
Else
Me.Visible = True
Set Me.Recordset = rst
End If

Set prm1 = Nothing
Set cmd = Nothing
Set rst = Nothing
Set cnn = Nothing
End Sub

 

[cmmrfrds]

Have you tested the stored procedure under conditions where YOU KNOW that it does not return a resultset? You could plug a 0 in the Parm and see what happens.

Since has worked then, it is probably a control flow problem since the syntax looks okay. I would put a debug.print before testing for the rst.EOF to see if there is a resultset to work with.

Debug.Print "Some results = " rst(0)
etc.....