Calculating and recording on the table from 12 fields in form

[dragmn2]

Hi,

I am new to Access and I seem to have run into a conundrum. I am currently using Ms Access 2007. I have a table setup with 12 fields and form with 12 fields which is corresponding to the 12 fields on the table.

The 12 fields on the form are combo box with setting
Row source: 1;"Yes";0;"No "
Column Count: 2
Column widths:0";2"

If I am correct, this allows the user to select Yes or No and have a corresponding values of 1 or 0 inputted in the table.

Now the real question is that I need to make a field that sums up the values from the 12 fields in the form and records it onto the table.

So far I have set the following up in the code builder

Private Sub Field1_AfterUpdate()
Me.Total = Me.Field1 + Me.Field2 + Me.Field3 + Me.Field4 + Me.Field5 + Me.Field6 + Me.Field7 + Me.Field8 + Me.Field9 + Me.Field10 + Me.Field11 + me.Field12
End Sub

Private Sub Field2_AfterUpdate()
Call Field1_AfterUpdate
End Sub

The Afterupdate on Field2 is repeated for Fields 2-12.

I have a hunch I did something wrong in the code and would appreciate any help in solving the problem.

Also, I am willing to undertake any other methods other than code builder if it yields better results as long as the Total field sums the values of the fields 1-12 and records it in the table.

[NeoPa]

If you only have 12 fields in your table where would the sum be stored?

If you have common code that needs to be run by each of the 12 different AfterUpdate event procedures then it is a good idea to put that code in a separate subroutine procedure and call it from each of the 12 event procedures.

See Normalisation and Table structures for why storing calculated results (your totals in this case) is not recommended at all.

Welcome to Bytes!

[missinglinq]

Even better would be creating a query based on your form with a calculated field like:

TotalField: Nz([Field1]) + Nz([Field2]) + Nz([Field3])

and so forth for all 12 fields. You'd only need the formula in one place, wouldn't need to call it from each AfterUpdate event, and you'd have a field for the total.

Then you'd simply base your forms, reports, etc on the query.

Linq ;0)>