Retrieve Identity Field Value from a stored procedure into a form

[feets]

OK first time poster, so hello everyone in advance. Right i'm sure this is a simple problem to solve, but I'm just getting the hang of SQL 2000. What I've got is a form where I input the values and then through a procedure these values are inserted into a table. That's fine. However I now need to open a subform which is linked by the ID field created through the first procedure. How do I retrieve that value back into the form??


The procedure code is

CREATE PROCEDURE InsertFamilyDet ails

@CarerID varChar(6),
@FamilyName varChar(30),
@Address1 varChar(30),
@Address2 varChar(30),
@Address3 varChar(30),
@PostCodeMain varChar(4),
@PostCodeSub varChar(30),
@PhoneNo varChar(16),
@LocalTransport varChar(200),
@Leisure varChar(200),
@School varChar(200),
@Rules varChar(250),
@Result int OUTPUT

AS

DECLARE @FamilyID int;

BEGIN TRANSACTION

-- Insert New Family

SELECT @FamilyID=@@Ide ntity

INSERT INTO tblWfsFamilyDet ails

(intCarerID,txt FamilyName,txtA ddress1,txtAddr ess2,txtAddress 3,txtPostCodeMa in,txtPostCodeS ub,txtPhoneNo,m emoLocalTranspo rt, memoLeisure, memoSchool,memo Rules)

VALUES

(@CarerID,@Fami lyName,@Address 1,@Address2,@Ad dress3,@PostCod eMain,@PostCode Sub,@PhoneNo,@L ocalTransport,@ Leisure,@School ,@Rules)



IF @RESULT<1

BEGIN

ROLLBACK TRANSACTION;

RETURN -1

END

SET @RESULT=1

COMMIT TRANSACTION
GO

Whereas, the VB Code is

Private Sub cmdInsert_Click ()
Set cmd = New ADODB.Command
cmd.ActiveConne ction = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "insertFamilyDe tails"

' FieldNames Assigned to FormControls

cmd.Parameters. Append cmd.CreateParam eter("CarerID", adInteger, adParamInput, 6, txtCarerID)
cmd.Parameters. Append cmd.CreateParam eter("FamilyNam e", adVarChar, adParamInput, 30, txtFamilyName)
cmd.Parameters. Append cmd.CreateParam eter("Address1" , adVarChar, adParamInput, 30, txtAddress1)
cmd.Parameters. Append cmd.CreateParam eter("Address2" , adVarChar, adParamInput, 30, txtAddress2)
cmd.Parameters. Append cmd.CreateParam eter("Address3" , adVarChar, adParamInput, 30, txtAddress3)
cmd.Parameters. Append cmd.CreateParam eter("PostCodeM ain", adVarChar, adParamInput, 4, txtPostCodeMain )
cmd.Parameters. Append cmd.CreateParam eter("PostCodeS ub", adVarChar, adParamInput, 4, txtPostCodeSub)
cmd.Parameters. Append cmd.CreateParam eter("PhoneNo", adVarChar, adParamInput, 12, txtPhoneNo)
cmd.Parameters. Append cmd.CreateParam eter("LocalTran sport", adVarChar, adParamInput, 200, memoLocalTransp ort)
cmd.Parameters. Append cmd.CreateParam eter("Leisure", adVarChar, adParamInput, 200, memoLeisure)
cmd.Parameters. Append cmd.CreateParam eter("School", adVarChar, adParamInput, 200, memoSchool)
cmd.Parameters. Append cmd.CreateParam eter("Rules", adVarChar, adParamInput, 200, memoRules)
cmd.Parameters. Append cmd.CreateParam eter("Result", adInteger, adParamOutput) ' OutPut Returns Result Parameter a Value

cmd.Execute

Res = cmd("Result")

If (Res = 1) Then
MsgBox "Family Inserted Successfully", , "Insert Family"
End If

Set cmd.ActiveConne ction = Nothing


End Sub

Apologise for wasting your time if this something obvious, but like I say I'm learning.

[scripto]

pretty simple - in your stored procedure immediately after the line values(.....) do this: set @retval = @@IDENTITY

that will automatically put the new ID into the variable @retval. Then you can do
Set @RESULT = @retval

[almaz]

pretty simple - in your stored procedure immediately after the line values(.....) do this: set @retval = @@IDENTITY

that will automatically put the new ID into the variable @retval. Then you can do
Set @RESULT = @retval

scope_identity( ) function is a better choice than @@IDENTITY as there might be some triggers defined on tblWfsFamilyDet ails table.