How to use unbound form with SP 1

[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.

 

[PWise]

try

   .Parameters("@Other") = Me!Other.Text
        .Parameters("@classComents") = Me!Comments.Text
    [COLOR=red]set rs =[/color].Execute
    End With
    
  
    Set cmd = Nothing
    Set cn = Nothing

 

[tis9700]

Morning pwise,
Thanks for the help.
I keep getting a message when I try to save the record..

Error number 2185. You can't reference a property or method for a control unless the control has the focus.

What would this mean?
Thanks again.

 

[PWise]

[b].Parameters("@instID") = Me!instID.Text[/b]
        .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

Are you getting the error on first line
use

.Parameters("@instID") = Me!instID
        .Parameters("@classDate") = Me!ClassDate
        .Parameters("@classType") = Me!ClassType
        .Parameters("@courseType") = Me!cboCourseType
        .Parameters("@classCounty") = Me!cboClassCounty
        .Parameters("@ttlHrsTaught") = Me!TtlHrsTaught
        .Parameters("@Enrolled") = Me!Enrolled
        .Parameters("@Graduated") = Me!Graduated
        .Parameters("@schoolHrs") = Me!schoolHrs
        .Parameters("@Males") = Me!Males
        .Parameters("@White") = Me!White
        .Parameters("@Black") = Me!Black
        .Parameters("@nativeAm") = Me!NativeAm
        .Parameters("@Hispanic") = Me!Hispanic
        .Parameters("@Other") = Me!Other
        .Parameters("@classComents") = Me!Comments

 

[tis9700]

Hi pwise,
I don't believe so. The error I'm getting now is..

Error number 3265. Item cannot be found in collection corresponding to the requested name or ordinal.

I'm not detecting any misspellings or any parameters out of order in my sp, code or unbound controls.

But what about classID as output? I have an unbound control on my form for it that's not being returned. Doesn't have to be specifically named in the code?

I think the previous error had to do with my classType field. I was experimenting with it a couple of days ago and forgot to change it back.

 

[PWise]

remove this line
On Error GoTo Error_Handler

and let me know on what line you are getting the error

 

[tis9700]

Man pwise,
Do I feel stupid! Misspelling on ..
.Parameters("@classComents") = Me!Comments

@classComments

It works now but I still need it to return the classID to the form because I need to pass it to another subform "fsubAssistants"

Can I run this code from a different event procedure instead of a button?

 

[PWise]

try this forget about the ouput Parameter

change this line in the SP
SELECT @classID = SCOPE_IDENTITY()
to
SELECT SCOPE_IDENTITY() As Classid

in you code add
set rs =.Execute
End With
Do While rs.State = adStateClosed
Set rs = rst.NextRecordset
Loop

me.classid=rs!Classid

 

[tis9700]

Wow! That's slick! Can this be used in a Form After_Update? Or a Lost_Focus Event? That way the user wouldn't have to click a button before tabbing to the fsubAssistant subform to add Assistants.

I'm going to try it.

 

[tis9700]

Bah! No luck! But I learned alot from pwise! Thanks!

 

[PWise]

try the on enter event of the subform control

just make sure that you dont add the same class twice

 

[tis9700]

That's perfect! Thanks!