Modify vba calculation to include percentage when criteria is met

[jbeet]

I have a form which has several combo boxes with the options of “Yes”, “No” or “N/A”. Some of the cbo’s have special requirements. When “No” is select in a few of the cbo’s a form will pop up requesting some additional information resulting is a percentage.
When there is a percentage entered into this box, I need the below code to calculate the score incorporating the percentage instead of following the normal calculation for “No” responses.

• The form uses a Tab control. Each tab has specific cbo’s with the TAG property set based on the tab. For example, the Documentation tab has 6 cbo’s which all have the Tag “Doc”
• Each of the cbo’s are named:
  • DocQ1, DocQ2, DocQ3, DocQ4, DocQ5 and DocQ6
  • DocQ2, DocQ3 and DocQ5 each have the additional requirement if “No” is selected. So when calculating the score, if any of these 3 cbo’s = “No” then I need the code to use the variance score in the calculation which will be listed as DocQ2Var, DocQ3Var and/or DocQ5Var. Otherwise the code should calculate the “yes” and the “N/A” as it is currently written.
  • DeskFieldTable is the name of the table that both of the forms use as a record source.

The below code works great currently but I do not know how to modify it to incorporate the additional criteria of the variance score. Thank you in advance for any help you can provide!

Private Sub btnDocScore_Click()
Dim c As Control, nYes As Long, nPartial As Long, nNo As Long, nNA As Long
nYes = 0
nPartial = 0
nNo = 0
nNA = 0


For Each c In Me.Controls
    If c.Tag = "Doc" Then
        If c.Value = "Yes" Then nYes = nYes + 1
        If c.Value = "Partially" Then nPartial = nPartial + 1
        If c.Value = "No" Then nNo = nNo + 1
        If c.Value = "NA" Then nNA = nNA + 1
    End If
Next c
DocScore = Format((nYes + nPartial * 0.5) / (nYes + nPartial + nNo + nNA), "Percent")

For Each c In Me.Controls
    If c.Tag = "Doc" Then
        If IsNull(c.Value) Then
         MsgBox ("ComboBox selection left blank. Please ensure all drop downs are selected before continuing.")
    Else
        txtDocStatus = "Complete"
    End If
End If
Next c

For Each c In Me.Controls
    If c.Tag = "Status" Then
        If c.Value = "Complete" Then
        txtDocStatus.BackColor = vbGreen
    End If
End If
Next c
End Sub

Private Sub cboDocQ2_AfterUpdate()
Select Case Me.cboDocQ2
    Case "No"
        DoCmd.OpenForm "frm_Variance", acNormal, , "ID = " & txtMasterID
End Select

Me.Requery

End Sub

 

[Micron]

cross posted

Calculating a score based on cbo selection while looking at multiple fields

I have a form which has several combo boxes with the options of “Yes”, “No” or “N/A”. Some of the cbo’s have special requirements. When “No” is select in a few of the cbo’s a form will pop up requesting some additional information resulting is a percentage. When there is a percentage entered...

www.accessforums.net

 

[Joe4]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

If you have posted the question at more places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.