• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2579
  • Last Modified:

Conditional Formatting of Toggle Button in Access

I haven't used Access in several years and have recently inherited an Access 2003 "Call In Log" application that has 22 toggle buttons on the front page. Each button is bound to a yes/no field in the database. The users click each one of these buttons that apply to the reason the customer is calling. They are not in an Option Group. By default, as you scroll through records in a form, Access toggle buttons show depressed when the record is true in the table. Now the user  wants to make the "depressed" look more obvious to all of the users; by either changing the font color, boldness, back color, etc. I've tried putting this code in the OnOpen event for the form:

Private Sub Form_Open(Cancel As Integer)
   
    If [How to Order].Value = True Then
        [How to Order].ForeColor = vbRed
    Else: [How to Order].ForeColor = vbBlack
    End If
   
End Sub

That does change the color of the text on this particular button, but it changes for every record from that point forward whether the button is selected or not. What event is triggered when a record changes on a form? Would that be the place to put this code?

I also wanted to try the Conditional Formatting for each button, but Conditional Formatting is greyed out in the Format menu.
0
Carla Romere
Asked:
Carla Romere
  • 5
  • 5
  • 2
1 Solution
 
Dale FyeCommented:
When a record changes the Form_Current event fires, but that is when you get to the new record, not before you leave the old record.
0
 
Dale FyeCommented:
You could use a loop to loop through the controls on the form to set the colors of all of the controls using code similar to:

Private Sub Form_Current

    Dim ctrl as control

    For each ctrl in me.controls

        if ctrl.controltype = 23  '<= look up the control type # for toggle button
            ctrl.ForeColor = iif(Ctrl.Value = true, vbRed, vbBlack)
        End If

    Next

End sub
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Well, that makes perfect sense, however, it doesn't appear to do anything. Here is the code I put in the Microsoft Office Access Class Objects > Form_Call_In Form:

Private Sub Form_Current()

Dim ctrl As Control

    For Each ctrl In Me.Controls

        If ctrl.ControlType = acToggleButton Then
            ctrl.ForeColor = IIf(ctrl.Value = True, vbRed, vbBlack)
        End If

    Next
End Sub

Open in new window


I also tried this code:

Private Sub Form_Current()
    Dim ctrl As Control

    For Each ctrl In Me.Controls
        With ctrl
            Select Case .ControlType
                Case acToggleButton
                    If .Value = -1 Then
                .ForeColor = vbRed
                Else
                .ForeColor = vbBlack
        End If
    Next
End Sub

Open in new window

That didn't work either. The buttons do show the default "depressed" if selected as I scroll through, but the fore color doesn't change with it.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
IrogSintaCommented:
I don't believe you can iterate through the controls using the OnCurrent event.  I believe that changing the forecolor of one toggle button in one record will affect all the records in the continuous form.

If you use Access 2007 or later, you have the advantage of newly designed toggle buttons that are more obvious when pressed.  However, you can try workaround by simulating toggle buttons.  

Here are the steps in creating one.  Let's say you have a Yes/No field called Completed:

1.

Add a textbox to your continuous form.

2.

Insert ="Completed" into the ControlSource of your textbox. Be sure to include the equal sign here.

3.

Change the BackColor of this textbox to grey and change the SpecialEffect to Raised.

4.

Add conditional formatting to this textbox using Expression Is and for your condition use [Completed]=True.  Select a red color font for this condition.

5.

Add a button to your continuous form and put it right on top of your textbox and change it's Transparent property to True.  Then give it an OnClick event like so:
Private Sub btnCompleted_Click()
    [Completed] = Not [Completed]
End Sub

Open in new window

0
 
Dale FyeCommented:
Sorry I didn't reply sooner, I was traveling yesterday and only have my iPad.

It worked for me, take a look at the attached.

Personally, I prefer checkboxes to represent Yes/No fields over toggles.
Toggles.accdb
0
 
IrogSintaCommented:
Ah, I misunderstood.  I thought this was all on a continuous form.
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Okay fyed, I put that code in and it's still not working. The db you sent works perfectly and it looks like it should work on this. I get this error when the form is opened:

Run-time error '2427':
You entered an expression that has no value.

When I click on Debug, the line that's not working is this one:

    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If ctrl.ControlType = acToggleButton Then
            ctrl.ForeColor = IIf(ctrl.Value = True, vbRed, vbBlack)       <<<<======
        End If
    Next
0
 
Dale FyeCommented:
It sounds like you have a toggle button that is not bound to a field.  In that case, the Value would probably be NULL, which might cause this problem.  Check the ControlSource for each of the toggles.

When you get that error, type the following in the immediate window:

?ctrl.Name

This should tell you which of the controls is causing the problem.
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Okay - stand by.
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
I really hate taking over projects that someone else created. There are two toggle buttons on the page that are populating one field in the database. I haven't figured out yet how he has it programmed, but I think this would have been much better served by being option buttons because there can only be one. Let me play with that a few and see if I can change that without messing up anything else he has programmed into this app.
0
 
Carla RomereDirector of Information TechnologyAuthor Commented:
Absolutely perfect! Once I corrected the field types, etc., and put option buttons where they should be as opposed to toggle buttons, the code worked beautifully! Thank you so much.
0
 
Dale FyeCommented:
glad I could help.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now