Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Auto Totalling Field on subform in Access 2007

Status
Not open for further replies.

Sech

Programmer
Jul 5, 2002
137
GB
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?
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top