Nigel Keith-Walker
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.
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
Have the sp return a recordset with the values you need.
ASKER
ASKER
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nigel, is this issue solved?
ASKER
It works and there were no other satisfactory responses