Retrieve Identity Field Value from a stored procedure into a form

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 InsertFamilyDetails

@.CarerIDvarChar(6),
@.FamilyNamevarChar(30),
@.Address1varChar(30),
@.Address2varChar(30),
@.Address3varChar(30),
@.PostCodeMain varChar(4),
@.PostCodeSubvarChar(30),
@.PhoneNovarChar(16),
@.LocalTransport varChar(200),
@.Leisure varChar(200),
@.School varChar(200),
@.RulesvarChar(250),
@.Resultint OUTPUT

AS

DECLARE @.FamilyID int;

BEGIN TRANSACTION

-- Insert New Family

SELECT @.FamilyID=@.@.Identity

INSERT INTO tblWfsFamilyDetails

(intCarerID,txtFamilyName,txtAddress1,txtAddress2, txtAddress3,txtPostCodeMain,txtPostCodeSub,txtPhon eNo,memoLocalTransport, memoLeisure, memoSchool,memoRules)

VALUES

(@.CarerID,@.FamilyName,@.Address1,@.Address2,@.Address 3,@.PostCodeMain,@.PostCodeSub,@.PhoneNo,@.LocalTransp ort,@.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.ActiveConnection = con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "insertFamilyDetails"

' FieldNames Assigned to FormControls

cmd.Parameters.Append cmd.CreateParameter("CarerID", adInteger, adParamInput, 6, txtCarerID)
cmd.Parameters.Append cmd.CreateParameter("FamilyName", adVarChar, adParamInput, 30, txtFamilyName)
cmd.Parameters.Append cmd.CreateParameter("Address1", adVarChar, adParamInput, 30, txtAddress1)
cmd.Parameters.Append cmd.CreateParameter("Address2", adVarChar, adParamInput, 30, txtAddress2)
cmd.Parameters.Append cmd.CreateParameter("Address3", adVarChar, adParamInput, 30, txtAddress3)
cmd.Parameters.Append cmd.CreateParameter("PostCodeMain", adVarChar, adParamInput, 4, txtPostCodeMain)
cmd.Parameters.Append cmd.CreateParameter("PostCodeSub", adVarChar, adParamInput, 4, txtPostCodeSub)
cmd.Parameters.Append cmd.CreateParameter("PhoneNo", adVarChar, adParamInput, 12, txtPhoneNo)
cmd.Parameters.Append cmd.CreateParameter("LocalTransport", adVarChar, adParamInput, 200, memoLocalTransport)
cmd.Parameters.Append cmd.CreateParameter("Leisure", adVarChar, adParamInput, 200, memoLeisure)
cmd.Parameters.Append cmd.CreateParameter("School", adVarChar, adParamInput, 200, memoSchool)
cmd.Parameters.Append cmd.CreateParameter("Rules", adVarChar, adParamInput, 200, memoRules)
cmd.Parameters.Append cmd.CreateParameter("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.ActiveConnection = Nothing

End Sub

Apologise for wasting your time if this something obvious, but like I say I'm learning.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|||

Quote:

Originally Posted by 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

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