Did you know you could simply output the desired result directly from the SP without declaring and using the Output clause?
You could also adjust the SP to return a meaningful result directly without you needing to open the recordset and process it.
I only normally process one command through a pass-though query, and I use a generic routine to accept the T-SQL and a flag to indicate if it returns records or simply need to run an action process.
If you set up a pass through query called qpt_Generic you can then do something like
Code:
Public Sub sSendToPT(strQuery As String, bRetRecs As Boolean)
Dim db As DAO.Database
Dim qDEF As QueryDef
Set db = CurrentDb()
Set qDEF = db.QueryDefs("qPT_Generic")
qDEF.Connect = db.TableDefs("ReplacethisWithALinkedTablename").Connect
qDEF.SQL = strQuery
qDEF.ReturnsRecords = bRetRecs
If Not bRetRecs Then ' We Want to execute an INSERT, UPDATE etc. type process
db.Execute "qPT_Generic", dbSeeChanges
Else
qDEF.Close
End If
Set qDEF = Nothing
Set db = Nothing
Then to use it (assuming your SP returns the ErrorSwitch as it's output)
Code:
strSQL = " EXEC dbo.uspNewSalesOrderTemplateItems " & Forms![GlobalVar]![SPExecErrorVar] & ", '" & prmSOType & "', " & prmTemplateID & ", " prmSalesOrderID & ", " &prmCustomerID & ", " & prmPriceLevel
Debug.print strSQL
sSendToPT strSQL, True
tmpReturnValue = DLookup("ErrorSwitch", "qpt_Generic")