Problem with Totals in Subform

[MatMac]

Hi.

I have a subform bound to a query. The subform contains one field for each query field, plus an additional field displaying the sum of the values in the other fields.

When I open the subform directly as a datasheet, the totals field is displayed fine.

However, when the subform is opened as a datasheet within its parent form, the totals field remains empty.

Any idea how I can overcome this?

Many thanks. Mat.

 

[hyperpau]

This is really what's gonna happen because the field does not exist in your query but only as an unbound calculated textbox in your form.

To resolve this, add a realtime calculated field in your query. And since the form is bound to the query, you can then add that field as a control to your form, this time it is not unbound but bound bec the field actually exists in your query.

To add a realtime calculted field, just Type the fieldname you desire in an empty column of your query design view, followed by a colon ":" then your calcultation. Something LIke:

MyCalculatedField:  DSum("MyOtherField", "MyTable", "ID=" & ID)

If you go to datasheet view of the query, you will now see the new column with the results you want. Go then to your form design view and replace your existing unbound textbox with the new field from your field list.

Hi.

I have a subform bound to a query. The subform contains one field for each query field, plus an additional field displaying the sum of the values in the other fields.

When I open the subform directly as a datasheet, the totals field is displayed fine.

However, when the subform is opened as a datasheet within its parent form, the totals field remains empty.

Any idea how I can overcome this?

Many thanks. Mat.

 

[MatMac]

Hi. Thanks for the reply, but I still don't understand why the data are visible when looking at the subform directly and not through the parent form.

Technically, its not an unbound field - its bound to a function which is the sum of the other fields.

The reason I have not done it as you suggest (and its not the sum of all values from a column I require, but a series of sums of all values in a row), putting the totals field in the query, is because the query fields themselves are generated from a crosstab, and - quite frankly - I don't know how to put a total in that way.

TRANSFORM Sum(costing.amount) AS SumOfamount
SELECT costing.projID, costing.phaseID, phase.phaseNum, [acYear] & "-" & [acyear]+1 AS [Academic Year]
FROM phase INNER JOIN costing ON phase.phaseID = costing.phaseID
GROUP BY costing.projID, costing.phaseID, phase.phaseNum, [acYear] & "-" & [acyear]+1
ORDER BY [acYear] & "-" & [acyear]+1
PIVOT costing.element In ("Staff","Non-Staff","FTE-Related");

Yes, I could add a second query reading the fields from the crosstab then adding a total, but I really wondered why I cant see the results of the sum the way I have originally done it through my parent form.

 

[Rich]

How is the subform linked to the main form and have you tried changing to a continuous form for the sub instead of datasheet?