ADO - Get Parameter Value from SQL within VBA

I am not really sure what you are wanting, and what works in your code.  It would be best in your question if you identify the real object names.  In addition is seems that your VBA is accomplishing some the goals you are trying to accomplish.

Can you clarify your question a bit and indicate at what point you are having difficulty?

I want to retrieve the Newly created EventID from SQL Query - so that I may update the remainig data within the SQL query.

Step 1:   I need to first create a record in SQL,
Step 2:   Capture the newly created EventID ((Record ID)
Step3:    Then run Update Query to update the ErrorMessage from Access to post to the Sql
              Query for the newly created EventID.

I need to create a new record in the [ISCenter_Monitor].[ISCenter_EventLog] with the Event Following data see below - Sql will create a new record - it is that EvnetID I need to capture inorder to update the remaining data to [ISCenter_Monitor].[ISCenter_EventLog] (where the data is null.  see  '+++++++ portion of code.  
See lines 51-53 -

the sRC value does not return an actual value instead it returns 0


Here is my latest attempt at the VBA Code:  

'---------------------------------------------------------------------------------------
' Procedure : ParamSPT
' Author    : 49kxs
' Date      : 8/22/2013
' Purpose   : Updates the Error Log for ISCenter - (SQL generated)
'---------------------------------------------------------------------------------------
'
Function ParamSPT(ReportName As String, MODNAME As String, RecCt As Long, ProcName As String, _
                    sErr As String, nResults As Boolean)
   
Dim sConn As ADODB.Connection
Dim sCmd As ADODB.Command
Dim sRC As ADODB.Parameter
Dim lRetVal As Long
Dim strConn As String
   
   On Error GoTo ParamSPT_Error
  
    Set sConn = New ADODB.Connection
    strConn = _
        "Driver=SQL Server;Server=AQL02; Database=TRACI_ANALYTICS;TrustedConnection = true;"

    sConn.ConnectionString = strConn
    
    sConn.Open strConn

    'SQL variables with database's counterparts
    
    '@EventName = ReportName
    '@ModuleName = ModName
    '@ProcedureName = ProcName
    '@ErrorMessage =Err.Number & ". " & Err.Description
    '@AffectedRows = RecCt
    '@StepSucceeded = 0 or 1 (True/False)

    sConn.Execute "EXEC [ISCenter_Monitor].[usp_log_ISCenter_Event]" & _
                    " @EventName='" & ReportName & "'," & _
                    " @ModuleName='" & MODNAME & "'," & _
                    " @ProcedureName='" & ProcName & "'"

 
 '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Set sCmd = New ADODB.Command
    sCmd.ActiveConnection = sConn
    sCmd.CommandType = adCmdStoredProc
    sCmd.CommandText = "[ISCenter_Monitor].[usp_log_ISCenter_Event]" & _
                    " @EventName='" & ReportName & "'," & _
                    " @ModuleName='" & MODNAME & "'," & _
                    " @ProcedureName='" & ProcName & "'"
    Debug.Print sCmd.CommandText
    
    Set sRC = sCmd.CreateParameter("EventID", adInteger, adParamReturnValue)
    sCmd.Parameters.Append sRC
    sCmd.Execute
    
    'Now check the return value of the procedure
    lRetVal = sCmd.Parameters("EventID")
    Debug.Print lRetVal
  
    sConn.Execute "UPDATE  [ISCenter_Monitor].[ISCenter_EventLog]" & _
                    " Set" & _
                        " @ErrorMessage='" & sErr & "'," & _
                        " @AffectedRows ='" & RecCt & "'," & _
                        " @StepSucceeded='" & nResults & "'" & _
                    " WHERE 'EventID ='" & lRetVal & "'"
    
    'sConn.Execute "EXEC [ISCenter_Monitor].[usp_log_ISCenter_Event]" & _
                    " @ErrorMessage='" & sErr & "'," & _
                    " @StepSucceeded='" & nResults & "'" & _
                    " WHERE 'EventID ='" & lRetVal & "'"


   On Error GoTo 0
   Exit Function

ParamSPT_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ParamSPT of Module basErrorEventLog"
End Function

Select allOpen in new window

I am still getting the following error message:


Debugged Values:
{ call [ISCenter_Monitor].[usp_log_ISCenter_Event] @EventName='Invoices Data Load', @ModuleName='Report Functions', @ProcedureName='InvoicesLoad' }

Thanks for your input.