Access Calculate for All Results in Continuous Form
You can use code to force access to recalculate the totals. Put the code in the after update event of those 4 controls in the detail section.
Here is the code to use.
Private Sub ControlName_AfterUpdate()
Me.Recalc
End Sub
Replace my object names with your own.
Jeanette Cunningham MVP(Access) Pakenham, Victoria Australia
Was this reply helpful?
In the simple case that is not really possible I think, because the footer recalculates based on SAVED values.
If you REALLY wanted to do it, you would have to write some code to populate the footer controls based on the sum of the values excluding the current row, plus the unsaved value in the current row. That would require a fair amount of coding skills.
-Tom. Microsoft Access MVP
-Tom.
Microsoft Access MVP
Phoenix, AZ
Was this reply helpful?
"zipone" wrote in message
news:*** Email address is removed for privacy ***...
>
> I have a continious form where each record has four numeric vales. In the
> footer I have totals for the four fields. When I change a value in one
> field and go to the next field in the save recode the total in the footer
> will not update. If I go to a different record then the totals update.
> How do I get the totals to update when I go to the next field in the same
> record?
To do this without getting very elaborate (a la Tom van Stiphout's idea),
you would need to force the record to be saved as soon as you change any of
those four values. You could do that by executing "Me.Dirty = False" in
each control's AfterUpdate event procedure. That way, simple =Sum()
expressions in the footer will work. However, it's up to you to decide
whether you want to force a record save so often.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
Dirk Goldgar, MS Access MVP 2001-2015
Access tips: www.datagnostics.com/tips.html
Was this reply helpful?
The field (and its record) would have to be saved for that to happen automatically. Saving a record every time one of the four fieds is edited would greatly increase the form's I/O and should be avoided. If you want to try it the code in each of the text boxes AfterUpdate event would be:
Me.Dirty = False
Using code to calculate the totals in unbound footer controls is possible, but I would hesitate to do it if there are a significant number of records that could cause a noticible delay in UI actions. If there are a reasonably small number of records you can try (in a test vopy of the form) removing the Sum expressions from the footer text boxes and adding code like this to each of the four text box's AfterUpdate event procedures:
Dim total As ??? 'depends on the datatype of the field
With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
Do Until .EOF
total = total + !thefield1
.MoveNext
Loop
total = total + Me.txtthefield1 - Me.txtthefield1.OldValue
Else
total = 0
End If
End With
Me.txttotalfield1 = total
Note that the name of the field (thefield1 above) to total and the name of the text box (txtthefield1 above) bound to it must be used as in the above if the names are different.
You will also need to call all four AfterUpdate event procedures in the form's Load event to set the totals when the form opens:
txtthefield1_AfterUpdate
txtthefield2_AfterUpdate
txtthefield3_AfterUpdate
txtthefield4_AfterUpdate
Are you really sure you want to do all that?
Caveat: The only times this kind of code is needed is when Access is too busy to recalculate text box expressions as quickly as you would like. Especially if you have other code that tries to use the result of the text box expression. If all you need is to display the result of the calculation, then the expression Dirk posted is by far the easiest way to do it.
Was this reply helpful?
That suggestion would of course require the fields not to be required, which may violate other business rules. I would certainly not want the OP to relax those business rules so he could implement this rather curious requirement / feature. As with most things Access: better go with the flow.
-Tom. Microsoft Access MVP
-Tom.
Microsoft Access MVP
Phoenix, AZ
Was this reply helpful?
"Tom van Stiphout [MVP]" wrote in message
news:*** Email address is removed for privacy ***...
> That suggestion would of course require the fields not to be required,
> which may violate other business rules. I would certainly not want the OP
> to relax those business rules so he could implement this rather curious
> requirement / feature. As with most things Access: better go with the
> flow.
Agreed. But I just had an idea that I think will allow an accurate sum
including unsaved changes, without elaborate code. Suppose that you wanted
to sum the field MyField, and you want the summing control to be accurate as
soon as the field is changed, even before the edited record is saved. This
formula seems to work:
=Sum([MyField])-Nz([MyField].[OldValue],0)+Nz([MyField],0)
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
Dirk Goldgar, MS Access MVP 2001-2015
Access tips: www.datagnostics.com/tips.html
Was this reply helpful?
Ladies and Gentlemen, you are all making too much out of this... Sheesh.
Zipone, you can't see the results of your changes to a field until you commit the record. That means saving it. When you leave the current record and it is dirty (chagnes have been made), it is automatically saved. To see the results of your changes you will need to update the record one way or another. Now you can do this through code in the After Update event of each field, but there is an easier way...
The simple solution to your problem is as follows:
To update the current record and remain at the same position, press Shift+Enter.
Just another helpful tip. Shift+F9 will requery the recordset. However, you shouldn't have to requery.
Was this reply helpful?
"Apollo67" wrote in message
news:*** Email address is removed for privacy ***...
> Ladies and Gentlemen, you are all making too much out of this...
Of course. But it was an interesting technical question, and clearly of
some interest to the end user.
> Zipone, you can't see the results of your changes to a field until you
> commit the record.
You can say that if you want, but I just showed a simple way to do it. Now,
if you want to say, "There's no *point* in expecting to see the results of
your changes to a field until you commit the record," I would agree with
you. So your advice is probably good -- unless the user has some good
reason to want totals to be accurate as soon as a field is changed. It may
be a waste of time, but it can be done, and without much difficulty.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html
Dirk Goldgar, MS Access MVP 2001-2015
Access tips: www.datagnostics.com/tips.html
Was this reply helpful?
Hey All,
Hope I didn't ruffle any feathers here. Just trying to make light humor. There are all kinds of ways to manage a problem. I just figured I'd throw my 2 cents in. :)
Happy Holidays All!
Was this reply helpful?
Source: https://answers.microsoft.com/en-us/msoffice/forum/all/update-totals-in-a-footer-on-a-continuous-form/220de7a4-bada-4cad-8d82-affb1204dac3
0 Response to "Access Calculate for All Results in Continuous Form"
Post a Comment