Calculated Controls

[Dan McClelland]

In Access 97, it was easy to put a calculated control in a
form's footer and get a sum of data from a field in the
Detail section. For example, the calculated control's
controlsource would be
=Sum([txtMyValue])

In an Access XP project bound to SQL Server data, it
doesn't appear to work. The calculated control simply
returns
#Error

This happens whether I use a table, SQL statement, stored
proc or table-based user defined function. Is there a
workaround? Having totals at the bottom of my form is
critical to the users.

 

[Rick Brandt]

In Access 97, it was easy to put a calculated control in a
form's footer and get a sum of data from a field in the
Detail section. For example, the calculated control's
controlsource would be
=Sum([txtMyValue])

In an Access XP project bound to SQL Server data, it
doesn't appear to work. The calculated control simply
returns
#Error

This happens whether I use a table, SQL statement, stored
proc or table-based user defined function. Is there a
workaround? Having totals at the bottom of my form is
critical to the users.

It might be because all of the records required to do the aggregation
haven't yet been retrieved from the server. By default, the mere act of
opening a form will not force all records to be retrieved. Normally this
is a good thing.

You might have to do a move-to-last and then back when the form is opened.
Could be a real performance bottle-neck though.

 

[Ted]

I know that you can get an error if the object name is
the same as the field that you are trying to sum,
although I assume that this isn't likely in your case.

If it is a problem with the fact that not all records are
being retrieved, you could try adding a subform bound to
a summary query, or using DSum() functions. I think that
the query would likely perform better, but I'm not sure.

-Ted