?
Solved

Conditional Formatting of Toggle Button in Access

Posted on 2013-05-16
12
Medium Priority
?
2,350 Views
Last Modified: 2013-05-20
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
Comment
Question by:Carla Romere
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39171941
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39171950
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
 

Author Comment

by:Carla Romere
ID: 39172053
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 29

Expert Comment

by:IrogSinta
ID: 39173538
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
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39174493
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39174856
Ah, I misunderstood.  I thought this was all on a continuous form.
0
 

Author Comment

by:Carla Romere
ID: 39181180
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39181417
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
 

Author Comment

by:Carla Romere
ID: 39181428
Okay - stand by.
0
 

Author Comment

by:Carla Romere
ID: 39181489
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
 

Author Comment

by:Carla Romere
ID: 39181656
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39181661
glad I could help.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question