I have realized that I can't total a calculated control in a sbfrm, it must be a field and queries are the best option. I Have created a query and put in the following field Total Amount: Sum([AccessCost per]*[AccessAmount]) then had an unbound field txtbox on the form that totaled all of the cost. It worked great, but using hte query based sbfrm as the record source does not allow me edit or change info in the subfrm. Any ideas?
Summing Totals
- Thread starter cruzpatas
- Start date
John Big Booty
AWF VIP
- Local time
- Today, 23:09
- Joined
- Aug 29, 2005
- Messages
- 8,262
You could add a check box to your table structure, called Edit, then use a query with EDIT = TRUE or -1 as a criteria to populate a form to allow edits of that record.
Rich
Registered User.
- Local time
- Today, 14:09
- Joined
- Aug 26, 2008
- Messages
- 2,898
Why not?
Because totals operate on fields in the recordset not controls
Rich
Registered User.
- Local time
- Today, 14:09
- Joined
- Aug 26, 2008
- Messages
- 2,898
Since the control is based on a calculation from a recordset the Sum can be calculated via another calculated control
Semantics maybe but this does not constitute totalling the calculated control.
Yes you can achieve the same result by duplicating the calculations used in the calculated control but this is actually working on the recordset.
The important point is that the totals are based on fields in the recordset.
Forms designed by the Wizard will name the form's bound controls the same as the fields but these controls are not actually what is being totalled. This is often misunderstood.
However as the OP discovered, adding a Totals field to the recordset makes it nonupdateable. Hence performing the calculations entirely on the form has an advantage. But with some complex expressions it cannot be easily done.
The other way to overcome the problem of being unable to update a recordset that includes Totals is to write the Totals to a temporary table and then join those records back to the original data as the recordset. Of course the Totals and anything based on them will be static until the temporary table is updated.
Yes you can achieve the same result by duplicating the calculations used in the calculated control but this is actually working on the recordset.
The important point is that the totals are based on fields in the recordset.
Forms designed by the Wizard will name the form's bound controls the same as the fields but these controls are not actually what is being totalled. This is often misunderstood.
However as the OP discovered, adding a Totals field to the recordset makes it nonupdateable. Hence performing the calculations entirely on the form has an advantage. But with some complex expressions it cannot be easily done.
The other way to overcome the problem of being unable to update a recordset that includes Totals is to write the Totals to a temporary table and then join those records back to the original data as the recordset. Of course the Totals and anything based on them will be static until the temporary table is updated.
Last night my wife took over the internet and I worked on it for a couple of hours. I figured it out the hard way. Thank you guys. I wound up creating a dummy subform from a query that is not visible on the form but does the calculations and then sends the info to a field in the Form and then the final calculations are done. Thanks guys
