Totals on Forms

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Here is the situation:
Since Access will not group by a field without sorting
(ascending or descending) by that field, it has become
necessary to add a "Sum" field.
Tables: Base
: Base Detail
Forms : Base
: Base Subform

Main form data example:
Project: XXXXX
Desc: Notes Supplies
Subform data example:
Y1 Y2 Y3 Y4 Y5
Paper 10 15 10 5
Pen 1 3 5 7 30
Ink 30 50 25 30 100

Currently on the subform there is a field that is not
visible that shows:
text11: 40 text12: 53 and so on
I have already set up "sum" fields in the "Base" table.
What is needed is for on the main part of the form, for
the sumy1 field to be filled in with the value of text11.
However without having its control source changed from the
table, since I need the data to flow through to the Base
table for grouping and sorting puposes.
I was thinking of an after update event to be used on each
Y1 - Y5 in the subform and tried the following:

Private Sub FY04_AfterUpdate()

[Paint].[SumY1] = ([Text11]![Paint Subform])

End Sub

However end up with a Run-Time Error '13'
Type MisMatch

Thank you in advance for any and all help,

Paul
 
Subform data example:
Y1 Y2 Y3 Y4 Y5
Paper 10 15 10 5
Pen 1 3 5 7 30
Ink 30 50 25 30 100

Currently on the subform there is a field that is not
visible that shows:
text11: 40 text12: 53 and so on
Click to expand...

What are the Control Sources of these? I see that 40 is the sum of all
five Paper controls - but what's 53?
I have already set up "sum" fields in the "Base" table.
Click to expand...

They SHOULD NOT EXIST in any table. Totals can and should be
calculated ON THE FLY. There is very rarely any reason to store a
value which can be derived from existing data, whether by a Totals
query or directly on a Form or Report.
What is needed is for on the main part of the form, for
the sumy1 field to be filled in with the value of text11.
However without having its control source changed from the
table, since I need the data to flow through to the Base
table for grouping and sorting puposes.
Click to expand...

No, you don't. You can group by or sort by a calculated field in a
Totals query - and you'll know that it's accurate.
I was thinking of an after update event to be used on each
Y1 - Y5 in the subform and tried the following:

Private Sub FY04_AfterUpdate()

[Paint].[SumY1] = ([Text11]![Paint Subform])

End Sub

However end up with a Run-Time Error '13'
Type MisMatch
Click to expand...

If you REALLY, REALLY want to store redundant, unreliable, possibly
erroneous derived data... put a bound textbox on the Form txtSumY1; it
can be invisible if you want. Put the data into this textbox rather
than attempting to put it directly into the table.
 
Back
Top