How to display ADO recordset in Access 2010

Question

I need some help solving this issue. I'm a VB novice, by the way. I currently have a stored procedure that accepts 3 parameters and when executed returns a temp table. I tested my procedure in SQL and it's working as expected.

I'm using an Access form as my front-end. It is to accept the 3 parameters via 3 text box controls and then executes my procedure 'on click' (User clicks the search button to execute and fire up SQL). The code below accomplishes this, up until it's time to display my recordsets in a table. I'm stuck on how to display my results from my procedure in a table by utlizing ADO properties I'm ok with either displaying the recordset in a datasheet form or inserting them into a table.

The code seems to choke up at this line, where I'm attempting to view my recordset: rs.Open cmd

Access VB Code

Private Sub cmdExecuteQuery_Click()
   Dim conn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim rs As ADODB.Recordset
   Dim accessionno As String

   Set conn = New ADODB.Connection
   Set rs = New ADODB.Recordset

   conn.ConnectionString = "Driver={SQL Server};Server=***;Database=***;Uid=****;Pwd=***;"
   conn.Open

   Set cmd = New ADODB.Command
   cmd.ActiveConnection = conn
   cmd.CommandType = adCmdStoredProc
   cmd.CommandText = "dbo.NameofStoredProcedure"

  cmd.Parameters.Append cmd.CreateParameter("@worksheetno", adVarChar, 
  adParamInput, 10, Forms!RepeatForm!txtEnterWrkSheetno)
  cmd.Parameters.Append cmd.CreateParameter("@name", adVarChar, 
  adParamInput, 10, Forms!RepeatForm!txtEnterName)
  cmd.Parameters.Append cmd.CreateParameter("@year", adVarChar, 
  adParamInput, 4, Forms!RepeatForm!txtEnterYear)

  With rs
     .ActiveConnection = conn
     .CursorType = adOpenForwardOnly
     .CursorLocation = adUseServer
  End With

  Set rs = cmd.Execute()
  DoCmd.OpenForm "frmRepeats", acViewNormal
  rs.Open cmd

  rs.Close
  conn.Close

  Set rs = Nothing
  Set cmd = Nothing
  Set conn = Nothing
End Sub

SQL

CREATE PROCEDURE [dbo].[NameofStoredProcedure] @worksheetno varchar(10), 
@name varchar(10), @year varchar(4)
AS 
BEGIN
CREATE TABLE #Temp (accessionno varchar (100),
No varchar (100),
worksheetno varchar (10),
name varchar(100),
location varchar (10),
Result varchar (100),
year varchar (4))

INSERT INTO #Temp
SELECT DISTINCT
    a.accessionno,
    e.name + substring(a.Year, 3,2) + '-' + convert(varchar(10), 
    c.SampleTestNum) AS No,
    a.worksheetno,
    b.name,
    c.platetext AS Location,
    d.finalcomment AS Result,
    a.Year
FROM
    tb_sample a (nolock), tb_patient b (nolock), tb_plate c (nolock), 
    tb_finalresult d (nolock), tb_testcode e (nolock)
WHERE
    a.sample_id = b.sample_id
    and a.sample_id = c.sample_id
    and a.sample_id = d.sample_id
    and a.test_id = e.test_id
    and finalcomment like '%1061%'
    and worksheetno = @worksheetno
    and e.name = @name
    and a.year = @year
ORDER BY No
END
select distinct * from #Temp
drop table #Temp
GO