Form Button SP call w/ parameters

[Guest]

I have asked this question in the past and have yet to find a clear answer as
I am a VBA novice.

Using Access & SQL 2000, I have a form (Enrollment) where I would like to
capture info in a few unbound textboxes and click a button to call a stored
procedure to perform the insert. Another major snag is that there is a
checkbox that will determine if the SP makes a single or double insert. Both
scenarios perform the same initial insert but if checked a second insert is
performed on a different table that requires the autonumber from the first
insert. Can anyone provide me with easy to understand pseudo-code for the
onclick event and perhaps the SP where my variables are easily identifiable?
Thank you.

 

[dbahooker]

buddy you need to give more details

why dont you just paste the body of your sproc here?

 

[Sylvain Lafontaine]

You have to possibility of doing this: you make the whole process of the
insertion inside a single SP or in two steps using VBA code and ADO objects.

For ADO objects, take a look at the thread « INSERT records » on 9/13/2006.
For the other solution of using a single SP, learning a little knowledge on
how to write T-SQL shouldn't hurt you.

If your question is about how to pass the paramaters to a SP, then the same
thread mentionned before will tell you how to do this with ADO objects.

 

[Guest]

I have the following button code:
Dim cmd As ADODB.Command
Dim pmt As ADODB.Parameter

Set cmd = New ADODB.Command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_NewEnrollment"

''Create Paramters required by the SP
Set pmt = cmd.CreateParameter("....")
cmd.Parameters.Append pmt

''More parameters here

''Execute the SP
cmd.Execute

and the accompanying SP:

INSERT INTO dbo.Enrollment
(ContactID, ScheduleID, PurchaseOrderNumber, Comments,
SelfEnrollment)
SELECT ContactID, ScheduleID, PurchaseOrderNumber, Comments,
SelfEnrollment
FROM dbo.Enrollment

All of the above field names need come from the form's unbound fields. How
should i modify both the button vba and sp to do this? Thanks again.

 

[Sylvain Lafontaine]

If the name of the first parameter is @FirstParameter and is of type integer
and the name of the text box is Text_FirstParameter, then you should write
something:

cmd.Parameters.Append cmd.CreateParameter("@FirstParameter", _
adInteger, adParamInput, , Me.Text_FirstParameter.Value)

Notice that the @ is mandatory for the name of the stored procedure's
parameter.

You also have a Select statement in your SP; so you will have to capture the
resultset in an ADODB recordset.

It is also a bad idea to use the prefix "sp_" for your stored procedures as
this prefix has a special meaning for SQL-Server and can lead to subtle bugs
or loss of performance.

 

[dbahooker]

hogwash

exec mySproc 'myParamValue1'

that's all that there is to it

-Aaron
ADP Nationalist