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

    Loop through comboxes on form to calculate score but skip 2 comboxes

    Hello,
    I have a form with 14 comboboxes. 12 of the comboboxes are used to calculate a total score. The other two comboboxes are unbound and are named cboStatus and cboEmployee. I need help modifying the current code I have that loops through the comboboxes on the form and calculates the score to ignore the cboStatus and cboEmployee comboboxes.

    I really appreciate any help you are able to give me!

    Code:
    Private Sub btnCalculate_Click()
    Dim target_control As Control
    Dim control_value As Integer
    Dim control_count As Integer
    control_value = 0
    control_count = 0
    
    For Each target_control In Me.Controls
        If TypeName(target_control) = "ComboBox" Then
            If Not target_control.Value = "N/A" Then
                            MsgBox (target_control.Name & ", " & target_control.Value)
                control_value = control_value + CInt(target_control.Value)
                control_count = control_count + 1
            End If
        End If
    Next
    txtPotential = control_count * 4
    txtScore = Format((control_value / txtPotential), "Percent")


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could exclude them by name but it would be more dynamic to use the Tag property of the controls you want included. Only test the ones with the appropriate value, like:

    If target_control.Tag= "IncludeMe" Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    This should work:

    Code:
    Private Sub btnCalculate_Click()
    Dim target_control As Control
    Dim control_value As Integer
    Dim control_count As Integer
    control_value = 0
    control_count = 0
    
    
    For Each target_control In Me.Controls
        Select Case target_control.Name
            case "cboStatus", "cboEmployee"
            case else
                If TypeName(target_control) = "ComboBox" Then
                    If Not target_control.Value = "N/A" Then
                                    MsgBox (target_control.Name & ", " & target_control.Value)
                        control_value = control_value + CInt(target_control.Value)
                        control_count = control_count + 1
                    End If
                End If
        End Select        
    Next
    txtPotential = control_count * 4
    txtScore = Format((control_value / txtPotential), "Percent")
    Last edited by davegri; 02-24-2020 at 11:38 AM. Reason: format

  4. #4
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Great suggestion! Thank you so much for your help and quick response!

  5. #5
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Ok - so I tried both suggestions and with both of them the error message goes away! Thank you for that! However the score is not calculating correctly.

    If there are 11 comboboxes and 4 are scored as "1" and 7 are scored as 4 then the total score should be 72.2% however it is populating 100%

    Any idea what I am missing? Thank you again for being willing to help me!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Either set a breakpoint and step through the code to see how those values are being incremented or add this after they've been calculated and see what they're ending up with:

    Debug.Print txtPotential
    Debug.Print txtScore
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Ok, I added in the Debug code you provided but nothing displayed I also used a messagebox to display the value that in each of the comboboxes as it loops through them and all of the values were correct. The txtPotential field is calculating correctly so from what I can gather the issue is at this point of the code "
    txtScore = Format((control_value / txtPotential), "Percent")

    Or

    control_value = control_value + CInt(target_control.Value)

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    In your form, does txtScore allow decimal places? It needs at least 3 because 72.7% is stored as .727

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The debug values would go here:

    http://www.baldyweb.com/ImmediateWindow.htm

    Did you check that the aggregation into control_value was happening correctly? What are the two values at the point of dividing for a percentage? Does the variable/control where you're putting the percentage have the appropriate data type and format? It could be calculating correctly but displaying incorrectly.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Thank you for that link - The values that show in the immediate window for txtPotential is 44 (correct) and txtScore is 0 (incorrect). I added a text box that will display the control_value and it displays 18 (based on my current cbo selections this is correct.) So it should be dividing 18 by 44.

    The txtScore format is set to Percent with auto decimal
    In the field the txtScore box is bound to is set to Number, long integer with Percent and decimal 2

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What does txtPotential contain? Long Integer can't hold a decimal value, which a percentage is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    txtPotential is set to General Number. It is unbound.

    I tried changing the txtscore in the table from Long Integer to Byte and Decimal. Nothing changed.

    I am so sorry to be such a pain. Im sure I am missing something silly here and I really appreciate your help!

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    In the field the txtScore box is bound to is set to Number, long integer with Percent and decimal 2
    That's the problem.

    As Paul and I have stated, integer and long cannot hold decimal places by definition. The table field that txtScore is bound to needs to be single or double.

  14. #14
    jbeets is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2020
    Posts
    94
    Ok - I changed the format to Double and it works! You have no idea how thankful I am that you helped me!!!

  15. #15
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Glad you got it working. And I'll bet you remember the quirks of certain data types!

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

Similar Threads

  1. Insert mutliple comboxes
    By Mike70 in forum Queries
    Replies: 3
    Last Post: 01-23-2016, 07:15 AM
  2. Replies: 4
    Last Post: 10-14-2014, 09:28 AM
  3. Using a Do loop to calculate Average
    By pmpursley in forum Access
    Replies: 2
    Last Post: 01-17-2014, 06:04 PM
  4. Replies: 1
    Last Post: 06-24-2013, 02:24 AM
  5. Form Printing Skip.
    By cap.zadi in forum Forms
    Replies: 2
    Last Post: 11-28-2011, 10:41 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