Output from stored procedure

How can I use output from a stored procedure in VBA coding?

The VBA coding calls a stored procedure to add a new customer by passing all of the relevant fields except the new customer ID.  The stored Proc has a transaction that determines the next ID and then inserts the customer using that ID before committing.

The ID is returned as a declared output parameter and the return code is set to 0 for success, 4 for failed insert.

I need to
1. determine if the insert was successful from the return code
2. Fill in the form with the new customer ID.

But I cannot find it in the querydef properties.


Private Sub SaveNewCustomer()
' use stored proc to save the newcustomer v9_5_2
' Transaction determines the next Cust Id number and saves before committing
  Dim parm As String
  Dim db As DAO.Database
  Dim qdf As DAO.QueryDef
  Dim qspt As String
  Dim msg As String
  Dim msg2 As String
  Dim rst As Recordset
  Dim ReturnCode As Integer
  
  On Error GoTo ErrHandle
  
' move the NewZealand street to the address field
  If Me.Country = "New Zealand" Then
    Me.Address = Me.NZ_Street
  End If
  
' build the parameters for the stored proc sp_new_customer
  parm = "NULL"                                   ' @ID
  parm = parm & ",'" & Me.FirstName & "'"        ' @FirstName
  parm = parm & ",'" & Me.LastName & "'"         ' @LastName
  parm = parm & "," & SQLdate(Me.DOB)            ' @DOB
  parm = parm & ",'" & Me.ID_Type & "'"          ' @ID_Type
  parm = parm & ",'" & Me.ID_Number & "'"        ' @ID_Number
  parm = parm & ",'" & Me.ID_Issuer & "'"        ' @ID_Issuer
  parm = parm & ",'" & Me.ID_Country & "'"       ' @ID_Country
  parm = parm & ",'" & Me.ID2_Type & "'"         ' @ID2_Type
  parm = parm & ",'" & Me.ID2_Number & "'"       ' @ID2_Number
  parm = parm & ",'" & Me.ID2_Issuer & "'"       ' @ID2_Issuer
  parm = parm & ",'" & Me.ID2_Country & "'"      ' @ID2_Country
  parm = parm & ",'" & Me.Address & "'"          ' @Address
  parm = parm & ",'" & Me.Suburb & "'"           ' @Suburb
  parm = parm & ",'" & Me.PCode & "'"            ' @Pcode
  parm = parm & ",'" & Me.State & "'"            ' @State
  parm = parm & ",'" & Me.Country & "'"          ' @Country
  parm = parm & ",'" & Me.Occupation & "'"       ' @Occupation
  parm = parm & ",'" & Me.Phone & "'"            ' @Phone
  parm = parm & ",'" & Me.EMail & "'"            ' @email
  parm = parm & ",'" & Me.SightedBy & "'"        ' @Sightedby
  parm = parm & ",'" & Me.Comment & "'"          ' @Comment
  parm = parm & ",'" & Me.Creator & "'"          ' @creator
  parm = parm & ",'" & Me.DateCreate & "'"       ' @datecreate
  parm = parm & ",'" & Me.Modifier & "'"         ' @modifier
  parm = parm & ",'" & Me.DateMod & "'"          ' @datemod
  parm = parm & ",'" & Me.Status & "'"           ' @status
  parm = parm & ",'" & Me.StreetNumber & "'"     ' @streetNumber
  parm = parm & ",'" & Me.ID_Doc_1.HyperlinkAddress & "'"     ' @ID_Doc1
  parm = parm & ",'" & Me.ID_Doc_2.HyperlinkAddress & "'"     ' @ID_Doc2
  
  
  Set db = CurrentDb()
  qspt = "DECLARE @RC int" & vbCrLf
  qspt = qspt & "DECLARE @Id int" & vbCrLf
  qspt = qspt & "-- Set parameter values" & vbCrLf
  qspt = qspt & "EXEC @RC = [sp_new_customer]" & parm
  
  
  Set db = CurrentDb()
  Set qdf = db.QueryDefs("qspt_New_customer")
  qdf.SQL = qspt
 
 ' test
   With qdf
      Debug.Print .Name
      Debug.Print "  " & .SQL
   End With
 
  Set qdf = Nothing
  Set db = Nothing

Exit_Sub:
    Exit Sub

End Sub

Open in new window

Nigel Keith-WalkerContractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Have the sp return a recordset with the values you need.
0
Nigel Keith-WalkerContractorAuthor Commented:
Hi Pat
Within the MS access I can see that there is a qspt with the result I need.  But I just cannot find the right commands to make it available to the VBA code.
qspt_new_customer
0
Nigel Keith-WalkerContractorAuthor Commented:
viewing the properties of qdf, there is an object with the name @ID but the value is not available

qdf_@ID
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Nigel Keith-WalkerContractorAuthor Commented:
MSDN explanation
MSDN has an explanation, but I have not been able to work out how to use their explanation.  How embarrassing!
0
Nigel Keith-WalkerContractorAuthor Commented:
I have a workaround.  My attempts to use a recordset failed with this stored procedure.  It may be because the stored procedure has a begin transaction and begin commitment.  But I was able to determine the ID from the parameter.

The pertinent part is:
'  Debug.Print cmd.Parameters("@ID").Value
  ReturnCode = cmd.Parameters("@Return_Value").Value
  ID_new = cmd.Parameters("@ID").Value

The full subroutine is below:

   Private Sub SaveNewCustomer()
' use stored proc to save the newcustomer v9_5_2
' Transaction determines the next Cust Id number and saves before committing

  Dim msg As String
  Dim msg2 As String
  Dim rst As New ADODB.Recordset
  Dim cmd As New ADODB.Command
  Dim ReturnCode As Integer
  Dim t_Creator As String
  Dim t_DateCreate As Date
'  Dim newcust As String
  Dim ID_new As Long
    
  On Error GoTo ErrHandle
  
' move the NewZealand street to the address field
  If Me.Country = "New Zealand" Then
    Me.Address = Me.NZ_Street
  End If
  
' set temp values for creator and date of creation
  t_Creator = [TempVars]![Logon_UserID].[Value]
  t_DateCreate = Now()

  dcnn  ' Check the connection
  
  Set cmd = New ADODB.Command
  Set rst = New ADODB.Recordset
  Set rst.ActiveConnection = gcnn
  rst.CursorLocation = adUseClient
  
  
' build the parameters for the stored proc sp_new_customer
  With cmd
    .ActiveConnection = gcnn
    .CommandText = "sp_new_customer"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
    .Parameters.Append .CreateParameter("@ID", adInteger, adParamReturnValue, , ID_new)
    .Parameters.Append .CreateParameter("@FirstName", adVarChar, adParamInput, 50, Me.FirstName)
    .Parameters.Append .CreateParameter("@LastName", adVarChar, adParamInput, 50, Me.LastName)
    .Parameters.Append .CreateParameter("@DOB", adDate, adParamInput, 50, mmmDate(Me.DOB))
    .Parameters.Append .CreateParameter("@ID_Type", adVarChar, adParamInput, 4, Me.ID_Type)
    .Parameters.Append .CreateParameter("@ID_Number", adVarChar, adParamInput, 20, Me.ID_Number)
    .Parameters.Append .CreateParameter("@ID_Issuer", adVarChar, adParamInput, 50, Me.ID_Issuer)
    .Parameters.Append .CreateParameter("@ID_Country", adVarChar, adParamInput, 50, Me.ID_Country)
    .Parameters.Append .CreateParameter("@ID2_Type", adVarChar, adParamInput, 4, Me.ID2_Type)
    .Parameters.Append .CreateParameter("@ID2_Number", adVarChar, adParamInput, 20, Me.ID2_Number)
    .Parameters.Append .CreateParameter("@ID2_Issuer", adVarChar, adParamInput, 50, Me.ID2_Issuer)
    .Parameters.Append .CreateParameter("@ID2_Country", adVarChar, adParamInput, 50, Me.ID2_Country)
    .Parameters.Append .CreateParameter("@Address", adVarChar, adParamInput, 100, Me.Address)
    .Parameters.Append .CreateParameter("@Suburb", adVarChar, adParamInput, 50, Me.Suburb)
    .Parameters.Append .CreateParameter("@Pcode", adVarChar, adParamInput, 15, Me.PCode)
    .Parameters.Append .CreateParameter("@State", adVarChar, adParamInput, 35, Me.State)
    .Parameters.Append .CreateParameter("@Country", adVarChar, adParamInput, 35, Me.Country)
    .Parameters.Append .CreateParameter("@Occupation", adVarChar, adParamInput, 36, Me.Occupation)
    .Parameters.Append .CreateParameter("@Phone", adVarChar, adParamInput, 20, Me.Phone)
    .Parameters.Append .CreateParameter("@Email", adVarChar, adParamInput, 70, Me.EMail)
    .Parameters.Append .CreateParameter("@SightedBy", adVarChar, adParamInput, 50, Me.SightedBy)
    .Parameters.Append .CreateParameter("@Comment", adVarChar, adParamInput, 36, Me.Comment)
    .Parameters.Append .CreateParameter("@Creator", adVarChar, adParamInput, 50, t_Creator)
    .Parameters.Append .CreateParameter("@DateCreate", adDate, adParamInput, 50, t_DateCreate)
    .Parameters.Append .CreateParameter("@Modifier", adVarChar, adParamInput, 50, Null)
    .Parameters.Append .CreateParameter("@DateMod", adDate, adParamInput, 50, "01-Jan-1980")
    .Parameters.Append .CreateParameter("@Status", adVarChar, adParamInput, 20, Me.Status)
    .Parameters.Append .CreateParameter("@StreetNumber", adVarChar, adParamInput, 10, Me.StreetNumber)
    .Parameters.Append .CreateParameter("@ID_Doc_1", adVarChar, adParamInput, 255, Me.ID_Doc_1.HyperlinkAddress)
    .Parameters.Append .CreateParameter("@ID_Doc_2", adVarChar, adParamInput, 255, Me.ID_Doc_2.HyperlinkAddress)
  End With
    
  Set rst = cmd.Execute

'  Debug.Print cmd.Parameters("@ID").Value
  ReturnCode = cmd.Parameters("@Return_Value").Value
  ID_new = cmd.Parameters("@ID").Value
  
  Me.ID = ID_new
  Me.Creator = t_Creator
  Me.DateCreate = t_DateCreate
  OptionAction = 3
  FormatCustForm Me.Form, "View"
  
  msg = "New Customer saved as:" & vbCrLf & ID_new & " : " & FirstName & " " & LastName
  MsgBox msg, vbInformation, "Form Customer"
  
Exit_Sub:
    Exit Sub

ErrHandle:
    msg = "Problem saving new customer" & vbCrLf & msg2 & vbCrLf & Err.Description
    MsgBox msg, vbExclamation, "Form Customer"
End Sub 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Nigel, is this issue solved?
0
Nigel Keith-WalkerContractorAuthor Commented:
It works and there were no other satisfactory responses
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.