Hi,
I am using a simple stored procedure from the pubs database to get a resultset. The stored procedure code is as follows:
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 am trying to use this stored procedure (passing parameter for type and royalty) as source of recordset. The recordset should display multiple records. The form is unbound. However, through the looping process I am expecting multirow. The form display resultset text boxes are in the detail section. However, I am getting only one record in the resultset in the form when I am expecting multi records. I have no idea why this is happening. Any help is appreciated.
CODE:
Private Sub cmdRunProc2_Click()
'To make all the fields visible

txtTitleID.Visible = True
txtTitle.Visible = True
txtType.Visible = True
txtRoyalty.Visible = True

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




Do While Not rs.EOF
    txtTitleID = rs.Fields(0)
    txtTitle = rs.Fields(1)
    txtType = rs.Fields(2)
    txtRoyalty = rs.Fields(3)
   rs.MoveNext
Loop

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

Private Sub Form_Load()
     txtTitleID = ""
     txtTitleID.Visible = False
     txtTitle = ""
     txtTitle.Visible = False
     txtType = ""
     txtType.Visible = False
     txtRoyalty = ""
     txtRoyalty.Visible = False
End Sub

Hmmm... long que. I presume you need multiple records in txtTitleID etc. but look what are you doing here in loop;
txtTitleID = rs.Fields(0)
With every loop it will refresh the value of text box rather than appending it.
Use this => txtTitleID = txtTitleID & vbcrlf & rs.Fields(0)

Don't forget to make your text box multiline.

Cheers,
Prabodh