Return Procedure variable to VBA

[allenjasonbrown@gmail.com]

Hello -

I am using the following code in Access. Can anyone tell me how to return that last output variable back to the VBA? The SP has 3 inputs and 1 output. I am just trying to get the one output and eventually put it on a form.

' Set oConn = New ADODB.Connection
Set oCmd = New ADODB.Command
oConn.ConnectionString = vDBConnectString
oConn.Open
oConn.CommandTimeout = 0
Set oCmd.ActiveConnection = oConn
oCmd.CommandTimeout = 0
oCmd.CommandText = cProcName
oCmd.CommandType = adCmdStoredProc
oCmd.Parameters.Append oCmd.CreateParameter("RetVal", adVarChar, adParamReturnValue)
oCmd.Parameters.Append oCmd.CreateParameter("pText1", adVarChar, adParamInput, 50)
oCmd.Parameters.Append oCmd.CreateParameter("pCombo64", adVarChar, adParamInput, 50)
oCmd.Parameters.Append oCmd.CreateParameter("pCombo66", adVarChar, adParamInput, 50)
'oCmd.Parameters.Append oCmd.CreateParameter("@policy_number", adVarChar, adParamOutput, 50)

oCmd("pText1") = Text1
oCmd("pCombo64") = Combo64
oCmd("pCombo66") = Combo66

[pbaldy]

Try

MsgBox oCmd.Parameters("@policy_number")

[allenjasonbrown@gmail.com]

It is empty

[pbaldy]

What does the SP look like? Did you execute the SP? I have a process that ends like this:

.Parameters.Append .CreateParameter("@RetResNum", adVarChar, adParamOutput, 15)

.Execute

strMsg = .Parameters("@RetResNum")

MsgBox "Reservation Number: " & strMsg

And it correctly returns the value from the SP to the user.

[allenjasonbrown@gmail.com]

Hi -

It is a basic stored procedure with a few inputs a 1 output. It works like a charm if I just run it from Query Analyzer. All the procedure does is perform an insert and an update. The update uses the output field which updates correctly so I know the field gets populated in the proc and also as I mentioned from query analyzer it works great. The procedure works from access and does the insert and update great.....it just looks like the ouput variable does not make it back to access.

[allenjasonbrown@gmail.com]

Thanks for your help. I got it to work. I just had a typo.....uggh.

[pbaldy]

Excellent, glad you got it working.