How to change option group button ForeColor dynamically?

[tuxalot]

Another quick question for ya...

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!

[ADezii]

1. To change the ForeColor of all ToggleButtons within the grpSections Option Group:
   

   Dim ctl As Control
    
   For Each ctl In Me.Controls
     If ctl.ControlType = acToggleButton And Left$(ctl.Name, 4) = "grpS" Then
       ctl.ForeColor = vbBlack
     End If
   Next

2. I couldn't figure out an easy way to accomplish your second question, but that doesn't mean that one does not exist. I devised what I feel is a simple Method of retaining a Reference to the Last Toggle Button pressed, and here it goes:
   1. Declare a Form Level variable to hold a Reference to the Last Toggle Button pressed:
      

      Private ctlLast As ToggleButton

   2. In the LostFocus() Event of each Toggle Button in the Option Group (2nd Button used for Demo), place the following line of Code to explicitly refer to that Button:
      

      Private Sub grpS2_LostFocus()
        Set ctlLast = Me!grpS2
      End Sub

   3. To now Change the ForeColor Property of the Last Toggle Button pressed in the Option Group:
      

      ctlLast.ForeColor = vbRed

3. As I stated earlier, an easier approach may exist, so keep monitoring this Thread.
4. Any questions, feel free to ask.

[tuxalot]

Oops. spoke too soon. Seems to change all to red not taking into account if there are missed questions. Testing, be back in a bit with more.


Working now. Set this as a public variable:

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 lngRed
        With ctrl
          .ForeColor = lngRed
          .PressedForeColor = lngRed
          .HoverForeColor = lngRed
       End With
    Else
        ' questions complete, change button ForeColor to lngBlack
        With ctrl
          .ForeColor = lngBlack
          .PressedForeColor = lngBlack
          .HoverForeColor = lngBlack
        End With
    End If
End Sub
 

Then, in the option groups after update event, this:

 
    Select Case Me.grpSections.Value
        Case 1
            Call changeColor(Me.grpS1)
        Case 2
            Call changeColor(Me.grpS2)
        Case 3
            Call changeColor(Me.grpS3)
        .
        .
        .
           to case 15
    End Select
 

Then in the forms open event, this, to change the default color to black (also put this in a few buttons on the form when changing records):

    Dim intCount As Integer
    Dim ctl    As Access.Control
    Dim lngRed As Long, lngBlack As Long
 
    lngRed = RGB(186, 20, 25)
    lngBlack = RGB(64, 64, 64)
 
    intCount = DSum("intSections", "qDSections2")
    For Each ctl In Me.Controls
        If TypeOf ctl Is ToggleButton Then
            If ctl.Tag > intCount Then
                ctl.Visible = False
            Else
                ctl.Visible = True
                With ctl
                    .ForeColor = lngBlack
                    .PressedForeColor = lngBlack
                    .HoverForeColor = lngBlack
                End With
            End If
        End If
    Next ctl
 

So on to the next challenge!

[tuxalot]

ok. Code is updating the fore color but it seems to change from black to red with sections that have unanswered questions and when all the questions are answered you have to click back through each section to get the color to switch from red (missed questions) to black. So maybe a combo approach will do the trick.

Private variable at form level set as such:

Private ctlLast As ToggleButton

... code for the option group after update event:

Private Sub grpSections_AfterUpdate()  
    Call changeColor(ctlLast)
End Sub
 

... button lost focus events (on each button)

Private Sub grpS1_LostFocus()
  Set ctlLast = Me!grpS1
End Sub
 

... and the function

Public Function changeColor(ctlLast)
    'Dim ctlLast As ToggleButton
    Dim intCount As Integer
    Dim lngRed As Long, lngBlack As Long
    lngRed = RGB(186, 20, 25)
    lngBlack = RGB(64, 64, 64)
 
    Debug.Print ctlLast '<<<<< error here, "entered expression that has no value"
 
    intCount = DCount("[RspnsID]", "qxtbChkSectionsComplete")
 
    If intCount > 0 Then
    ' missing questions, change button ForeColor to lngRed
        With ctlLast
          .ForeColor = lngRed
          .PressedForeColor = lngRed
          .HoverForeColor = lngRed
       End With
    Else
        ' questions complete, change button ForeColor to lngBlack
        With ctlLast
          .ForeColor = lngBlack
          .PressedForeColor = lngBlack
          .HoverForeColor = lngBlack
        End With
    End If
End Function
 

Seems variable has no value?

[ADezii]

Works fine from this end, are you sure that you are setting the Value of ctlLast in the 'LostFocus()' Event and for each Toggle Button grpS1 thru grpS14?

[tuxalot]

yep. btw, I only have some of the toggles visible when the form loads, depending on how many sections I have in the current survey. I don't think this would be causing the issue. This code for the forms on open event is

    Dim intCount As Integer
    Dim ctl    As Access.Control
    Dim lngRed As Long, lngBlack As Long
 
    lngRed = RGB(186, 20, 25)
    lngBlack = RGB(64, 64, 64)
 
    intCount = DSum("intSections", "qDSections2")
    For Each ctl In Me.Controls
        If TypeOf ctl Is ToggleButton And Left$(ctl.Name, 4) = "grpS" Then
            If ctl.Tag > intCount Then
                ctl.Visible = False
            Else
                ctl.Visible = True
                With ctl
                    .ForeColor = lngBlack
                    .PressedForeColor = lngBlack
                    .HoverForeColor = lngBlack
                End With
            End If
        End If
    Next ctl
 

[ADezii]

1. Is the code in the Form's Open() Event working as it should?
2. intSections in the DSum() Function appears to be a Variable Name, is it an actual Field contained in qDSections2?
   

   intCount = DSum("intSections", "qDSections2")

[tuxalot]

Yes it is working. I've tried shutting down the db with red colored buttons and it opens with black. Also, I've added/subtracted sections on my survey and the representative sections buttons come and go as expected.

So the last code I posted is working on your end? Very strange. I am running A2010 (but tested it on 07 as well with same results).

Here's the two queries mentioned:

qDSections1

SELECT tblQuestions.SectionID, Count(tblQuestions.SectionID) AS CountOfSectionID
FROM tblQuestions
WHERE (((tblQuestions.QstnIsActive)=True))
GROUP BY tblQuestions.SectionID;
 

qDSections2

SELECT Count(*) AS intSections
FROM qDSections1;
 

[tuxalot]

Maybe it is where I placed the variable reference. This code

Private ctlLast As ToggleButton

I placed in the declaration section of Form_frmMain (just above Option Explicit). Should it not be Public instead?

I CAN pass the tag value of the button as a string variable so maybe I can work from that angle. Not sure so any help is appreciated.

[tuxalot]

All is well now. I ditched the function and instead placed this code in the groups after update event

    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 lngRed
        With ctlLast
            .ForeColor = lngRed
            .PressedForeColor = lngRed
            .HoverForeColor = lngRed
        End With
    Else
        ' questions complete, change button ForeColor to lngBlack
        With ctlLast
            .ForeColor = lngBlack
            .PressedForeColor = lngBlack
            .HoverForeColor = lngBlack
        End With
    End If
 
 

The buttons themselves have this as their lost focus event as you provided

Private Sub grpS1_LostFocus()
    Set ctlLast = Me.grpS1
End Sub
 

and this as the variable declaration

Private ctlLast As ToggleButton

Thanks for the help once again ADezii.

[tuxalot]

arghh!

So now I need to get the VALUE of ctlLast to pass it as a criteria in my query.

intCount = DCount("[RspnsID]", "qxtbChkSectionsComplete")
 
    If intCount > 0 Then
        ' missing questions, change button ForeColor to lngRed
        With ctlLast
            .ForeColor = lngRed
            .PressedForeColor = lngRed
            .HoverForeColor = lngRed
        End With
    Else
        ' questions complete, change button ForeColor to lngBlack
        With ctlLast
            .ForeColor = lngBlack
            .PressedForeColor = lngBlack
            .HoverForeColor = lngBlack
        End With
    End If

Right now, the query gets it's criteria value from

[Forms]![frmMain].[Controls]![grpSections].[value]

so it is the current value of the control, not the last value.

Seems I'm chasing my tail ;)

[tuxalot]

Still chasing, but getting somewhere I think. After some study I settled on the following which works but seems clunky. Let me know what you think.

In the lost focus of each option button I put this:

Private Sub grpS1_LostFocus()
    Set ctlLast = Me.grpS1 'grabs the name of the button
    intLastValue = Me.grpSections.Value 'grabs the value of the button
    setLastValue intLastValue
End Sub
 

Then added this:

Option Compare Database
Private intLastValue As Integer
Public Sub setLastValue(Value As Integer)
    intLastValue = Value
End Sub
 

And this:

Public Function GetLastValue()
    GetLastValue = intLastValue
End Function
 

Then put this in as a criteria in my query:

GetLastValue()

The after update event for the option group remained unchanged and is shown above.

This is WAY over my head but I'm beginning to see the light. Seems like I've got an extra step in here somewhere.

Nite all.