How to automatically save the running total in subform in the pare

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to automatically save a running total in subform into a record in its
parent form without the user pressing an update button/code.

I have code for the update button that updates the parent record just fine.
me.Total =nz(POItemSubform.Form!SumofCost)

When I use the same code in the onExit of the subform or try to use the
..parent code in the subform it doesn't udpate correctly. It seems to be the
previous or old value.

I found if I wait some period of time I can get it to work by reentering and
exitiing the subform.
 
I need to automatically save a running total in subform into a record in its
parent form without the user pressing an update button/code.

I have code for the update button that updates the parent record just fine.
me.Total =nz(POItemSubform.Form!SumofCost)

When I use the same code in the onExit of the subform or try to use the
.parent code in the subform it doesn't udpate correctly. It seems to be the
previous or old value.

I found if I wait some period of time I can get it to work by reentering and
exitiing the subform.
Click to expand...

In general, it's not a good idea to store calculated data in a table.
Why do you need to do this?
 
Let me see if I can explain this without taking too long.

"F1" is a purchase order form
"F2" is the child form contains the items associated with the po in "F1".
"F2" has a running total of the cost of all items

"F3" is a job form
"F1" is now a child form containing all the po associated with this job
"F1" has a running total of all po's that is displayed and used in
calculations in "F3"

Until I'm told of anther way or get an inspiration I have to keep pursuing
this method till I figure it out.
 
Use queries as the RecordSources for your forms. You can sum the item costs
so that it shows at the parent level. If necessary (because you need the
form to be updatable), you can use DSum() to sum the item costs.
 
Let me see if I can explain this without taking too long.

"F1" is a purchase order form
"F2" is the child form contains the items associated with the po in "F1".
"F2" has a running total of the cost of all items

"F3" is a job form
"F1" is now a child form containing all the po associated with this job
"F1" has a running total of all po's that is displayed and used in
calculations in "F3"

Until I'm told of anther way or get an inspiration I have to keep pursuing
this method till I figure it out.
Click to expand...

It looks like you might only need to display this on screen. It's a
different issue with saving the data to a table, though. Pat
Hartmann's suggestion to use queries and do the sums in the query is a
good one, but doesn't address the "save to a table" question.

If you need to create an unchanging historical record of your purchase
orders, then you do need to store all the data, including calculated
totals, just as it appears on the invoice or purchase order. In that
case, it might be better to use unbound forms and do everything in VBA
with transactions since it probably involves updates to several tables
at once.
 
That's very close to what I actually did.

After having to state my problem to Mr. Hairgrove I went back and evaluated
my premise and decided to try using only the data from F2. It had all the
data I needed.
I actually stumbled upon the grouping in the queries. Dumb luck I guess.

Tables for F1 "P.O.'s" and F2 "P.O. Items" are joined and have a
relationship with the PO Index.

I created a querie with the tables from both F1 and F2 that summed the
individual items from F2 and grouped on the PO index from F1.

The query worked beautifully. I remove the need to write code to populate
fields
It updated lighting fast.

I also included my job number index associated with F1 so I could use the
same query later in my job forms to create the new subform "F3" that showed
each po and the po total (which is the summed items in the query)

**** Where would you put the dsum? In the query?
 
Domain functions can be used wherever other functions can be used. However,
Let me reiterate, using domain functions in a query is a last resort. Each
domain function must run its own query so if you have only 100 rows in your
recordset, 100 queries must be run if you are using a domain function. Of
course, the more rows you have, the slower the query will be. It was past
my bedtime when I responded to your post yesterday so the message regarding
DSum() was incomplete. I meant to suggest that you use it on the form if
you needed an updatable recordset. At least when you use the domain
functions on a form, they only run for each record you view. To use a
domain function on a form, place it in the ControlSource of a control:

=DSum("amt", "yourtable", "fldA = " & somefield)

Bretsw said:
That's very close to what I actually did.

After having to state my problem to Mr. Hairgrove I went back and
evaluated
my premise and decided to try using only the data from F2. It had all the
data I needed.
I actually stumbled upon the grouping in the queries. Dumb luck I guess.

Tables for F1 "P.O.'s" and F2 "P.O. Items" are joined and have a
relationship with the PO Index.

I created a querie with the tables from both F1 and F2 that summed the
individual items from F2 and grouped on the PO index from F1.

The query worked beautifully. I remove the need to write code to populate
fields
It updated lighting fast.

I also included my job number index associated with F1 so I could use the
same query later in my job forms to create the new subform "F3" that
showed
each po and the po total (which is the summed items in the query)

**** Where would you put the dsum? In the query?
Click to expand...
 
Back
Top