Subtotal in Datasheet Form

  • Thread starter Thread starter Tim Martin
  • Start date Start date
T

Tim Martin

Is it possible to create a subtotal row in a datasheet
form? For example I have lines 1-6 that I need to total
on Line 7 and store the values on all 7 lines. i.e. lines
1-6 are expenses and line 7 is a total of those expenses.

Thanks for any help.
 
Tim Martin said:
Is it possible to create a subtotal row in a datasheet
form? For example I have lines 1-6 that I need to total
on Line 7 and store the values on all 7 lines. i.e. lines
1-6 are expenses and line 7 is a total of those expenses.
Click to expand...

Not in any easy way; at least, not that I can think of. Could you use
a continuous form instead, and just format it so that it looks like a
datasheet? Then you could put your subtotal line in the form footer.

I'm not sure what you mean when you say "I need to total on Line 7 and
store the values on all 7 lines". It is generally considered a bad idea
to store both calculated values such as totals and the detail values
from which they are calculated, except in special circumstances. It's
almost always faster to calculate the totals when you need them, rather
than read them from disk, and storing both the basis of a calculation
and its result always runs the risk of allowing an inconsistency in the
data. For example, you calculate and store the total, and then sometime
afterward someone modifies the detail without updating the total.
 
Dirk Goldgar said:
Not in any easy way; at least, not that I can think of. Could you use
a continuous form instead, and just format it so that it looks like a
datasheet? Then you could put your subtotal line in the form footer.

I'm not sure what you mean when you say "I need to total on Line 7 and
store the values on all 7 lines". It is generally considered a bad idea
to store both calculated values such as totals and the detail values
from which they are calculated, except in special circumstances. It's
almost always faster to calculate the totals when you need them, rather
than read them from disk, and storing both the basis of a calculation
and its result always runs the risk of allowing an inconsistency in the
data. For example, you calculate and store the total, and then sometime
afterward someone modifies the detail without updating the total.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thank you for the continuous form idea.
Click to expand...

I know it's a bad idea to store totals. The problem is the table is being
exported to Excel in a specific format to utilize DSum functions in Excel for
a variety of exhibits. Another problem is that there is actually 57 lines and
about 4 or 5 subtotals that have to be calculated. I am rebuilding an
application that will not convert to XP because it was based on an old VB
front end (I can't even find a reader for it) and some of the .dlls will not
convert. It was a great tool and I have most of it done with the exception of
this piece. The user needs to be able to see the subtotals when inputting
information into the form.

I will play with the continuous form idea and see if I can get it to work
that way.

Thanks again
 
Back
Top