Help - Conditional Formatting won't help me

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that when given three specific criteria on a record, a
message box will appear giving further information. This works fine but I
would also like to change the font color for these three fields based on the
criteria being true. I have approximately 25 different criteria situations
that could appear.

Here is a sample of the code I am using...When I change the forecolor and
background it applies to all records in the database, it does not pick out
the record that has the criteria listed.

In the first paragraph when "JS" is the line and "Bonder" is the Description
and "Workstation #4" is the Workstation I want the message box to appear but
I also want the font color of "JS" "Bonder" and "Workstation #4" to change to
red. I do not want the other records to change colors. Can anyone help me?
(FYI - I have posted this message twice but didn't see it listed, so I hope
I'm not duplicating anything). Thanks in advance for your help.

If Line = "JS" And Description = "Bonder" And Workstation = "Workstation #4"
Then
MsgBox "OPERATOR MUST MEET QUANDRANT 3 OR ABOVE STANDARDS TO OPERATE THIS
STATION. FOR THE PURPOSES OF TRAINING AN OPERATOR TO MEET THIS STANDARD, THE
TRAINEE MUST WORK WITH A QUALIFIED QUADRANT 3 OPERATOR OR SUPERVISOR FOR A
MININUM OF 30 MINUTES. AS WELL, THE SUPERVISOR MUST ACKNOWLEDGE THE ABILITY
OF THE TRAINEE TO MEET THE QUADRANT 3 QUALIFICATIONS"

ElseIf Line = "JS" And Description = "FLA" And Workstation = "Workstation
#9" Then Line.ForeColor = lngRed: Workstation.ForeColor = 255:
Description.ForeColor = 255
MsgBox "OPERATOR MUST MEET QUANDRANT 3 OR ABOVE STANDARDS TO OPERATE THIS
STATION. FOR THE PURPOSES OF TRAINING AN OPERATOR TO MEET THIS STANDARD, THE
TRAINEE MUST WORK WITH A QUALIFIED QUADRANT 3 OPERATOR OR SUPERVISOR FOR A
MININUM OF 30 MINUTES. AS WELL, THE SUPERVISOR MUST ACKNOWLEDGE THE ABILITY
OF THE TRAINEE TO MEET THE QUADRANT 3 QUALIFICATIONS"

ElseIf Line = "968 Rear" And Description = "Welder" And Workstation =
"Workstation #3" Then
MsgBox "OPERATOR MUST MEET QUANDRANT 3 OR ABOVE STANDARDS TO OPERATE THIS
STATION. FOR THE PURPOSES OF TRAINING AN OPERATOR TO MEET THIS STANDARD, THE
TRAINEE MUST WORK WITH A QUALIFIED QUADRANT 3 OPERATOR OR SUPERVISOR FOR A
MININUM OF 30 MINUTES. AS WELL, THE SUPERVISOR MUST ACKNOWLEDGE THE ABILITY
OF THE TRAINEE TO MEET THE QUADRANT 3 QUALIFICATIONS"

ElseIf Line = "Mustang RCL" And Description = "Welder" And Workstation =
"Workstation #1" Then
MsgBox "OPERATOR MUST MEET QUANDRANT 3 OR ABOVE STANDARDS TO OPERATE THIS
STATION. FOR THE PURPOSES OF TRAINING AN OPERATOR TO MEET THIS STANDARD, THE
TRAINEE MUST WORK WITH A QUALIFIED QUADRANT 3 OPERATOR OR SUPERVISOR FOR A
MININUM OF 30 MINUTES. AS WELL, THE SUPERVISOR MUST ACKNOWLEDGE THE ABILITY
OF THE TRAINEE TO MEET THE QUADRANT 3 QUALIFICATIONS"
 
You can use conditional formatting.
First write a vba function that returns True or False based on the multiple
conditions you have

Public Function fTestConditions(strLine, strDesc, strWorkStation)
If strLine = "JS" and strDesc = "Bonder" and strWorkStation = "Workstation
#4" then
fTestConditions = True
ElseIf strLine = "JS" and strDesc = "FLA" and strWorkStation = "Workstation
#9" then
fTestConditions = True
....
END IF

Your condition becomes
Expression is fTestConditions([Line],[Description],[WorkStation])

By the way I would build a table of the requirements that trigger this and
simplify my life.
Table:TriggerWarning
fLine
fDescription
fWorkStation
fMessage

Then the function becomes simpler to write and too maintain

Public Function fTestConditions(strLine, strDesc, strWorkStation)

If DCount("*","TriggerWarning", _
"fLine=""" & strLine _
& """ and fDescription = """ & strDesc _
& """ and fWorkStation= """ & strWorkStation & """") > 0 Then

fTestConditions = True

End If

You could also use the table to get the message, using DLookup function. If
DLookup returned null or a zero-length string then you would not display a
message.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John, you are a genius! Thank you so much :-)

John Spencer said:
You can use conditional formatting.
First write a vba function that returns True or False based on the multiple
conditions you have

Public Function fTestConditions(strLine, strDesc, strWorkStation)
If strLine = "JS" and strDesc = "Bonder" and strWorkStation = "Workstation
#4" then
fTestConditions = True
ElseIf strLine = "JS" and strDesc = "FLA" and strWorkStation = "Workstation
#9" then
fTestConditions = True
....
END IF

Your condition becomes
Expression is fTestConditions([Line],[Description],[WorkStation])

By the way I would build a table of the requirements that trigger this and
simplify my life.
Table:TriggerWarning
fLine
fDescription
fWorkStation
fMessage

Then the function becomes simpler to write and too maintain

Public Function fTestConditions(strLine, strDesc, strWorkStation)

If DCount("*","TriggerWarning", _
"fLine=""" & strLine _
& """ and fDescription = """ & strDesc _
& """ and fWorkStation= """ & strWorkStation & """") > 0 Then

fTestConditions = True

End If

You could also use the table to get the message, using DLookup function. If
DLookup returned null or a zero-length string then you would not display a
message.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Michelle said:
I have a database that when given three specific criteria on a record, a
message box will appear giving further information. This works fine but I
would also like to change the font color for these three fields based on
the
criteria being true. I have approximately 25 different criteria
situations
that could appear.

Here is a sample of the code I am using...When I change the forecolor and
background it applies to all records in the database, it does not pick out
the record that has the criteria listed.

In the first paragraph when "JS" is the line and "Bonder" is the
Description
and "Workstation #4" is the Workstation I want the message box to appear
but
I also want the font color of "JS" "Bonder" and "Workstation #4" to change
to
red. I do not want the other records to change colors. Can anyone help
me?
(FYI - I have posted this message twice but didn't see it listed, so I
hope
I'm not duplicating anything). Thanks in advance for your help.

If Line = "JS" And Description = "Bonder" And Workstation = "Workstation
#4"
Then
MsgBox "OPERATOR MUST MEET QUANDRANT 3 OR ABOVE STANDARDS TO OPERATE THIS
STATION. FOR THE PURPOSES OF TRAINING AN OPERATOR TO MEET THIS STANDARD,
THE
TRAINEE MUST WORK WITH A QUALIFIED QUADRANT 3 OPERATOR OR SUPERVISOR FOR A
MININUM OF 30 MINUTES. AS WELL, THE SUPERVISOR MUST ACKNOWLEDGE THE
ABILITY
OF THE TRAINEE TO MEET THE QUADRANT 3 QUALIFICATIONS"

ElseIf Line = "JS" And Description = "FLA" And Workstation = "Workstation
#9" Then Line.ForeColor = lngRed: Workstation.ForeColor = 255:
Description.ForeColor = 255
MsgBox "OPERATOR MUST MEET QUANDRANT 3 OR ABOVE STANDARDS TO OPERATE THIS
STATION. FOR THE PURPOSES OF TRAINING AN OPERATOR TO MEET THIS STANDARD,
THE
TRAINEE MUST WORK WITH A QUALIFIED QUADRANT 3 OPERATOR OR SUPERVISOR FOR A
MININUM OF 30 MINUTES. AS WELL, THE SUPERVISOR MUST ACKNOWLEDGE THE
ABILITY
OF THE TRAINEE TO MEET THE QUADRANT 3 QUALIFICATIONS"

ElseIf Line = "968 Rear" And Description = "Welder" And Workstation =
"Workstation #3" Then
MsgBox "OPERATOR MUST MEET QUANDRANT 3 OR ABOVE STANDARDS TO OPERATE THIS
STATION. FOR THE PURPOSES OF TRAINING AN OPERATOR TO MEET THIS STANDARD,
THE
TRAINEE MUST WORK WITH A QUALIFIED QUADRANT 3 OPERATOR OR SUPERVISOR FOR A
MININUM OF 30 MINUTES. AS WELL, THE SUPERVISOR MUST ACKNOWLEDGE THE
ABILITY
OF THE TRAINEE TO MEET THE QUADRANT 3 QUALIFICATIONS"

ElseIf Line = "Mustang RCL" And Description = "Welder" And Workstation =
"Workstation #1" Then
MsgBox "OPERATOR MUST MEET QUANDRANT 3 OR ABOVE STANDARDS TO OPERATE THIS
STATION. FOR THE PURPOSES OF TRAINING AN OPERATOR TO MEET THIS STANDARD,
THE
TRAINEE MUST WORK WITH A QUALIFIED QUADRANT 3 OPERATOR OR SUPERVISOR FOR A
MININUM OF 30 MINUTES. AS WELL, THE SUPERVISOR MUST ACKNOWLEDGE THE
ABILITY
OF THE TRAINEE TO MEET THE QUADRANT 3 QUALIFICATIONS"
Click to expand...
Click to expand...
 
Back
Top