Total Row in datasheet view has problems if using ADP / SQL

[dantcv]

When using the "Total Row" in datasheet view (of a table or a view) of an
Access 2007 PROJECT (.adp with SQL Server as the back-end database), the
Total Row will not update after a filter is applied to the datasheet.

Moreover, if I press "Refresh" or "Refresh All" after applying the Total
Row, the values in the Total Row disappear, and the only way I can get them
back is to close and re-open the datasheet. This also only happens with .adp
/ SQL.

Is this a bug in Access 2007? We really need the ability for the Total Row
to re-calculate when a filter is applied; otherwise, it's not of much use.

 

[Jeff Boyce]

It sounds like you expect Access to work like a spreadsheet.

Would what you're doing work better in a spreadsheet?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dantcv]

First of all, the data is already in a SQL Server Database, so it would be
more work to export/import the data into Excel or another spreadsheet.

Moreover, the "Total Row" does provide all of the mathematical/computational
power that I need to analyze the data... if it were to function properly
(like it does in regular Access databases)

Another problem with copying all of the data to a spreadsheet is then I lose
the ability to easily filter by records and all of the other advanced
functionality that Microsoft Access provides in datasheet view.

As an example, it seems to me that it shouldn't matter what the data source
is (Access table vs. SQL table)... if you sum a field in two records (e.g.,
12 + 15 = 27) but then filter for only records where the field value is 12,
the sum of 12 (+ nothing) should not still display as 27!

Also, don't forget the second part of my original post: when you refresh the
records (in ADP mode), the Total Row values just disappear (filter or not)!

 

[Jeff Boyce]

No first hand experience with the ADP variant. In addition to your post
here, have you tried searching on-line?

Are you working in a form or a query? I can envision a way to get those to
'refresh' after filtering...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[dantcv]

Yes, I've Googled and I've Binged... seemingly endlessly, without any success.

This haapens in tables, form datasheets, and views (ADP versions of queries)

I can make an actual form display the correct values, but this requires
up-front programming and does not allow for new filters dynamically. We'd
like to just get the values automatically from datasheet view.

Thanks again,
Dan