Use a Custom Color in Conditional Formatting

[wojo]

I would love to use a custom color as a conditional format. I can only
'see' the standard colors when I choose the color option.

Can other colors be used for Conditional formatting? If so, how?
Thanks in advance.

Jo

 

[Allen Browne]

You can programmatically set any RGB value.

For example if Form4 is open in design view, and has a basic color set for
the text, you could open the Immediate Window (Ctrl+G) and change teh
ForeColor of the first condition on the Amount text box with:

Forms!Form4!Amount.FormatConditions(0).ForeColor = RGB(64, 128, 196)

using any value between 0 and 255 for red, green, and blue respectively.

 

[wojo]

Allen, first I want to thank you for all your wonderful information. I
recently solved a problem with giving edit rights to some fields within
a record. I used the information on your web site. Thanks to you,
problem solved!

As for this problem, I must admit that I don't understand the
instructions. I am a self taught Access user and I need step by step
instructions.

Here's as many details as I can provide.
Form "leave requests" Field "today's date" Field "CancelledRequest"

I have set Today's Date (and other fields) to change color with
Conditional Format
Expression is [CancelledRequest]="X"

The color I want to use, is the background color of the form, which is
9666156

If I need to use programming code to accomplish this, I need to know
where the code should go.

I have about 6 fields that I would like to change to the background
color if CancelledRequest is X'd. This will make those entries less
noticable to the user, however, still allowing them to see the orginal
request.

Thanks again. I hope I can figure this out.

WoJo

 

[Allen Browne]

You only need to do this once (for any field you want to set). Then save the
form. As a result, you do not need to type code into anywhere where it is
kept.

1. Open the form in design view.

2. If you have not already done so, select the [today's date] text box, and
choose Conditional Formatting (CF) from the Format menu. Set the condition
you want, and click any color in the bucket - say, red. Close the CF dialog.

3. Press Ctrl+G to open the Immediate Window.

4. Type this into the Immediate window:
? Forms![leave requests]![today's date].FormatConditions(0).BackColor
When you press Enter, Access should give you the number for the color you
chose above, e.g. 255 for red.

5. Now that you know the expression is working, you can assign the color you
want by entering:
Forms![leave requests]![today's date].FormatConditions(0).BackColor =
9666156
You can then repeat the expression above to check that it now has the new
color.

6. Save the form. In future, the CF on this text box will use this
background color.

7. Repeat for other text boxes.

 

[wojo]

Allen, I was able to follow your instructions... however... here's what
happened.

I opened the form "Leave Requests", Selected the Field "Today's Date",
set the conditional format (condition 1) to RED.
Used the Ctrl G, intermediate window opens at bottom of screen.
I entered...

? Forms![leave requests]![today's date].FormatConditions(0).BackColor

When I press Enter, I get an error message 2465 Microsoft cannot find
the field 'Today's Date' referred to in your expression. I wish I
hadn't named the field with an apostrophe and a space, but it's too
late to change that. Many parts of the program referr to this field.

I tried another field in the Form, EnteredByInitials

This one worked.

Here is the list from my program. Some, but not all get error code
2465 when I press 'enter' to go to the next line.

Forms![leave
requests]![EnteredByInitials].FormatConditions(1).BackColor=9666156
Forms![leave requests]![Employee
Initials].FormatConditions(1).BackColor=9666156 [this one gets the
error]
Forms![leave
requests]![Approved].FormatConditions(1).BackColor=9666156 [this one
gets the error]
Forms![leave requests]![Denied].FormatConditions(1).BackColor=9666156
Forms![leave
requests]![CancelledRequest].FormatConditions(1).BackColor=9666156
Forms![leave requests]![From].FormatConditions(0).BackColor=9666156
Forms![leave requests]![To].FormatConditions(0).BackColor=9666156
Forms![leave requests]![Today's
Date].FormatConditions(0).BackColor=9666156 [this one gets the error]

This has me 'baffled'. I am using a form that HAS No blocking (per
field). I don't know what other information may help you.

As I said, some of the fields conditional statements work as I expect.

 

[Allen Browne]

Right-click the text box (in form design view), and choose Properties.
On the Other tab of the Properties box, is the Name property.

Whatever is in that property is the name you need to use inside the square
brackets in the immediate window.

 

[wojo]

I did that. I even used copy/paste to ensure no spelling errors

Any other ideas why Access doesn't recognize the field name on some
fields?

I have high hopes that this can be fixed.

Thanks, Jo

 

[Allen Browne]

Are you absolutely certain that you are using the name of the *control*? It
may be different to the name of the field the control is bound to (its
ControlSource.)

If so, you have some corruption in your database, and Name AutoCorrect would
be the first suspect. Try this sequence to recover your database:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
and the code syntax is compilable.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

 

[wojo]

Allen, you asked, "Are you absolutely certain that you are using the
name of the *control*? It
may be different to the name of the field the control is bound to (its
ControlSource.) "

I have not had the opportunity to try your solution yet, but could you
explain the difference between the name of the filed and "control"?
I'm sure this is probably my problem, maybe they are not the same name.

Thanks in advance,

Jo

 

[Allen Browne]

In form design view, right-click the control and choose Properties.

The Name property is on the Other tab of the Properties box.
The name of the field it is bound to is in the Control Source property, on
the Data tab.

 

[wojo]

Thank you so much. THAT was the problem. I was NOT using the name
property, I was using the control name.

Everything works, just like you said it would.

Thank you for helping me!

Jo