Automaticaly Sum multiple fields in a form

[Kemidan2014]

I am helping a co worker from another department with their database as the original creator no longer works for Company. They asked me if it was possible for a total feild to automatically add up values from other fields and still be bound to the table while doing so.

My solution was to use VBA in the form on _Afterupdate to add values from the fields so that it displays the number in the Total field (which is tied to the same table as the other field values I am adding up

The VBA adds up the values but gives me a Macro error 2950 and gives me options to stop all macros. is this a Code problem or a Client settings problem? I googled the error and it all suggests Trust center settings but i have enabled them and i still get the error.

I am working on a back up copy of the original front end. below is the code I added to perform the math.

Private Sub Form_AfterUpdate()
Me.Total_b.Value = Me.RI_b.Value + Me.DC_MO_b.Value + Me.FI_b.Value + Me.MA_b.Value + Me.AS_b.Value + Me.WPAS_b.Value + Me.WPMA_b.Value + Me.QC_b.Value + Me.HT_GC_b.Value + Me.RM_b.Value + Me.ST_b.Value + Me.Ship_b.Value + Me.Dock_b.Value

End Sub

The original problem they had is they needed the Totals for each record event with in a certain time frame. what I found out was happening is no one was entering in the totals in the Total_b field because they were instructed not to as their supervisor who worked on this database used an expression in a REPORT that was NOT tied to the table to add up values. but the problem with it is after the 3rd record it just repeated the same value over and over regardless of what the true math would be for each record.

I found a solution for this by editing the query where the report is generating from by having the QUERY do the math NOT the report fields. This corrected the export BUT. it still did not update the table with the summed up values so i have thousands of records with 0 in the Total_b field

my approach:
1) fix the data entry to enter values into the table for the total while overcoming user errors in doing math themselves and either a) entering in incorrect values or b) skipping this step because they're in a rush
2) Run a simple update query to do a one-time math function to put in the correct values in the total_b field in the table.

 

[Kemidan2014]

I figured i would add the error image to give context. argument of 18 was a number i keyed into one of the fields associated with the VBA code in my previous post

 

[welshgasman]

How to Fix MS Access Database Error 2950? | Stellar

Quick fixes for MS Access Database error 2950. The easy solution will hep you to get rid of this error without any hassle.

www.stellarinfo.com

Unable to run macro in Access database - Office

Fixes an issue in which you can't run a macro that calls a VBA function in an Access database.

learn.microsoft.com

Does this apply?
Unless EVERY control has a value, that code is going to fail?

I would be looking at why the report was doing what it did, as it appears it was working for a good while? When that is the case and it then does not work, unless code amendments have occurred, the data is at fault?

Also not sure why a macro is at fault when you are coding in VBA?

 

[Kemidan2014]

Also not sure why a macro is at fault when you are coding in VBA?

I think I stumbled upon the answer to that just now, I was clicking through the Design view of the form. and in the properties of the fields i am wanting to Sum, I found that they all had an Embedded macro to Refresh the form any time it was updated.

So I went through and removed the Embedded Macro option from After Update property for each field.

Effect: Error no longer occurs HOWEVER the calculated value does not display in the Total_b feild UNLESS I MANUALLY click the "Refresh All" button in the Home tab.

I added Me.Refresh to the end of the code and that did not cause a refresh. it just causes Run Time Error 2115 when i manually refresh So i removed the Me.Refresh.

How do I get the form to refresh itself and display the newly updated sum value in Total_b field anytime I change the values in the other 13 fields? I would prefer to do this through VBA and not the Embedded macro system. I think this is what was causing the error in the first place.

 

[welshgasman]

Should be seen immediately?
I just tested a form with

Private Sub Check31_Click()
Debug.Print "Check31 is "; Me.Check31
If Check31 Then
    Me.txtTime = 13 + 23 + 45
Else
    Me.txtTime = 0
End If
End Sub

and that works fine?

 

[Kemidan2014]

So doing the onclick would require i make 13 versions of it but I altered the name of the sub to be the name of a field_AfterUpdate and it does calculate and update so i Will just have to bear it and create 13 versions of this code each associated with the field the user would add data to

 

[welshgasman]

No, I just used OnClick on some control I had on a test form.
What do you mean 13 versions?, it needs to apply to 13 forms?

 

[Micron]

It's generally accepted that you don't store calculations in tables. Maybe not doing so would eliminate this issue?

 

[Kemidan2014]

Private Sub Form_AfterUpdate()
Me.Total_b.Value = Me.RI_b.Value + Me.DC_MO_b.Value + Me.FI_b.Value + Me.MA_b.Value + Me.AS_b.Value + Me.WPAS_b.Value + Me.WPMA_b.Value + Me.QC_b.Value + Me.HT_GC_b.Value + Me.RM_b.Value + Me.ST_b.Value + Me.Ship_b.Value + Me.Dock_b.Value


End Sub

For some reason the VBA formula code will not update Field "Total_b" in the form if i associate it with "Private Sub Form_AfterUpdate()" but when i changed it to for example to "Private Sub RM_b_Afterupdate"
entering a new number in RM_b field in form view changed the value of Total_b automatically with out error or clicking the refresh button so i am going to basically replicate this math in "Ri_B_Afterupdate()", "DC_MO_B_AfterUpdate()" and so on.

 

[welshgasman]

After Update does not work if VBA updates the value. Only when the user alters it.
You could use FormCurrent?

However @Micron has rasied a valid issue. Your totals should be calculated as needed.

Again, I would be looking at fixing the report.