Using stored procedure and ado to display recordset

[Jack]

Hi,
I have a stored procedure that is in the pubs database. The stored procedure
is:
CREATE PROCEDURE sp_select
@type varchar(40),
@royalty int
AS
select title_id, title, type, royalty
from titles
where
type = @type
AND
royalty = @royalty
GO

Now I want to call this procedure from an Access form so that the recordset
is selected in the form.
I am having difficulty to come up with the recordset object.
Here is the code associated with the form that inputs the type and royalty
field. I want to display the recordset that is coming out of the stored
procedure. Thanks

CODE:

Private Sub cmdRunProc2_Click()
Dim conn As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmdSelect As ADODB.Command
Dim strType As String
Dim curPercent As String

Set conn = New ADODB.Connection
Set cmdSelect = New ADODB.Command

conn.Provider = "SQLOLEDB"

conn.ConnectionString = "Provider=SQLOLEDB.1;Initial Catalog=pubs;Data
Source=USILDEK1-21001;User ID=sa;Password=alloyd;"
conn.Open

'Code added

cmdSelect.CommandType = adCmdStoredProc
cmdSelect.CommandText = "sp_select"
Set cmdSelect.ActiveConnection = conn

' Get the form values
strType = Forms!frmParamPassing_select!txtBookType
curPercent = Forms!frmParamPassing_select!txtPercent
curPercent1 = CCur(Forms!frmParamPassing_select!txtPercent)

' Add the parameters
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@Type",
adVarWChar, adParamInput, 12, strType)
cmdSelect.Parameters.Append cmdSelect.CreateParameter("@Percent",
adCurrency, adParamInput, , curPercent1)

' Execute the command
Set rs = cmdSelect.Execute



With rst
rst.MoveFirst
Do Until rst.EOF

CODE Required

Loop
End With

Set conn = Nothing
Set cmdSelect = Nothing
Set rst = Nothing
End Sub

 

[Jack]

Folks,
Never mind. I got it. Thanks

 

[Tony_VBACoder]

What did you do to fix it?

Folks,
Never mind. I got it. Thanks