Stored Procedure Parameter

[Guest]

Hello:

I have a form which has the fields CustomerID and PlanID. This form is used
to select a customer and home plan and load the specifications for the
customers home in to a table there is a command button which has the
following code attached:

Private Sub cmdCreateHome_Click()
On Error GoTo Err_cmdCreateHome_Click

Dim stDocName As String

stDocName = "tsh_AddCustSpec"
DoCmd.OpenStoredProcedure stDocName, acViewNormal, acEdit

Exit_cmdCreateHome_Click:
Exit Sub

Err_cmdCreateHome_Click:
MsgBox Err.Description
Resume Exit_cmdCreateHome_Click

End Sub


Here is the code for the stored procedure which runs:

ALTER PROC tsh_AddCustSpec (
@CustID INTEGER,
@PlanID INTEGER
)
AS
INSERT INTO CustSpecs
(CustSpecs_CustID,
CustSpecs_PlanID,
CustSpecs_RoomID,
CustSpecs_Present)

SELECT @CustID, Plans_PlanID, Plans_RoomID, Plans_Present

FROM PlanSpecs

WHERE Plans_PlanID = @PlanID

In the Input Parameter of the form, I have placed the following:

@CustID int = Forms!frmCreateHome!cboCustomer, @PlanID int =
Forms!frmCreateHome!cboPlan

Upon opening the form, I get the following error:

Bad query parameter '@CustID"


What am I doing wrong?


Thanks
Brennan

 

[J. Clay]

It looks like you have a couple of issues here.

1) If this procedure is the one that is used as a recordset for the form, it
doesn't make much sense. It is an insert procedure and therefore does NOT
return a recordset. If this is NOT the used as the recordset for the form,
then there is NO need for input parameters.

2) Your button command will not work (or at least shouldn't). The procedure
call does not include any parameters and therefore can't function properly.
You should use an ADODB.Command to define the procedure and parameters and
Execute it.

HTH,
Jim

 

[Guest]

Thanks