[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Conditional Formatting of Toggle Button in Access

Posted on 2013-05-16
12
Medium Priority
?
2,480 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
  • 5
  • 5
  • 2
12 Comments
 
LVL 49

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 49

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 49

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 49

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 49

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 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