8
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
@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=@@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.
