How to use unbound form with stored procedure

[tis9700]

Good morning,
I've been working on this for a couple of days and I concede that I need some help.

This is being developed using SQL Server 2000, Ms Access 2002 ADP.

I created a stored procedure (usp_InsertClass) to insert class information.
I tested it in Query Analyzer and it inserts the record and returns the Scope_Identity.

Next..

I have a Parent Form and Child Form. The parent forms record source is a stored procedure that selects an active Instructor that teaches the class and populates the Parent form with their information. I use a separate dialog form with a combobox to pass a parameter (SSN) to that stored procedure.

Next....
This is where I've been stopped in my tracks. The child form
is unbound. I created the SP but I'm having problems with the parameters. I'm not sure.

Stored Procedure...

CREATE PROCEDURE usp_InsertClass
(@instID int,
@classDate DateTime,
@classType tinyint,
@courseType tinyint,
@classCounty char(2),
@ttlHrsTaught smallint,
@Enrolled smallint,
@Graduated smallint,
@schoolHrs bit,
@Males smallint,
@White smallint,
@Black smallint,
@nativeAm smallint,
@Hispanic smallint,
@Other smallint,
@classComments varchar(500),
@classID int OUTPUT)
AS
SET NOCOUNT ON
INSERT INTO [HS_PROG_DEV].[dbo].[Classes]
(instID, classDate, classType, courseType, classCounty, ttlHrsTaught, Enrolled, Graduated, schoolHrs, Males, White, Black, nativeAm, Hispanic, Other, classComments)
VALUES
(@instID, @classDate, @classType, @courseType, @classCounty, @ttlHrsTaught, @Enrolled, @Graduated, @schoolHrs, @Males, @White, @Black, @nativeAm, @Hispanic, @Other, @classComments)

SELECT @classID = SCOPE_IDENTITY()


Note: I get the instID from the parent form when an instructor is selected by putting =Forms!frmClassInstructor!instID in the control source of the instID textbox on the child form.

But I need to have the classID returned from the SP. I've seen a lot of examples and I've tried them but to no avail.So I need to start fresh. This is what I currently have for code, it's for a button on the child form.

Code..

Private Sub cmdSave_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim parm As New ADODB.Parameter

On Error GoTo Error_Handler

'Create a new ADO Connection object
Set cn = New ADODB.Connection

'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "BH01WF54"
.Properties("Initial Catalog").Value = "HS_PROG_DEV"
.Properties("Integrated Security").Value = "SSPI"
.Open
End With

' Execute command to run stored procedure

Set cmd = New ADODB.Command


With cmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "usp_InsertClass"
.CommandTimeout = 15
.Parameters.Refresh
.Parameters("@instID") = Me!instID.Text
.Parameters("@classDate") = Me!ClassDate.Text
.Parameters("@classType") = Me!ClassType.Text
.Parameters("@courseType") = Me!cboCourseType.Text
.Parameters("@classCounty") = Me!cboClassCounty.Text
.Parameters("@ttlHrsTaught") = Me!TtlHrsTaught.Text
.Parameters("@Enrolled") = Me!Enrolled.Text
.Parameters("@Graduated") = Me!Graduated.Text
.Parameters("@schoolHrs") = Me!schoolHrs.Text
.Parameters("@Males") = Me!Males.Text
.Parameters("@White") = Me!White.Text
.Parameters("@Black") = Me!Black.Text
.Parameters("@nativeAm") = Me!NativeAm.Text
.Parameters("@Hispanic") = Me!Hispanic.Text
.Parameters("@Other") = Me!Other.Text
.Parameters("@classComents") = Me!Comments.Text
.Execute
End With


Set cmd = Nothing
Set cn = Nothing



Exit Sub



Error_Handler:
MsgBox "An error occurred. The error number is " & _
Err.Number & " and the description is " & _
Err.Description
Exit Sub

End Sub

Obviously, the above code is not returning the classID or a recordset.

This form is for a data entry and I may need to add another subform which is why I want the classID returned.

Thanks for your help.

 

[tis9700]

Hi all,
I reposted this thread under forum for Microsoft Access Project ADP. Access Forms probably wasn't the best place to post it.
Thanks

 

[JoeAtWork]

You need to add the ClassID as an output parameter in your VB code, and then check it's value after the cmd.Execute.

MsgBox "ClassID = " & cmd.Parameters("@ClassID")

I don't know if this makes a difference, but when assigning values to output parameters in stored procedures, I usually do it this way:

SET @classID = SCOPE_IDENTITY()

 

[tis9700]

Hi JoeAtWork,
I eventually did get it to work just as you described. Thanks for your reply. I've got one more question..

They've decided to add a subform for entering assistants to the classes and I was wondering if I could use my code in a different event procedure rather than having the user click a button on that form before moving to the subform. If so which event procedure would you use?

I'll have to write another sp for the assistants but that will be no problem.

Thanks