How To Calculate Total from values of other fields

[Pada]

I have four combo boxes text fields (S1, S2, S3 and S4) on a form that are bound to four fields (Score1, Score2, Score3 and Score4) in table Class. These fields have data type as number (integer), and they are control sources of those four combo box fields.

S1, S2, S3 and S4 each has a dropdown value from 0 to 100 (with interval 10) for user to select.

I'd like to calculate the total of these field based on what user entered and put the result into a text field, name txtTotalScore. This field is not bound to any field in the table Class.

After user enters values for each field, I'd like to validate the total of these fields: they must be equal to 100.



A simple general formula for the TotalScore field at its control source is S1+S2+S3+S4.

How can I apply this formular in MS Access correctly to yield the correct result and how I validate it in codes?

Thank you in advance for your help.

[madpiet]

Looks like you're treating Access like Excel. I think your design is wrong.

Is this for keeping track of student scores or something like that?

Normally, you'd have a structure more like (StudentID, TestID, Score) then getting an average would be super simple:

SELECT StudentID, AVERAGE(Score)
FROM MyTable
GROUP BY StudentID;

If I were you, I'd stop for a minute and make sure my tables are set up correctly. Doing that will save you a ton of headache down the road. A bad design will paint you into a corner really quickly. You may be able to use it to answer some simple questions, but when you try to answer complicated questions, it'll fall flat.

If you answer our questions, someone can suggest a better design.

[June7]

Simple expression in textbox: =S1 + S2 + S3 + S4

If any can be left Null, need to deal with that: =Nz(S1) + Nz(S2) + Nz(S3) + Nz(S4)

Or do the addition in Calculated field in table.

Will there always be a score in each? Will there never be more than 4? True, this is not a fully normalized structure but can make it work. If you ever do want to do statistics such as averages, non-normalized will make it more complicated.

When you say "this field is not bound" do you mean "this textbox is not bound"?

Don't refer to controls on forms or reports as "fields" - fields are in tables and queries.

[jojowhite]

you can also write a Function inside your Form:

Public Function fnTotal() As Integer
With Me.txtTotalScore
    .Value = Val([S1] & """) + Val([S2] & "") + Val([S3] & "") + Val([S4] & "")
    IF .Value > 100 Then
        Msgbox "Total should be equal to 100"
    End If
End With
End Function

on design view of your form, add this to Each combobox's AfterUpdate Property:

=fnTotal()

[madpiet]

oh yuck. No, just no.
For one, why are you creating a function and not adding input parameters? And two, your function doesn't return a value.

AND, a function declaration ends with End Function, not End Sub.

[jojowhite]

are you Trolling my post? you should not because you are not the OP.
and the OP has the last to say.

see this to understand that a function can be placed in the AfterUpdate Property of a textbox:
TextBox.AfterUpdate property (Access) | Microsoft Learn

[June7]

Okay, only function procedure can be called from event property. Function does not have to return a value but should do something with result of any calcs, such as set value of a control, which is what this procedure does. Input parameters are not need because procedure would be behind form and pull values directly from controls. Unfortunately, since textbox is UNBOUND, every record will show the same result. If form is set for single view that may be okay but for continuous or datasheet then no.

[jojowhite]

that is true for continuous form, same value in txtTotalScore will propagate to all records.
then do the totally in the Query and use the query as recordsource of the form. remove
the assignment of value from the function since it is readonly anyway.

[Welshgasman]

Why are you asking this again in aa new thread?
https://www.accessforums.net/showthr...537#post530537

[Pada]

Hi June7, the first control S1 is required, and its default value is 100. Other controls can be null. However, if S1 is less than 100, other controls must be filled out, so the total is 100.

No, the addition in the Calculated textbox is not in table. It is used to monitor the total. If the total is less than or greater than 100, an alert message will pop up.

There are 4 controls. No more.

Thanks

[Pada]

Thank you very much, jojowhite for your code. Nice work.

[Pada]

Wedshgasman, because that approach doesn't work.

[Pada]

I actually have tried to work on it and put this formula in the Calculate's control source.
=Nz([S1,0])/100 + Nz([S2,0])/100 + Nz([S3,0])/100 + Nz([S4,0])/100

The weird thing is if I take 100s from the formula out, values enter will be concatenated as a string. For example, if I entered 70, 10, 10, 10 it will show as 70101010.
So I leave the formular as is to get 1 (total scores / 100 = 1). Then in the BeforeUpdate event, I put code like this.
If txtTotalScore = 1 then
Exit Sub
Else
MsgBox "The total must be 100"
End if
The second strange thing is if I entered 70, 10, 10, 10, it will pop up the alert message even txtTotalScore = 1.
On the other hand, if I entered 60, 20, 10, 10 or 50, 30, 10, 10 then it works like a champ.
The third strange thing to me is the <> operator seems do not work. For example, If txtTotalScore <> 1 then pops up the message. It always pops up the message no matter I put the total right values that add up to 100. Therefore, I have to write the codes above with the = operator.

[jojowhite]

it might be that the ControlSource of your four textbox is actually a Short text and not a Number.

[Pada]

Hey jojowhite, good catch!!!!

Yes, you are right. Those scores fields in the table are actually short text. That explains why those strange things happened.

Actually, I did change to Number data type for them in the backend and link to my frontend database, but the table in the frontend did not change. I have to delete the link and re-link it again.

Thank you for your help.