Radio buttons to control color of box on form.

[zero3ree]

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.

[pbaldy]

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.

[June7]

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.

[zero3ree]

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.

[June7]

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.

[zero3ree]

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.

[June7]

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.

[pbaldy]

Using a table:

[zero3ree]

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.

[zero3ree]

Using a table:

Thanks, I am going to experiment with this and see if I can get it to work for my situation. I appreciate the help.

[zero3ree]

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

[June7]

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.

[zero3ree]

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?

[pbaldy]

Apparently continued here:

https://www.accessforums.net/access/...ent-32625.html

[zero3ree]

Apparently continued here:

https://www.accessforums.net/access/...ent-32625.html

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.