Calculated fields on forms 1

[ednz]

I want to be able to edit records on a subform, and have a running total on each record updated according to changes I make.

When I add calculated fields to the underlying query, it becomes non-updatable.

For a simple example, keep a table of amounts in date order, and show with each item the running balance, updating the total as amounts change.

I could use separate queries for editing and for display of results, but then I'd have trouble displaying each total beside the record to which it relates.

I'm using Access97 , but I observe the same problem using Access2000.

 

[Steve101]

A couple of points here:

(a) You say When I add calculated fields to the underlying query, it becomes non-updatable. This should not be the case. For example:
[tt]
SELECT Num1, Num2, Num1+Num2 AS YourTotal
FROM tblYourTable;
[/tt]
still allows fields Num1 and Num2 to be updated in the query (directly or through a form). Thus for horisontal totalling, this technique is legitimate.

(b) For vertical totalling in a subform, add a Form Header / Footer to the subform, and in the footer part, add an unbound control for each field you want to total on the subform; Lets assume that you were using the query provided above as the recordsource to the subform, and you wanted to vertically total the Num1 and Num2 columns. You would create two unbound controls in the footer section, name them SumSum1 and SumNum2 respectively, and set their respective ControlSource properties to:
[tt]
=Sum(Num1)
and
=Sum(Num2)
[/tt]
respectively. Make sure you include the equal signs, and dont include any quotes.

(c) If you are displaying the subform in Continuous Form mode, then the Report Footer (and the above total fields) will be visible as part of the subform display in the main form. If you are displaying the subform in datasheet view, then the report footer will not be visible (shock horror). Fear not though, the Access engine has still done its job, and the computed SumNum1 and SumNum2 control values are still available. Simply place some additional controls on the MAIN form, and set them to the appropriate subform controls; eg. if the subform control is called YourSubForm, then, set the respective main form controlsource properties to:
[tt]
=YourSubForm!SumNum1
and
=YourSubForm!SumNum2
[/tt]
The values will propogate back to the main form, and will be fully dynamic. As you add/change delete records in the subform, the subtotals will remain accurate.

(d) For the subtotal controls discussed above, you might want to set the Enabled and Locked properties appropriately so that the user cannot attempt to move to or change them.

Hope this Helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[ednz]

Thanks for your response Steve101.

The case I am interested is in having the results of partial vertical totalling appearing on each record.

I am interested in having a running total.

Example: Table items (item autonmumber, name text, amount number)

Query:SELECT items.item, items.name, items.amount, (select sum(a.amount) from items a where a.item <= items.item) AS total
FROM items;

This produces a nice query complete with running total, but I cannot alter the values of amount because query has become not updatable.

How can I get the same result with with editable inputs?

Thanks for the help?

 

[Steve101]

OK. I understand. Try the following:
[tt]
SELECT item, name, amount,
DSum(&quot;Amount&quot;, &quot;Items&quot;, &quot;Item <= &quot; & Item) as Total
FROM items;
[/tt]
It will work, as it does not include a subquery. Instead it uses the DSum (domain sum) function to compute the total field. Note the following:

(a) I cant vouch for the performance on a large table. Make sure that the Item field is indexed.

(b) If you make changes to the data in the query, the computed field will not automatically recompute. This makes sense if you think about it. You need to refresh the query, using the F9 function key, or do this through code if the query is being shown through a form/subform.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[ednz]

Steve101

Excellent. That's the answer I needed.

Many thanks for solving my problem so quickly.