Returning a value from a sql SP and using in vba to set a form value

[mendesj1]

want to take return currentdelta which is one field and display the value in a from

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Dim SQLServer As String
Dim Catalog As String
Dim SQLString As String
Dim SQLInvNo As String
Dim comboboxfut As Integer

comboboxfut = Me!Combo58


SQLServer = "Gretzky\RNYSQLAccess"
Catalog = "prototypedatabasecafe2010"
SQLString = "Exec sp_CurrentDelta " & comboboxfut





'Create a new ADO Connection object
Set cn = New ADODB.Connection
'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = SQLServer
.Properties("Integrated Security").Value = "SSPI"
.Properties("Initial Catalog").Value = Catalog
.Open
End With
'Create an instance of the ADO Recordset class, and
'set its properties


Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = SQLString
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With


_________________________

SQL



USE [PrototypeDatabaseCafe2010]
GO
/****** Object: StoredProcedure [dbo].[sp_CurrentDelta] Script Date: 11/28/2011 12:43:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_CurrentDelta]
@comboboxfut int ,@CurrentDeltaM float
As
Begin


SET @CurrentDeltaM = (SELECT SUM(Subwt)/37500 AS DeltaValue from
(SELECT OrderDetails.NOBAGS * Inventory.NET_WT * OrderDetails.FixationBasisOrderDetails AS Subfixbasis,
OrderDetails.NOBAGS * Inventory.NET_WT AS Subwt, OrderDetails.NOBAGS, OrderDetails.PRICE, OrderDetails.FixationBasisOrderDetails,
OrderDetails.Dcheckbox, OrderDetails.Scheckbox, OrderDetails.Gcheckbox, OrderDetails.Zcheckbox, OrderDetails.PROCcheckbox, Inventory.NET_WT,
FuturesCompositeValues.FUTCOMPID, FuturesCompositeValues.FUTCOMPname
FROM OrderDetails INNER JOIN
Inventory ON OrderDetails.[REFF NUMBER] = Inventory.[REFF NUMBER] LEFT OUTER JOIN
FuturesCompositeValues ON OrderDetails.FutcompID = FuturesCompositeValues.FUTCOMPID INNER JOIN
Orders ON OrderDetails.ORDERID = Orders.ORDERID
WHERE (OrderDetails.Dcheckbox = 1) AND (OrderDetails.PROCcheckbox IS NULL OR
OrderDetails.PROCcheckbox = 0) and orderdetails.futcompid = @comboboxfut
) as t
)
RETURN @CurrentDeltaM

end

 

[bparkinson]

Dim p1 As New ADODB.Parameter
Dim sSQL As String
Dim rs As New ADODB.Recordset
Dim rs1 As DAO.Recordset

On Error Resume Next

Set cmd = New ADODB.Command

InitConnection

cmd.ActiveConnection = cn

p1.Direction = adParamInput
p1.Type = adInteger
p1.value = iAthleteID


cmd.CommandType = adCmdStoredProc
cmd.CommandText = "spGetTrainingDay"
cmd.Parameters.Append p1

Set rs = cmd.Execute

Then just display rs!ColumnName.