Total Values Must Be 100

[Pada]

I have four text fields (Class1 to Class4) and four corresponding score fields (Score1 to Score4).
The first text field (Class1) is default, and the first score field (S1) is also default to an instant number 100. Most students are default to these FIRST two fields (for example, Class1.Value and Score1.Value (=100).
However, some students may be also assigned to the second, or third or fourth text field(s) and they also get their corresponding score(s).
The rule is total of the scores must be 100.


I am trying to write the VBA logic to validate this requirement as following. Please forgive my syntax (like DoCmd Msg or Cancel); I will fix it later.
Is the programming logic below written correctly to validate the requirement? Do you have more efficient programming codes to deal with this logic?
Thank you so much in advance.

If (Class1.Value is not null and Class1.Value is not "") AND (S1.Value is not null and S1.Value is not "") then
      If S1 = 100 Then Exit Sub              'Met the condition so exit
      ElseIf S1 < 100 Then                     'The condition doesn't meet, so check the next fields
        If (Class2.Value is not null and Class2.Value is not "") AND (S2.Value is not null and S2.Value is not "") then
               If S1 + S2 = 100 Then Exit Sub 
               ElseIf S1 + S2 < 100 Then    'Check the next fields
                      If (Class3.Value is not null and Class3.Value is not "") AND (S3.Value is not null and S3.Value is not "") then
                                If S1 + S2 + S3 = 100 Then Exit Sub 
                                ElseIf S1 + S2 + S3 < 100 Then    'Check the next fields
                                    If (Class4.Value is not null and Class4.Value is not "") AND (S4.Value is not null and S4.Value is not "") then
                                           If S1 + S2 + S3 + S4 = 100 Then
                                                  Exit Sub
                                           Else                 'Either the total values of S1 + S2 + S3 + S4 less than 100 or greater than 100
                                                  DoCmd Msg "The total must be 100"
                                                  DoCmd Cancel
                                            End If
                                     Else
                                           DoCmd Msg "The values of Class 4 and Score 4 must be entered"
                                           DoCmd Cancel
                                     End If
                                 Else                 'The total values of S1 + S2 + S3 is greater than 100
                                            DoCmd Msg "The total values of S1 + S2 + S3 must be 100"                                           
                                            DoCmd Cancel 
                                 End If                
                        Else
                                 DoCmd Msg "The values of Class 3 and Score 3 must be entered"
                                 DoCmd Cancel
                        End If
                Else      'The total values of S1 + S2 is greater than 100
                     DoCmd Msg "The total values of S1 + S2 must be 100"                                           
                     DoCmd Cancel
                End if
         Else
                DoCmd Msg "The values of Class 2 and Score 2 must be entered"
                DoCmd Cancel
         End If
      Else      'The total values of S1 is greater than 100
             DoCmd Msg "The value of S1 must be 100 or less"                                           
             DoCmd Cancel
      End If
Else
      DoCmd Msg "The values of Class 1 and Score 1 must be entered"
      DoCmd Cancel
End If

[June7]

Please post code between CODE tags to retain readability. Click # button on tool bar.

Have you even tested?

[Pada]

Please post code between CODE tags to retain readability. Click # button on tool bar.

Have you even tested?

Hi June7,
Thank you for your response. No, I haven't tested yet. I will try to do it tomorrow.

[jojowhite]

not tested:

    Dim tf As Boolean
    Dim n1 As Double, n2 As Double, n3 As Double, n4 As Double
    Dim s1 As String, s2 As String, s3 As String, s4 As String
    
    n1 = Nz([s1], 0): n2 = Nz([s2], 0): n3 = Nz([s3], 0): n4 = Nz([s4], 0)
    s1 = [class1] & "": s2 = [class2] & "": s3 = [class3] & "": s4 = [class4] & ""
    tf = False
    
    Select Case True
        Case n4 <> 0
            If (n1 + n2 + n3 + n4) = 100 Then
                If Len(s1) = 0 Or Len(s2) = 0 Or Len(s3) = 0 Or Len(s4) = 0 Then
                    MsgBox "Please enter the name of all 4 classes!"
                    tf = True
                End If
            Else
                MsgBox "Please make sure that the total of 4 scores is equal to 100."
                tf = True
            End If
            
        Case n3 <> 0
            If (n1 + n2 + n3) = 100 Then
                If Len(s1) = 0 Or Len(s2) = 0 Or Len(s3) = 0 Then
                    MsgBox "Please enter the name of all 3 classes!"
                    tf = True
                End If
            Else
                MsgBox "Please make sure that the total of 3 scores is equal to 100."
                tf = True
            End If
        Case n2 <> 0
            If (n1 + n2) = 100 Then
                If Len(s1) = 0 Or Len(s2) = 0 Then
                    MsgBox "Please enter the name of all 2 classes!"
                    tf = True
                End If
            Else
                MsgBox "Please make sure that the total of 2 scores is equal to 100."
                tf = True
            End If
        Case Else
            If n1 = 100 Then
                If Len(s1) = 0 Then
                    MsgBox "Please enter the name of 1st class!"
                    tf = True
                End If
            Else
                MsgBox "Please make sure that score1 is 100."
                tf = True
            End If
        
    End Select
    
    Cancel = tf

[Welshgasman]

Hi June7,
Thank you for your response. No, I haven't tested yet. I will try to do it tomorrow.

Always a good idea to test your code and when it does not work as expected WALK your code with F8 and breakpoints.

[Pada]

Hi jojowhite,
Neat and nice codes! I didn't think about using the CASEs statement. Looks like your codes are better than mine. I will use them and test them.

Thank you very much for your time and help.

[Pada]

In another direction, I am thinking using the field's Validation Rule and Validation Text properties to setup the requirements such as Not Null and not "" and other rules.

That way will reduce the VBA line of codes.

Do you think it is a good idea? Thank you!

[June7]

Probably won't change performance. If you already have working code, why bother with the properties? But if code isn't working and setting properties would be faster and less frustrating to get working, then use them.

[Pada]

Probably won't change performance. If you already have working code, why bother with the properties? But if code isn't working and setting properties would be faster and less frustrating to get working, then use them.

Got it, June7. Thank you for replying. I definitely have to test the codes using Debug mode like Welshgasman (Thank you) mentioned.