Dynamically change option group button fore color

[tuxalot]

Here's my button code, and I want to see if I can do something different so I don't have to repeat the same code for each of my 14 buttons in my option group:

Private Sub grpS1_LostFocus()

    Dim intCount As Integer
    Dim lngRed As Long, lngBlack As Long
    lngRed = RGB(186, 20, 25)
    lngBlack = RGB(64, 64, 64)
    
    intCount = DCount("[RspnsID]", "qxtbChkSectionsComplete")
    
    If intCount > 0 Then
    ' missing questions, change button ForeColor to red
        Me!grpS1.ForeColor = lngRed
        Me!grpS1.PressedForeColor = lngRed
        Me!grpS1.HoverForeColor = lngRed
    Else
        ' questions complete, change button ForeColor to black
        Me!grpS1.ForeColor = lngBlack
        Me!grpS1.PressedForeColor = lngBlack
        Me!grpS1.HoverForeColor = lngBlack
    End If

End Sub

The buttons in the option group "grpSections" are named grpS1 - grpS14 and represent sections on my questionnaire. The query above uses the value of the option group as criteria with [Forms]![frmMain].[Controls]![grpSections].[value] to calculate the DCount on RspnsID for each section.

So it all boils down to this. Is there a way to put code in the option group's on click or after update event to grab the value of the button that has just lost focus, to run the query above and change THAT button fore color? So for example as a user goes from section 1 to 2 by clicking the grpS2 button the query would check for complete questions in section 1 and if there are questions missed then change the fore color for grpS1.

As always, thanks everyone for your help!

 

[MajP]

Select all the options. In the click event property put
=changeColor()

put this in the form module

Public Function changeColor()
    Dim ctrl As Access.Control
    Dim intCount As Integer
    Dim lngRed As Long, lngBlack As Long
    lngRed = RGB(186, 20, 25)
    lngBlack = RGB(64, 64, 64)
    
    Set Control = Screen.ActiveControl
    intCount = DCount("[RspnsID]", "qxtbChkSectionsComplete")
    
    If intCount > 0 Then
    ' missing questions, change button ForeColor to red
        With ctrl
          .ForeColor = lngRed
          .PressedForeColor = lngRed
          .HoverForeColor = lngRed
       End With
    Else
        ' questions complete, change button ForeColor to black
        With ctrl
          .ForeColor = lngBlack
          .PressedForeColor = lngBlack
          .HoverForeColor = lngBlack
        End With
    End If
End Function

 

[tuxalot]

Thanks for the response MajP. Not quite working. I put this in the option groups click event

Private Sub grpSections_Click()
Call changeColor

At the below line I am getting an error "object doesn't support this property or method"

Public Function changeColor()
Dim ctrl As Access.Control
Dim intCount As Integer
Dim lngRed As Long, lngBlack As Long
lngRed = RGB(186, 20, 25)
lngBlack = RGB(64, 64, 64)

Set ctrl = Screen.ActiveControl
intCount = DCount("[RspnsID]", "qxtbChkSectionsComplete")

If intCount > 0 Then
' missing questions, change button ForeColor to red
With ctrl
.ForeColor = lngRed '<<<<<<<<<<<< error here
.PressedForeColor = lngRed
.HoverForeColor = lngRed
End With
Else
' questions complete, change button ForeColor to black
With ctrl
.ForeColor = lngBlack
.PressedForeColor = lngBlack
.HoverForeColor = lngBlack
End With
End If
End Function

suggestions?

 

[MajP]

However I did not realize you said option group. That code will not work with an option because the active control is the optiongroup not the individual option. You should capture the afterupdate event of the optionGroup and use a select case to determine the option selected..

Private Sub optGrpOne_AfterUpdate()
  Select Case Frame2.Value
  Case 1
    call changeColor(me.grpS1)
  Case 2
    call changeColore(me.grpS2)
  ...
  Case 14
    call changeColor(me.grps14
  End Select
End Sub

Public Sub changeColor(ctrl As Access.Control)
    Dim intCount As Integer
    Dim lngRed As Long, lngBlack As Long
    lngRed = RGB(186, 20, 25)
    lngBlack = RGB(64, 64, 64)
    
    intCount = DCount("[RspnsID]", "qxtbChkSectionsComplete")
    
    If intCount > 0 Then
    ' missing questions, change button ForeColor to red
        With ctrl
          .ForeColor = lngRed
          .PressedForeColor = lngRed
          .HoverForeColor = lngRed
       End With
    Else
        ' questions complete, change button ForeColor to black
        With ctrl
          .ForeColor = lngBlack
          .PressedForeColor = lngBlack
          .HoverForeColor = lngBlack
        End With
    End If
End Sub

 

[MajP]

However you can not change the button color, you can only change the properties listed. I think you are limited to the border color.

 

[tuxalot]

Thanks MajP but I really only want to change the button ForeColor which changes the text color only. These properties are available in the property sheet when I select a button so I wonder why it won't work?

 

[MajP]

Like I said the first method will not work because when you click in an option group the activecontrol is the frame not the individual option. The frame does not have a forecolor property. You would have to do something like the second method. I assume the options are toggle buttons, which does have a forecolor property. In 2003 they do not have a pressed forecolor or hover. But they may have in a later version.

 

[tuxalot]

I'm an idiot. I used your other code and it's working!

Thanks again for the assistance.

 

[TheAceMan1]

How are ya tuxalot . . .

Just grooming the code:

[blue]Public Sub changeColor(ctrl As Access.Control)
   Dim intCount As Integer, lngColor As Long
   
   lngColor = RGB(64, 64, 64) [green]'Black[/green]
   intCount = DCount("[RspnsID]", "qxtbChkSectionsComplete")
   If intCount > 0 Then lngColor = RGB(186, 20, 25) [green]'Red[/green]
   
   With ctrl
     .ForeColor = lngColor
     .PressedForeColor = lngColor
     .HoverForeColor = lngColor
   End With

End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[tuxalot]

Hiya Ace. Thanks for the pointers. I can use all I can get Much cleaner now.

Have a great Sunday!