Link to home
Avatar of Nigel Keith-Walker
Nigel Keith-WalkerFlag 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

Avatar of PatHartman
PatHartman
Flag of United States of America image

Have the sp return a recordset with the values you need.
Avatar of 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.
User generated image
viewing the properties of qdf, there is an object with the name @ID but the value is not available

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

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Nigel, is this issue solved?
It works and there were no other satisfactory responses