update sql table from access form (.adp)

[LTusing]

I wish to update database data housed on sqlserver2K via Access 2003(.adp) front-end. My current codes is noted below for table: Consent:icd_id (int), pt_id(int), vdt_con (smalldatetime), cons_dt(smalldatetime), vdt_hip1(smalldatetime), hipa_dt(smalldatetime). My code thus far is not acceptable and I would appreciate your advice at this point.

CREATE PROCEDURE procConsentUpdate(
@icd_id int = NULL,
@pt_id int = NULL,
@RetCode int = NULL OUTPUT,
@RetMsg varchar(150) = NULL OUTPUT)
AS
DECLARE @Err int
DECLARE @Exists bit
DECLARE @strSQL nvarchar(200)
DECLARE @ConsentCount int
SET NOCOUNT ON

/* Check if icd exists. */

IF @icd_id IS NULL
BEGIN
SELECT @RetCode = 0,
@RetMsg = 'icd_id required.'
RETURN
END

--Bail if record does not exist
IF
(SELECT COUNT(*) FROM Consent
WHERE icd_id = @icd_id) = 0
BEGIN
SELECT @RetCode = 0,
@RetMsg = 'icd_id does not exist.'
RETURN
END


SELECT @strSQL = N'UPDATE CONSENTSET '

IF @pt_id IS NOT NULL
BEGIN
SELECT @strSQL = @strSQL +
'pt_id = ''' + @pt_id + ''', '
--Flag indicating that pt_id exists
SELECT @Exists = 1
END
ELSE
--Set flag indicating no pt_id exists
SELECT @Exists = 0

--Bail if nothing to update
IF @Exists = 0
BEGIN
SELECT @RetCode = 0,
@RetMsg = 'Nothing to do.'
RETURN
END

--Strip the trailing comma
SELECT @strSQL=LEFT ( @strSQL, LEN (@strSQL) -1)

--Add the WHERE clause
SELECT @strSQL = @strSQL +
' WHERE icd_id = ' + CAST (@icd_id AS varchar(7))

--Generate a plan
EXECUTE sp_executesql @strSQL

--If error, branch to error handler
SELECT @Err = @@ERROR
IF (@Err <> 0) GOTO HandleErr

--If we got this far, then no error
SELECT @RetCode = 1,
@RetMsg = 'Record Edited'
RETURN

HandleErr:
SELECT @icd_id = 0,
@RetCode = 0,
@RetMsg = 'Runtime Error: ' + CAST(@Err AS varchar(10))
RETURN
GO

Visual Basic - Form Control
Private Sub Update_Record_Click()
'Declare new connection and recordset
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim str As String

'Create a new connection
Set adoConnection = New ADODB.Connection
'Create a new recordset
Set adoRecordset = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = gcnn
cmd.CommandText = "procConsentUpdate"
cmd.CommandType = adCmdStoredProc
cmd.Execute

End Sub