VBA to calculate average in text field based on two other text fields

[jbeets]

Hi!



I am trying to populate the average of two fields when I press a button.

Based on the below code the

txtDeviationScore field populates with the correct score. The txtDifferenceScore field does not populate with anything, nor do I get an error message. This field should populate based on what is showing in the txtFails field. Once those are working properly I need to populate the "score" field with the average of the txtDeviationScore and the txtDifferenceScore.

I know I am missing something simple as I tried to add on to this code but I cant seem to figure out what the issue is.

Private Sub Command468_Click()
Dim target_control As Control
Dim control_Value As Integer
Dim control_Count As Integer
Dim Score As Integer
control_Value = 0
control_Count = 0
For Each target_control In Me.Controls
    Select Case target_control.Name
        Case "cboPhysicalReview", "cboReviewOf"
        Case Else
    If TypeName(target_control) = "ComboBox" Then
        If Not target_control.Value = "Yes" Then
        MsgBox (target_control.Name & ", " & target_control.Value)
            control_Value = control_Value + 1
            control_Count = control_Count + 1
        End If
    End If
End Select
Next
    If txtPercentDiviation.Value <= 5 Then
        txtDeviationScore.Value = 5
      If txtPercentDiviation.Value > 5 And txtPercentDiviation.Value <= 10 Then
         txtDeviationScore.Value = 4
        If txtPercentDiviation.Value > 10 And txtPercentDiviation.Value <= 15 Then
            txtDeviationScore.Value = 3
           If txtPercentDiviation.Value > 15 And txtPercentDiviation.Value <= 3 Then
              txtDeviationScore.Value = 2
              If txtPercentDiviation.Value > 3 And txtPercentDiviation.Value <= 49 Then
                  txtDeviationScore.Value = 1
                  If txtPercentDiviation.Value > 49 Then
                     txtDeviationScore.Value = 0
                    End If
                End If
            End If
        End If
    End If
End If

If txtFails.Value <= 1 Then
        txtDifferenceScore.Value = 5
      If txtFails.Value = 2 Then
         txtDifferenceScore.Value = 4
        If txtFails.Value = 3 Then
            txtDifferenceScore.Value = 3
           If txtFails.Value = 4 Then
              txtDifferenceScore.Value = 2
              If txtFails.Value = 5 Then
                  txtDifferenceScore.Value = 1
                  If txtFails.Value > 5 Then
                     txtDifferenceScore.Value = 0
                    End If
                End If
            End If
        End If
    End If
End If

txtFails = control_Value

For Each c In Me.Controls
    If TypeName(c) = "ComboBox" Then
        If c.Value = "" Then na = MsgBox("All dropdown boxes must be filled out before continuing. Please choose Yes, No, Partially or N/A before continuing.", vbOKOnly, "Quarterly Audits")
    End If
Next c
End Sub

Thank you for any help you can provide!

[kd2017]

Looking at your code is there a copy and paste typo, should txtPercentDiviation be txtPercentDeviation? Also, to refere to these objects on your form put the word Me. infront of the object name. Finally make sure you have "Option Explicit" at the very top of every single code module file, when you have that up there it will help you catch these mistakes.

Your nested if statements are hard to digest, and I'm thinking you shouldn't be using nested ifs here anyway. If you follow your code "if txtPercentDiviation.Value <= 5 then" you'll see that if it's something greater than five it will evaluate to false and exit the if statement completely, it doesn't ever fall to and execute any of the following nested ifs. I'd suggest using select case statements or if else. Here is untested examples of select case statements:

Select Case Me.txtPercentDeviation.Value
    Case Is <= 5
        Me.txtDeviationScore.Value = 5
    Case 6 To 10
        Me.txtDeviationScore.Value = 4
    Case 11 To 15
        Me.txtDeviationScore.Value = 3
    Case 16 To 30
        Me.txtDeviationScore.Value = 2
    Case 31 To 49
        Me.txtDeviationScore.Value = 1
    Case Else
        Me.txtDeviationScore.Value = 0
End Select

Select Case Me.txtFails.Value
    Case Is <= 1
        Me.txtDifferenceScore.Value = 5
    Case 2
        Me.txtDifferenceScore.Value = 4
    Case 3
        Me.txtDifferenceScore.Value = 3
    Case 4
        Me.txtDifferenceScore.Value = 2
    Case 5
        Me.txtDifferenceScore.Value = 1
    Case Else
        Me.txtDifferenceScore.Value = 0
End Select

[jbeets]

Wow! Yours is so much cleaner and easier. Thank you for explaining this to me. It works perfectly!