Subtotal in form

  • Thread starter Thread starter L. Woodhouse
  • Start date Start date
L

L. Woodhouse

I have a form called ORders.
On it, there is a subform for the details of the order
called OrderDetails.
As I enter lines into orderdetails, I want it to calculate
the Subtotal. How do I do this?
The Subtotal would be on the Order form; not the subform.
 
put an unbound textbox called Subtotal on the main form.
put the following in the subform's Form_AfterUpdate event:

***BEGIN AIR CODE***

Dim dblTotal As Double

dblTotal = DSum("Cost","OrderDetails","OrderID = Forms!
Orders!OrderID")

Forms!Orders!Subtotal = dblTotal

***END AIR CODE***

change the table and field names, of course. every time
you add or change an order detail record, the value of the
unbound control on the main form will change. to also show
changes after a detail record is deleted, add the above
code to the form's AfterDelConfirm event.

hth
 
When I enter that code, I get an error

Run-time error '2001'
You canceled the previous operation.


Is there a way to do this in the subtotal field itself?
 
hmmm....suggest you step thru the code to see exactly
where it's erring out. if the DSum function isn't working,
first guess is the system isn't picking up the value in
the OrderID field.
you could run the same code in the subtotal control's
AfterUpdate event instead of the form's AfterUpdate event,
but when you're entering a new record the DSum function
won't "see" the record until it's saved to the table, so
the control's value won't be added to the subtotal. you'd
have to write a little tricker code to overcome that issue.
you could fiddle with it a bit, see if you can get it to
work....or maybe someone else will post a better solution
for you.

good luck
 
put a invisible unbound text box on detail form (form header or footer not
page header, footer and detail)
set control source of the text box like;
=sum(amount or what ever you want)

then write a code in after update event of sub form like;

me.parent.form.maintotal.requery

also maintotal text box control source must be like;
=me.subform.form.subtotal


Atilla
 
Back
Top