Auto Totalling Field on subform in Access 2007

[Sech]

Ok this is a weird one. I recently got Windows Vista along with Access 2007. I am making a new database which deals with item sales. The items are listed in a subform each with their own price. Underneath the subform I have a totals text box. I want this to automatically update to show the price total, so as the user changes prices or adds new items it updates as necessary. How I managed to do this before in Access 2003 was to have a form footer in the subform, stick a text box in there with the control source =Sum([Price]). This would automatically total up all the prices. Then I would set the control source of the text box on the main form to =[subItems].[Form].[Controls]("Price"). However in Access 2007, the total in the form footer of the subform doesn't seem to work right, it just displays #Error. If I import a screen from an older database into the new one which has this function on, it works fine until you go and edit it, and then it displays #Error as well.

Does anyone know how the hell to get around this? Do I need to change something to get this to work, or does anyone know any other method, apart from having to set up queries to calculate the totals every time a change is made?

 

[avolkert]

You might try doing this programatically ... the subform totals (i.e. =sum[MyField]) only work on straight data ... as far as I know.

You can't, for example, have a field with a calculation in it and then total that field in the subform.

Example: You can total UNITS_ORDERED, but you can't total a field which is calculated such as =[PRICE]*[UNITS] ... even if you give it a special name such as TOTAL_CHARGE or something. Once the footer runs into anything, it errors out everything.

If the data in the subform is stored in a table, just try DSUM ... attached to a subform's AFTER_UPDATE property ???

Me.MY_SUBFORM_SUBTOTAL = _
dsum("[TOTAL_THIS_FIELD]","FROM_THIS_TABLE")

I've just found that the =sum([FORM_FIELD]) is too problematic to use ... once you do anything remotely neat with it, it poses problems.

By the way: Vista and Access 2007 sounds like an unholy union if you ask me ! ;-P



Alan J. Volkert
Fleet Services
GE Commercial Finance Capital Solutions
(World's longest company title)
Eden Prairie, MN