Conditional Formatting on non-numerical value

Hi there

I apologise for my lack of understanding about Access 2003, I'm still
learning and hoping you can help make me better!

I have designed a form, and need to conditionally format one of the controls
based on its own contents. I have managed to make it work when I am working
with numerical vaules, but can't seem to get my head around making it work
with text inputs.

Effectively the form displays projects. Each project is taken from a record
on one table. Against each project is one of four project administrators (the
administrators are stored in a separate table on a one-to-many relationship).

So, I am trying to get the form to change the colour of the administrator
control depending on which administrator has been selected for the project.

As a second request (which is not a priority by any means) is it possible to
change the background colour of the form itself dependent on the contents of
one of its controls?

In Conditional Formatting you'll need to use

Expression Is

then

[Administrator]="Washington"

where Washington is the admin.

Since you have 4 administrators, you'll have to settle for using the Default
Formatting for one of them.

As for your second question, what kind of form are you talking about?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via http://www.accessmonster.com

Hi Ben,

Try the following in the Northwind.mdb sample database:

To implement Conditional Formatting on text values:

1.) Open the Categories form in design view. Display the form's Properties
dialog (View | Properties, or the F4 button). Select the CategoryName text
box. You should see "Text Box: CategoryName" in the blue title bar of the
Properties dialog. Click on Format | Conditional Formatting...

2.) Add the following two conditions:

Condition 1
Field Value Is equal to "Beverages"
Select a bold red font with a white background

Condition 2
Field Value Is equal to "Condiments"
Select a bold green font with a blue background

To change the background color of the form, based on a value in the
CategoryName field:

1.) With the word "Form" showing in the blue title bar of the Properties
dialog, select the Format tab. Remove the (bitmap) from the Picture property.

2.) Copy and paste the following VBA code into the Form_Current procedure
for this form. You can add additional Case statements, as desired:

Option Compare Database
Option Explicit

Private Sub Form_Current()
On Error GoTo ProcError

Select Case Me.CategoryName
Case "Beverages"
Me.Detail.BackColor = 255 'Red
Case "Condiments"
Me.Detail.BackColor = 33023 'Orange
Case Else
Me.Detail.BackColor = 12632256 'Gray
End Select

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Current event procedure..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Ben M Rowe" wrote:

Hi there

I apologise for my lack of understanding about Access 2003, I'm still
learning and hoping you can help make me better!

I have designed a form, and need to conditionally format one of the controls
based on its own contents. I have managed to make it work when I am working
with numerical vaules, but can't seem to get my head around making it work
with text inputs.

Effectively the form displays projects. Each project is taken from a record
on one table. Against each project is one of four project administrators (the
administrators are stored in a separate table on a one-to-many relationship).

So, I am trying to get the form to change the colour of the administrator
control depending on which administrator has been selected for the project.

As a second request (which is not a priority by any means) is it possible to
change the background colour of the form itself dependent on the contents of
one of its controls?

Thank you to both responses, very helpful and I've achieved exactly what I
needed now!

Ben

"Tom Wickerath" wrote:

Hi Ben,

Try the following in the Northwind.mdb sample database:

To implement Conditional Formatting on text values:

1.) Open the Categories form in design view. Display the form's Properties
dialog (View | Properties, or the F4 button). Select the CategoryName text
box. You should see "Text Box: CategoryName" in the blue title bar of the
Properties dialog. Click on Format | Conditional Formatting...

2.) Add the following two conditions:

Condition 1
Field Value Is equal to "Beverages"
Select a bold red font with a white background

Condition 2
Field Value Is equal to "Condiments"
Select a bold green font with a blue background

To change the background color of the form, based on a value in the
CategoryName field:

1.) With the word "Form" showing in the blue title bar of the Properties
dialog, select the Format tab. Remove the (bitmap) from the Picture property.

2.) Copy and paste the following VBA code into the Form_Current procedure
for this form. You can add additional Case statements, as desired:

Option Compare Database
Option Explicit

Private Sub Form_Current()
On Error GoTo ProcError

Select Case Me.CategoryName
Case "Beverages"
Me.Detail.BackColor = 255 'Red
Case "Condiments"
Me.Detail.BackColor = 33023 'Orange
Case Else
Me.Detail.BackColor = 12632256 'Gray
End Select

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Current event procedure..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Ben M Rowe" wrote:

Hi there

I apologise for my lack of understanding about Access 2003, I'm still
learning and hoping you can help make me better!

I have designed a form, and need to conditionally format one of the controls
based on its own contents. I have managed to make it work when I am working
with numerical vaules, but can't seem to get my head around making it work
with text inputs.

Effectively the form displays projects. Each project is taken from a record
on one table. Against each project is one of four project administrators (the
administrators are stored in a separate table on a one-to-many relationship).

So, I am trying to get the form to change the colour of the administrator
control depending on which administrator has been selected for the project.

As a second request (which is not a priority by any means) is it possible to
change the background colour of the form itself dependent on the contents of
one of its controls?

Hi Ben,

You're welcome. You know, if you are going to use VBA code to change the
backcolor of a particular form section (such as the Detail section), you
might as well do the conditional formatting in the same procedure, rather
than rely on the built-in conditional formatting. Doing so will keep all
formatting operations in one place (easier to maintain), and you'll be able
to expand beyond the limit in Access 2003 of three conditions. Something like
this:

Private Sub Form_Current()
On Error GoTo ProcError

Select Case Me.CategoryName
Case "Beverages"
Me.Detail.BackColor = 255 'Red
With Me.CategoryName
.ForeColor = 255 'Red
.BackColor = 16777215 'White
.FontBold = True
End With

Case "Condiments"
Me.Detail.BackColor = 33023 'Orange
With Me.CategoryName
.ForeColor = 32768 'Green
.BackColor = 16711680 'Blue
.FontBold = True
End With

Case Else
Me.Detail.BackColor = 12632256 'Gray
With Me.CategoryName
.ForeColor = 0 'Black
.BackColor = 12632256 'Gray
.FontBold = False
End With
End Select

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in Form_Current event procedure..."
Resume ExitProc
End Sub


Notes:
Remove the existing Conditional Formatting applied via the Format |
Conditional Formattng... dialog.

You can also reference pre-defined intrinsic constants for color (or define
your own with constants). The following constants can be used anywhere in
your code in place of the actual values:

Constant Description
vbBlack Black
vbRed Red
vbGreen Green
vbYellow Yellow
vbBlue Blue
vbMagenta Magenta
vbCyan Cyan
vbWhite White


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Ben M Rowe" wrote:

Thank you to both responses, very helpful and I've achieved exactly what I
needed now!

Ben