Solved

Conditional Formatting of Toggle Button in Access

Posted on 2013-05-16
12
2,078 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:Hers2keep
  • 5
  • 5
  • 2
12 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 47

Expert Comment

by:Dale Fye (Access MVP)
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:Hers2keep
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
 
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Hers2keep
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 47

Expert Comment

by:Dale Fye (Access MVP)
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:Hers2keep
ID: 39181428
Okay - stand by.
0
 

Author Comment

by:Hers2keep
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:Hers2keep
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 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39181661
glad I could help.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now