Results 1 to 15 of 15
  1. #1
    zero3ree is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2010
    Posts
    39

    Radio buttons to control color of box on form.

    I have a general question to ask before I even attempt this code. Can I create radio buttons in a table that calculate a color in a box on a form. The situation is I have 3 factors that decide whether the status of a project is Red, Green, or Yellow. I want to achieve this by have the user click the color as a radio button for each of the three. Then on a form a status box will show up either Red, Green, or Yellow depending on some criteria to be set. Before I try to do it I just want to make sure its possible.

    Thank you in advance.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,935
    I don't think you can have radio buttons in a table, but you can certainly have a field for color in a "status" table and use that to color your status box.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,594
    Need to use Conditional Formatting for this.

    Radio buttons can be on form, not table. A single radio button can be bound to field (probably check box is better) or multiple radio buttons can be in an
    OptionGroup control then the OptionGroup control can be bound to number field that saves status value.
    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.

  4. #4
    zero3ree is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2010
    Posts
    39
    Quote Originally Posted by June7 View Post
    Need to use Conditional Formatting for this.

    Radio buttons can be on form, not table. A single radio button can be bound to field (probably check box is better) or multiple radio buttons can be in an
    OptionGroup control then the OptionGroup control can be bound to number field that saves status value.
    Thanks for the quick response to both of you. I think what I am looking for is a mixture of both your solutions. I need to set up these OptionGroups for the three checks but ultimately someway I need the end result to be one box that displays the color of the status. Basically "Quality" can be R,G,Y; "Time" can be R,G,Y; and "Cost" can be R,G,Y in the table. On the "PSR" form one box displays the overall color of the job. One Red and the box is red but 2 greens and a yellow can be green and 2 yellows and a green will be yellow. To add to all that only one optiongroup is manual check the other two are calculated from comparing two numbers. If this seems confusing try being me with my tad bit over novice expertise trying to put this together for my boss.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,594
    Sounds like a fairly complex nested IIf or a custom function for calculating the overall status based on the 3 criteria. Do IIf expression or call a function in textbox ControlSource to calculate the overall status of R,G,Y. Then Conditional Formatting to display appropriate color dependent on the calculated value in textbox.
    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
    zero3ree is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2010
    Posts
    39
    I don't even want to pretend I know how to set this up. Any chance either of you could create a simple example for me to work off of? If not that's fine doesn't hurt to ask. I do appreciate the help you have given already.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,594
    I suggest you start exploring and experimenting. Instead of radio buttons and option group control could have a combobox with 3 value choices. Combobox is easier to understand. Then the other two criteria would be textboxes with calculations to produce the values.

    A simple IIf expression in the overall status textbox would be like:

    IIf([comboboxname]="R" Or [textboxname1]="R" Or [textboxname2]="R", "R", "G")

    So you can see how complex this will be for different color combinations. Use VBA to build a custom function that has input argument(s). The code could be like:
    Function GetOverallStatus(ColorCode) As String
    Select Case ColorCode
    Case "RRR"
    GetOverallStatus = "red"
    Case "GGR"
    GetOverallStatus = "green"
    Case "GRR"
    etc. continue this exercise in logic to get the desired output based on the input
    End Select
    End Function

    Call the function from textbox:
    =GetOverallStatus([comboboxname] & [textboxname1] & [textboxname2])

    Then use the ConditionalFormatting from the ribbon Design tab to set conditions for color display. There will be 3 conditions, one for each color, which is convenient because Access 2007 allows only 3 conditions.
    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.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,935
    Using a table:

    Click image for larger version. 

Name:	Status.jpg 
Views:	24 
Size:	168.9 KB 
ID:	11065
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    zero3ree is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2010
    Posts
    39
    Quote Originally Posted by June7 View Post
    I suggest you start exploring and experimenting. Instead of radio buttons and option group control could have a combobox with 3 value choices. Combobox is easier to understand. Then the other two criteria would be textboxes with calculations to produce the values.

    A simple IIf expression in the overall status textbox would be like:

    IIf([comboboxname]="R" Or [textboxname1]="R" Or [textboxname2]="R", "R", "G")

    So you can see how complex this will be for different color combinations. Use VBA to build a custom function that has input argument(s). The code could be like:
    Function GetOverallStatus(ColorCode) As String
    Select Case ColorCode
    Case "RRR"
    GetOverallStatus = "red"
    Case "GGR"
    GetOverallStatus = "green"
    Case "GRR"
    etc. continue this exercise in logic to get the desired output based on the input
    End Select
    End Function

    Call the function from textbox:
    =GetOverallStatus([comboboxname] & [textboxname1] & [textboxname2])

    Then use the ConditionalFormatting from the ribbon Design tab to set conditions for color display. There will be 3 conditions, one for each color, which is convenient because Access 2007 allows only 3 conditions.
    Thanks, This is very helpful. I am going to try to work on this today at work.

  10. #10
    zero3ree is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2010
    Posts
    39
    Quote Originally Posted by pbaldy View Post
    Using a table:

    Click image for larger version. 

Name:	Status.jpg 
Views:	24 
Size:	168.9 KB 
ID:	11065
    Thanks, I am going to experiment with this and see if I can get it to work for my situation. I appreciate the help.

  11. #11
    zero3ree is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2010
    Posts
    39
    Quote Originally Posted by June7 View Post
    I suggest you start exploring and experimenting. Instead of radio buttons and option group control could have a combobox with 3 value choices. Combobox is easier to understand. Then the other two criteria would be textboxes with calculations to produce the values.

    A simple IIf expression in the overall status textbox would be like:

    IIf([comboboxname]="R" Or [textboxname1]="R" Or [textboxname2]="R", "R", "G")

    So you can see how complex this will be for different color combinations. Use VBA to build a custom function that has input argument(s). The code could be like:
    Function GetOverallStatus(ColorCode) As String
    Select Case ColorCode
    Case "RRR"
    GetOverallStatus = "red"
    Case "GGR"
    GetOverallStatus = "green"
    Case "GRR"
    etc. continue this exercise in logic to get the desired output based on the input
    End Select
    End Function

    Call the function from textbox:
    =GetOverallStatus([comboboxname] & [textboxname1] & [textboxname2])

    Then use the ConditionalFormatting from the ribbon Design tab to set conditions for color display. There will be 3 conditions, one for each color, which is convenient because Access 2007 allows only 3 conditions.
    So I have been really busy so I was trying to do this in my spare time at home. I created a function but I can't figure out how to add it to my query or form. How do I go about this. I imagine it needs to be part of the form to add the

    =GetOverallStatus([TIME_STAT] & [COST_STAT] & [Quality_PSR]) to the text box. Here is my function. The one you did with all options added.

    GetOverallStatus(ColorCode) As String
    Select Case ColorCode
    Case "Red,Red,Red"
    GetOverallStatus = "Red"
    Case "Red,Green,Red"
    GetOverallStatus = "Red"
    Case "Red,Red,Yellow"
    GetOverallStatus = "Red"
    Case "Green,Green,Red"
    GetOverallStatus = "Red"
    Case "Green,Red,Red"
    GetOverallStatus = "Red"
    Case "Green,Red,Yellow"
    GetOverallStatus = "Red"
    Case "Red,Red,Green"
    GetOverallStatus = "Red"
    Case "Red,Green,Yellow"
    GetOverallStatus = "Red"
    Case "Red,Green,Green"
    GetOverallStatus = "Red"
    Case "Green,Green,Yellow"
    GetOverallStatus = "Yellow"
    Case "Green,Green,Green"
    GetOverallStatus = "Green"
    End Select
    End Function
    End Sub

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,594
    The parameters in the Case structure have commas. So need to include commas in concatenating the value for the function argument. Call the function from ControlSource property of a textbox.
    =GetOverallStatus([TIME_STAT] & "," & [COST_STAT] & "," & [Quality_PSR])

    Also, need to include the word 'Function' in the function declaration line. See my example. The function can be placed in a general module or behind form. Remove the End Sub line.
    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
    zero3ree is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2010
    Posts
    39
    Thanks for your help. That worked great. Another snag was thrown at me today. I need to add to the if statements that provide the color to the TIME_STAT.

    It need to be red, yellow, or green depending on the percentage below or above the actual number is. I wrote this if statement but it doesn't like it.

    TIME_STAT: IIf([TOT_EST]<=[ACT10PLUS]>=[ACT10MIN],"Green","Yellow") Or IIf([TOT_EST]<=[ACT20PLUS]>=[ACT20MIN],"Yellow","Red")

    I am not sure how to do this in Access. Bottom line is that If the actual is in the range of 10% either way it can be green, within 20% it can be yellow, after that it is red. I put this formula in and it gave me all "-1" as my result. I think I am looping somehow. Any suggestions on a more simple formula?

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,935
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    zero3ree is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jan 2010
    Posts
    39
    Quote Originally Posted by pbaldy View Post
    Yeah I added it here because it was part of the same issue but figured it should be addressed as a separate one after the fact. Thanks you for the forward.

    This issue was resolved in the continued post.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Radio Buttons
    By maguyver in forum Access
    Replies: 3
    Last Post: 07-07-2012, 10:39 AM
  2. Using Radio Buttons on a Form
    By DDEB in forum Forms
    Replies: 2
    Last Post: 05-19-2012, 07:32 AM
  3. Radio Buttons
    By ccordner in forum Forms
    Replies: 1
    Last Post: 01-27-2012, 09:56 AM
  4. Radio Buttons
    By neil123williams in forum Forms
    Replies: 8
    Last Post: 11-11-2011, 03:38 PM
  5. what happened to radio buttons?
    By judomum in forum Forms
    Replies: 1
    Last Post: 10-09-2011, 11:58 AM

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