set ADP form text to value of an SQL user function 1

[bradmaunsell]

This should be simple but I cannot figure it out.

I want to set a form text box to the result of an SQL 2005 Express user function.

The function works and returns a value when I execute it from the QUERIES screen in my ADP.

Access Data Project
Access 2003
SQL 2005 Express


For example, behind my form
Forms!frmRating!StdPrem = $5,000
Forms!frmRating!StdPrem = [dbo].[sf_getStdPremium_R2] @Rev, @FeeOnly, etc



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[sf_getStdPremium_R2]
(
@Rev as dec(7,0),
@FeeOnly as dec(8,4),
@Multi as dec(8,4),
@Claims as dec(8,4),
@SchCred as dec(8,4),
@Affinity as dec(8,4)
)
RETURNS decimal (8,0)
AS
BEGIN
DECLARE @dblTotalDiscAndSurcharges as dec(8,4)
DECLARE @dblBasePremium as dec(8,4)
DECLARE @dblNetPremium as dec(8,4)
DECLARE @intMinMarkup As int
If (@FeeOnly) Is Null
Begin
set @FeeOnly = 0
end
If (@Multi) Is Null
Begin
set @Multi = 0
end
If (@Claims)Is Null
Begin
set @Claims = 0
end
If (@Affinity) Is Null
Begin
set @Affinity = 0
end

set @dblBasePremium = ((@Rev * 0.00812) + 1650)
If @dblBasePremium < 2250
Begin
set @dblBasePremium = 2250
end
set @dblTotalDiscAndSurcharges = @FeeOnly + @Multi - @Claims + @SchCred + @Affinity

set @dblNetPremium = @dblBasePremium - ((@dblBasePremium) * (@dblTotalDiscAndSurcharges))

If @dblNetPremium < 2250
Begin
set @intMinMarkup = 2250 - @dblNetPremium
set @dblNetPremium = 2250
end
----Else If
-----set @intMinMarkup = 0

RETURN @dblNetPremium

END

 

[GrahamHills]

try this...

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Set rs = CurrentProject.Connection.Execute("SELECT dbo.sf_getStdPremium_R2(2, 0, 2, 2, 2, 2)")

Me.txtFunction = rs(0)

 

[GrahamHills]

Do not need following line:-

Set rs = New ADODB.Recordset