Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94

    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 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!

    Code:
    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

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not seeing any code that opens popup form. I presume that would be behind combobox AfterUpdate event. Set value of a global variable or TempVar or textbox on calling form and use that in formula.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Here is the code i have set on the after update event of each cbo to open the form
    Code:
    Private Sub cboDocQ2_AfterUpdate()Select Case Me.cboDocQ2
        Case "No"
            DoCmd.OpenForm "frm_Variance", acNormal, , "ID = " & txtMasterID
    End Select
    
    
    Me.Requery
    
    
    End Sub

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Code in some event behind popup form should set value of a global variable or TempVar or textbox on calling form. Pick one and try it. Real trick is figuring out what event to put code into.

    If forms have same RecordSource, are you saying popup is opening to same record that is in edit mode on main form? Then you are entering a value on popup form? This attempts to edit same record in two places - that should not work.


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Thank you June7.
    Both forms are linked to the same table. When the second form opens it does open to the same record in the table as the main form. There are fields in the table for each the controls on the pop up form. So it is modifying the same record but not the same fields in the record.

    I made a sample database so that you can see the tables/forms and full vba.
    Attached Files Attached Files

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,
    It looks like this dB was a SS imported into Access. Consider normalizing the table design.

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi
    What does DCL stand for?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I think mike60smart meant DOL. Also, what is COL?

    I agree with ssanfu, probably should normalize structure and get away from spreadsheet-like 'flat file'.

    Again, record in edit mode simultaneously in two places should not work.

    You have undeclared variables and with Option Explicit this triggers compile error. Have you even run Debug Compile?

    Step debug code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would guess:
    DOL= Date of Loss
    COL = Cause of Loss


    The sample dB you posted appears to be missing some tables and forms.
    I don't see where you can enter the "AuditName", "Manager" or "UserName" (employee/Adjuster) on either form.

  11. #11
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Sorry for the delayed reply - I was on vacation and thankfully not thinking about this database!

    I copied only the pieces out of my original database to one that I could post here and maybe I missed some important pieces.... I will go back and review what I missed.

    - In the form there are a few fields that are used to help filter the records in the table. Status, Quarter, Manager and user name. Once these fields are selected the other fields in the top portion of the form will populate with the first matching record in the table. The user will then select the drop down boxes on each tab in the form to create a score based on their responses. The table is loaded with new records to be audited each month.
    - DOL = Date of Loss
    -COL = Cause of Loss

    - What if I get rid of the pop up box and instead put text boxes that can be filled in directly on the form. Then from there, I need to figure out how to say in VBA - if this cbo = No then use the % in the variance box in the score calculation instead of the "No"....

    I know there are a lot of other issues with the database that you all have pointed out; I truly appreciate the advice and help you provide. I am still learning and so I basically piece together the database with what I can find online or with what someone helps me with. Once I can get the calculation issue resolved I can move on to some of the other issues that you pointed out.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, textbox on form would simplify coding.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Ok - I added the text fields to the form. Now, I need to modify the code that calculates the score to say IF cboDocQ2 = No Then use txtDQ2Var in the score calculation. Below is the current code.

    The current code is looking at the cbo's and creating a score based on their response. If No is chosen currently the user gets a 0 on that question. Instead of the 0 I need it to use the % that will be entered in the variance text box.

    On this tab of the form, 3 of the 6 questions would need to use that variance % in the score calculation in order to provide an accurate score if No was selected. The cbos are:
    cboDocQ2
    cboDocQ3 and
    cboDocQ5

    The text boxes with the variance score are:
    txtDocQ2Var
    txtDocQ3Var
    txtDocQ5Var

    If Yes is selected then the calculation should proceed as it is currently written. I also provided an image of what this looks like on the form.Click image for larger version. 

Name:	Example Form Variance.PNG 
Views:	24 
Size:	118.9 KB 
ID:	43900

    Code:
    Private Sub btnDocScore_Click()Dim c As Control, nYes As Long, nNo As Long, nNA As Long
    nYes = 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 = "No" Then nNo = nNo + 1
            If c.Value = "NA" Then nNA = nNA + 1
        End If
        
    Next c
    If nYes = 0 Then
        DocScore = ""
    Else
        DocScore = Format((nYes) / (nYes + nNo + nNA), "Percent")
    End If
    
    
    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

  14. #14
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I think is better to use checkboxes instead of CBOs (without any VBA).
    So, the expression for the DocScore textbox (Controlsource) could be this:
    Code:
    =-([chk1]+[chk2]+[chk3]+[chk4]+[chk5]+[chk6])/6
    Even if I don't understand the need of the variance, the expression could be like this:
    Code:
    =-(IIf([chk1];[chk1];-0,05)
    +IIf([chk2];[chk2];-0,05)
    +IIf([chk3];[chk3];-0,05)
    +[chk4]+[chk5]+[chk6])/6
    (Replace the comma and the semicolon if needs with your system separators)
    Maybe need to replace the value 0,05 with the field name of the variance.

    Cheers,
    John
    Last edited by accesstos; 01-12-2021 at 04:32 PM. Reason: Expression correction

  15. #15
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    accesstos - Thank you for providing some help! So a couple questions...
    1) Can you help me understand the formula you wrote that accounts for the variance field? Is it saying - If checkbox1 (the Yes response) is checked then count or add 1 otherwise subtract the variance field then divide the total by 6?
    2) Do I just add another IF statement to account for scenarios where none of the "Yes" checkboxes are selected (avoiding the divide by 0 error). In my current code it is the If Then Else statement.

    As for the point of the variance - On some of these questions the end user would like to provide a percentage of how off the employee was on their estimate. So instead of getting 0 points for that quesiton in the review they want to use the variance to show how off they were while still providing them some credit. In the example I provided they would be 5% off.

    Thank you again for your time and for helping me! I greatly appreciate it!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating a score based on yes/no parameters
    By okieheart in forum Queries
    Replies: 5
    Last Post: 10-16-2018, 07:46 AM
  2. Replies: 4
    Last Post: 11-02-2017, 09:58 AM
  3. Calculating Percent of a Test Score
    By cohnhead in forum Queries
    Replies: 5
    Last Post: 11-21-2014, 03:22 PM
  4. Replies: 1
    Last Post: 06-24-2013, 02:24 AM
  5. Update Multiple Fields based on Selection (inTable)
    By aguestnga in forum Database Design
    Replies: 3
    Last Post: 11-03-2011, 04:17 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums