Save stored procedure output

[ascheper]

Hello,

I have a stored procedure that works fine on it's own, now I need to call it in the before update event of a control on my form and save the output in a database field. I've searched MS knowlege and this site for an answer first, but cannot seem to find it. I may not be asking the correct question, but any help would be greatly appreciated, thank you.

 

[Rolliee]

I do not understand what your 'stored procedure" is nor 'how you call it in.' If you show some code, we can help you. There are all kinds of ways to save data.

rollie@bwsys.net

 

[ascheper]

the stored proceedure simply gets the users login name and I want to store that in a field in the customer table that the the form being used is attached to. I want to place a call to the sp in the before update event of a control, presumably using docmd.openstoredprocedure, and save the resulting user name in another field. I tried the following, which does not work:

me.CallListUser = docmd.OpentoredProcedure "sp_GetUserName"

 

[ascheper]

I think I may need to create a recordset with the SP and then assign the value to my field...

 

[Rolliee]

If the ID you wish is in the bound recordset do a

set rs = me.recordsetclone.

Otherwise open the foreign recordset with an

set rs = currentdb.openrecordset( "MyTable&quot

then find where rs![desiredfield] = me.desiredvalue

rollie@bwsys.net

 

[cmmrfrds]

Is the stored procedure an sql server stored procedure?

 

[ascheper]

Sorry for the delay in answering, yes, the SP is a SQL Server SP. I tried the recordset approach and it doesn't work. VBA doesn't seem to be able to work like that. Still trying to play around with it, but not getting anywhere.

 

[cmmrfrds]

Here is an example of calling an SP with an input parm, return code, output parm, and recordset. It appears that an output parm should be sufficient for your requirements - modify as needed. If you still need help then post the code for your SP.

Function CommandObjectSP()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter, param4 As Parameter

Dim var2 As String, recAffected As Integer
Dim fld As Field, er As Error
Dim connString As String
connString = "Provider=SQLOLEDB.1;Persist Security Info=False; " & _
"User ID=sa;Initial Catalog=msdpn;Data Source=localhost"


cnn.ConnectionString = connString
cnn.Open connString

Set cmd.ActiveConnection = cnn
Set param1 = cmd.CreateParameter("ReturnVal", adSmallInt, adParamReturnValue)
cmd.Parameters.Append param1

Set param2 = cmd.CreateParameter("InInt", adSmallInt, adParamInput)
cmd.Parameters.Append param2
param2.Value = 3
Set param3 = cmd.CreateParameter("Outparm", adVarChar, adParamOutput, 20)
cmd.Parameters.Append param3
param3.Value = "XXXXXX"

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_supplier"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute

For Each er In cnn.Errors
Debug.Print "error is: "; er.Number; " with value = "; er.description
Next

For Each fld In rst.Fields
Debug.Print "field is: "; fld.name; " with value = "; fld.Value
'-- Debug.Print "field name = "; fld.Value
Next

Debug.Print "return code = "; cmd.Parameters("ReturnVal&quot.Value
Debug.Print "input value = "; cmd.Parameters("InInt&quot.Value
Debug.Print "output value = "; cmd.Parameters("OutParm&quot.Value


End Function

 

[ascheper]

Thank you cmmrfrds, I'll try that tonight...