VB6 Recordset not opening with stored procedure

USE [MainDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SYS_Purchasing_CDOrder] @PONo as varchar(10) AS
BEGIN
	DECLARE @HeaderID int
	DECLARE @FrameID int
	DECLARE @RowOn int = 1
	DECLARE @RowCount int
	DECLARE @tmpParts TABLE(HeaderID int, FrameID int, SetCode varchar(3), PCode varchar(99))
	DECLARE @tmpPurchase TABLE(LineID int IDENTITY(1,1) NOT NULL, CustRef varchar(99), HeaderID int, FrameID int, PONo varchar(10), Width int, Height int, Qty int, OrdDate DATE, DelDate DATE, CDPart varchar(99), LB varchar(3), Opener varchar(99), Handed varchar(99))
	
	INSERT INTO @tmpPurchase SELECT JobNo+'_'+CustRef, HeaderID, FrameID, PONo, Width, Height, Qty, CAST(OrdDate AS DATE), CAST(DelDate AS DATE), null, null, null, null FROM SYS_Purchasing WHERE PONo = @PONo AND PartType = 'CDOOR'
	SET @RowCount = (SELECT MAX(LineID) FROM @tmpPurchase)
	WHILE @RowOn <=@RowCount
	BEGIN
		SET @HeaderID = (SELECT HeaderID FROM @tmpPurchase WHERE LineID = @RowOn)
		SET @FrameID = (SELECT FrameID FROM @tmpPurchase WHERE LineID = @RowOn)
		INSERT INTO @tmpParts SELECT HEADER_ID, FRAME_ID, SetCode, PCode FROM PARTS WHERE (HEADER_ID = @HeaderID AND FRAME_ID = @FrameID) AND (SetCode = 'CDR' OR SetCode = 'LB')
		UPDATE @tmpPurchase SET CDPart = (SELECT PCODE FROM @tmpParts WHERE HeaderID = @HeaderID AND FrameID = @FrameID AND SetCode = 'CDR') WHERE LineID = @RowOn
		UPDATE @tmpPurchase SET LB = (SELECT SETCODE FROM @tmpParts WHERE HeaderID = @HeaderID AND FrameID = @FrameID AND SetCode = 'LB') WHERE LineID = @RowOn
		UPDATE @tmpPurchase SET OPENER = (SELECT SASHPCODE FROM SASHES WHERE Header_ID = @HeaderID AND Frame_ID = @FrameID AND SASHPCODE LIKE 'CDS%') WHERE LineID = @RowOn
		UPDATE @tmpPurchase SET Handed = (SELECT SASHType FROM SASHES WHERE Header_ID = @HeaderID AND Frame_ID = @FrameID AND SASHPCODE LIKE 'CDS%') WHERE LineID = @RowOn
		SET @RowOn += 1
	END
	SELECT CustRef, HeaderID, FrameID, PONo, Width, Height, Qty, OrdDate, DelDate, CDPart, LB, Opener, Handed, SupplierCode, ColourExt, ColourInt, Cassette, SpecialRequirements  FROM @tmpPurchase LEFT JOIN SYS_PURCHASING_CDMatrix ON CDPart = SYS_PURCHASING_CDMatrix.WDProdCode 
END

Set Cmd1 = New ADODB.Command
Set rs = New ADODB.Recordset
Set sqlServer = New ADODB.Connection
'extra code here to open the connection, this opens ok
sqlServer.Open
Cmd1.ActiveConnection = sqlServer
Cmd1.CommandType = adCmdStoredProc
Cmd1.CommandText = "[SYS_Purchasing_CDOrder]"
Set Parm1 = Cmd1.CreateParameter("@PONo", adVarChar, adParamInput, 10, OrderNo)
Cmd1.Parameters.Append Parm1
rs.Open Cmd1
Debug.Print rs.State 'This always returns 0