Avatar of Nigel Keith-Walker
Nigel Keith-Walker
Flag for Australia asked on

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

Microsoft AccessMicrosoft SQL ServerVBA* CodingSQL

Avatar of undefined
Last Comment
Nigel Keith-Walker

8/22/2022 - Mon
PatHartman

Have the sp return a recordset with the values you need.
Nigel Keith-Walker

ASKER
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
Nigel Keith-Walker

ASKER
viewing the properties of qdf, there is an object with the name @ID but the value is not available

qdf_@ID
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Nigel Keith-Walker

ASKER
MSDN explanation
MSDN has an explanation, but I have not been able to work out how to use their explanation.  How embarrassing!
ASKER CERTIFIED SOLUTION
Nigel Keith-Walker

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Vitor Montalvão

Nigel, is this issue solved?
Nigel Keith-Walker

ASKER
It works and there were no other satisfactory responses
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.