Form Problem w/ Sum Function in ADP Project

[Cheryl3D]

Hello:

I need help with an ADP Project. First, I'm working on an MS ADP project with a SQL Server 2000 database backend. I have a subform and a form that both use the SUM function, =Sum([field name]) in a text box control on the forms.

The Record Source for both forms (which are bound) is a view called 'qryAnnex_View'. The SQL for 'qryAnnex_View' is as follows:

SELECT DISTINCT dbo.TabSupplier.[ID #], dbo.TabSupplier.SRAN, dbo.TabSupplier.JULIAN, dbo.TabAnnex.[ID #] AS Expr1, dbo.TabSupplier.[ID #] AS ID, dbo.TabSupplier.Agreement, dbo.TabSupplier.Supplier, dbo.TabSupplier.Receiver, dbo.TabSupplier.WorkyearCost, dbo.TabAnnex.Categories, dbo.TabAnnex.SubCategory, dbo.TabAnnex.Support, dbo.TabAnnex.RDescription, dbo.TabAnnex.RUnits, dbo.TabAnnex.RUnitCost, dbo.TabAnnex.NRManpower, dbo.TabAnnex.NRDescription, dbo.TabAnnex.NRUnits, dbo.TabAnnex.NRUnitCost,
COALESCE (dbo.TabAnnex.NRManpower * dbo.TabSupplier.WorkyearCost, 0) AS NRWorkyearCost,
COALESCE (dbo.TabAnnex.RUnits * dbo.TabAnnex.RUnitCost, 0) AS RCost,
COALESCE (dbo.TabAnnex.NRUnits * dbo.TabAnnex.NRUnitCost, 0)AS NRCost, dbo.TabAnnex.Document, dbo.TabAnnex.Billed, dbo.TabAnnex.Coll, dbo.TabSupplier.Annex_Complete, dbo.TabSupplier.Status
FROM dbo.TabSupplier LEFT OUTER JOIN dbo.TabAnnex ON dbo.TabSupplier.[ID #] = dbo.TabAnnex.[ID #]


As you can see, qryAnnex_View contains a number of calculated fields, particularly called RCost, NRWorkyearCost, NRCost,and NRManpower (which is not calculated).

Primarily, the fields are from the table called 'TabAnnex'or derived from a combination of 'TabAnnex' and 'TabSupplier' fields. See table structures below:

TabAnnex Table:
Column Name Data Type Length
s_GUID uniqueidentifier 16
[ID #] nvarchar 50
Receiver nvarchar 50
Categories nvarchar 50
SubCategory nvarchar 50
Support nvarchar 50
RDescription nvarchar 50
RUnits real 4
RUnitCost money 8
NRManpower real 4
NRDescription nvarchar 50
NRUnits real 4
NRUnitCost money 8
Document nvarchar 50
Billed money 8
Coll money 8


TabSupplier Table:
Column Name Data Type Length
s_GUID uniqueidentifier 16
[ID #] nvarchar 6
SRAN nvarchar 8
JULIAN nvarchar 6
Receiver nvarchar 100
Supplier nvarchar 100
OBANOAC nvarchar 8
Receiver_Name nvarchar 100
Receiver_Phone nvarchar 50
Receive_Email nvarchar 50
Location nvarchar 50
ExeComptroller bit 1
Agreement nvarchar 5
Department nvarchar 5
SR nvarchar 5
Officers int 4
Enlisted int 4
Civilians int 4
Contractors int 4
[Analyst POC] nvarchar 50
Reimbursable bit 1
Reimburse money 8
NONrbs money 8
Reimb_Positions int 4
Reimb_Notes nvarchar 250
SAM_POC nvarchar 50
Status nvarchar 20
Revd_Coord smalldatetime 4
Sent_to nvarchar 20
Date_Sent_for_Coord smalldatetime 4
Suspense_for_Coord smalldatetime 4
Coord_Complete smalldatetime 4
Recvd_for_Annex smalldatetime 4
Suspense_for_Annex smalldatetime 4
Annex_Complete smalldatetime 4
Recvd_for_Sign smalldatetime 4
Sig_Complete smalldatetime 4
Link nvarchar 255
Notes nvarchar 255
[Pending Status] bit 1
[Original_ISA_ Start_ Date] smalldatetime 4
TriAnnual_Complete smalldatetime 4
Annual_Review_Complete smalldatetime 4
Terminated_Date smalldatetime 4
WorkyearCost money 8
[Host Base] nvarchar 50
Host_ID nvarchar 1
[Office Symbol] nvarchar 50
r money 8
nr money 8

The data type for most of the fields used in the calculations for RCost etc, are either money or real.

My problem is that when I try to use Microsoft Access' built-in SUM function in the text box control source like so:

=Sum([RCost])

or

=Sum([NRWorkyearCost] + [NRCost])

or

=Sum([NRManpower])

They all produce an #Error in the derived fields on the form at runtime.

What alternatives do I have to get these calculating and working on the form at runtime?

If one solution is to let SQL Server do the work instead of the control source on the form by using a user defined function (to calculate the sums), then how do I do it?

If the correct alternative is to use a user-defined function in SQL Server, then how do I get the view 'qryAnnex_View' to call my user-defined function?

Please help. Any suggestions would be greatly appreciated.

Thanks,
Cheryl3D

 

[cani]

I had a similar problem with field summmation which I resolved with user-defined functions. In most cases, I used in-line UDFs to return a recordset, and tested to see whether there was a recordcount. If so, I moved to the first record (which would be the summation, and assigned the value to the field). If there wasn't a record count then I assigned the field a null value.

I call the functions immediately after assigning the record source to the form (if a bound form then at the form's OnOpen or OnLoad event properties) and (if a record source is present) at the form's OnCurrent event property to ensure that field values change when the record set changes.

I'm somewhat new at SQL server so there could be a more efficient way.

Below is an example:

Function AssignValueToField()

'Declare variables
Dim rst as adodb.recordset, [parameter variables]

'assign values to variables
[parameter variables]=[fields, etc.,assigning values to parameter variables used in function]
set rst = new adodb.recordset

'open recordset
rst.open "select * from functionnamethatsums(" & [parameter variables] & &quot", currentprojection.connection,adOpenStatic, adLockReadOnly

'Or whatever but since a calculated field you probably wish to choose the most efficient cursor and lock types


'assign value to field on form
If rst.recordcount then
rst.movefirst
[Field to receive summation] = rst![fieldname in function containing summation]

else
[Field to receive summation] = null
end if

set rst = nothing

end function


When the recordsource is assigned (OnOpen if bound form) and OnCurrent event properties

Call AssignValueToField