469,346 Members | 6,518 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,346 developers. It's quick & easy.

Calculating and recording on the table from 12 fields in form


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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Field1_AfterUpdate()
  2. 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
  3. End Sub
  5. Private Sub Field2_AfterUpdate()
  6. Call Field1_AfterUpdate
  7. 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.
Jul 1 '10 #1
2 1331
32,184 Expert Mod 16PB
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!
Jul 1 '10 #2
3,532 Expert 2GB
Even better would be creating a query based on your form with a calculated field like:

Expand|Select|Wrap|Line Numbers
  1. 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)>
Jul 1 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Mike S. Nowostawsky | last post: by
4 posts views Thread by yanjie.ma | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.