A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

HELP: Conditional Formatting with more then 3 conditions



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2005, 12:50 PM
Tim
external usenet poster
 
Posts: n/a
Default HELP: Conditional Formatting with more then 3 conditions

ACCESS 2000

There is a field in my database that requires highlighting; however, I have
a total of 15 cases that requires, obviously I can't set that up with the
default C.F.

Is there a way to set up a VBA code that would allow me to set up the 15
conditions?
  #2  
Old May 12th, 2005, 02:45 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

Yes, in the Format event of the section of the report that the field is
reported in, you could set the formatting of the control for that field. A
Select Case statement may be the easiest.

Example:
Select Case Me.txtMyTextbox
Case "AB"
Me.txtMyTextbox.ForeColor = 8454143
Case 'etc
Case Else
Me.txtMyTextbox.ForeColor = 0
End Select

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
ACCESS 2000

There is a field in my database that requires highlighting; however, I
have
a total of 15 cases that requires, obviously I can't set that up with the
default C.F.

Is there a way to set up a VBA code that would allow me to set up the 15
conditions?



  #3  
Old May 12th, 2005, 03:38 PM
Tim
external usenet poster
 
Posts: n/a
Default

that code works for the text, but I need it for the background color.

I tried

Select Case Me.txtBox
Case "ab"
me.txtBox.BackColor=255
Case Else
me.txtBox.BackColor=0
End Select
End Sub

(to highlight the entire box red) but it didn't work
  #4  
Old May 12th, 2005, 04:08 PM
Tim
external usenet poster
 
Posts: n/a
Default

nevermind, I solved the problem.

I had the text box set on TRANSPARENT; that's why the conditional
highlighting wasn't appearing. when I changed the default BackColor to
white, the conditional BackColor appeared.

thanks for your help.
  #5  
Old May 12th, 2005, 04:36 PM
Tim
external usenet poster
 
Posts: n/a
Default

Could this work:

Condition BackColor
X1 255
X2 255
X3 255
Y1 100
Y2 100
Z1 301
Z2 302



Would this be more efficient, espically if I have a long list, and more
conditions arise that would require highlighting?

How would I structure the VBA code?

  #6  
Old May 12th, 2005, 05:03 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

Select Case Me.txtBox
Case "X1", "X2", "X3"
Me.txtBox.BackColor = 255
Case "Y1", "Y2"
Me.txtBox.BackColor = 100
Case "Z1"
Me.txtBox.BackColor = 301
Case "Z2"
Me.txtBox.BackColor = 302
Case Else
Me.txtBox.BackColor = 16777215
End Select

In the first statement, you may be able to get by with

Case "X1" To "X3"

so, if you have a lot more of these (i.e. X4, X5, X6, etc), that may be the
shorter way to write it. With just 3 elements though, it's not much shorter.
Whether or not this could be simplified (to lessen the typing) will depend
on how many you have and what sort of pattern they are (i.e. is there a
pattern to them that would be short and easy to code). The Like operator may
come to mind, but it's not supported in a Select Case statement.

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
Could this work:

Condition BackColor
X1 255
X2 255
X3 255
Y1 100
Y2 100
Z1 301
Z2 302



Would this be more efficient, espically if I have a long list, and more
conditions arise that would require highlighting?

How would I structure the VBA code?



  #7  
Old May 12th, 2005, 05:22 PM
Tim
external usenet poster
 
Posts: n/a
Default

So I would not be able to do this by setting up a table with the condition in
one column and the color# in the second column.
I would need to specify the condition in the line of code?

This list has the potentional of expanding; what I wrote was just a
quick-and-dirty example.

Sorry I didn't clarify that before.

"Wayne Morgan" wrote:

Select Case Me.txtBox
Case "X1", "X2", "X3"
Me.txtBox.BackColor = 255
Case "Y1", "Y2"
Me.txtBox.BackColor = 100
Case "Z1"
Me.txtBox.BackColor = 301
Case "Z2"
Me.txtBox.BackColor = 302
Case Else
Me.txtBox.BackColor = 16777215
End Select

In the first statement, you may be able to get by with

Case "X1" To "X3"

so, if you have a lot more of these (i.e. X4, X5, X6, etc), that may be the
shorter way to write it. With just 3 elements though, it's not much shorter.
Whether or not this could be simplified (to lessen the typing) will depend
on how many you have and what sort of pattern they are (i.e. is there a
pattern to them that would be short and easy to code). The Like operator may
come to mind, but it's not supported in a Select Case statement.

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
Could this work:

Condition BackColor
X1 255
X2 255
X3 255
Y1 100
Y2 100
Z1 301
Z2 302



Would this be more efficient, espically if I have a long list, and more
conditions arise that would require highlighting?

How would I structure the VBA code?




  #8  
Old May 12th, 2005, 05:37 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

Yes, you could set up a table with the condition and the color. You could
then lookup the color and apply it.

Example:
Me.txtBox.BackColor = DLookup("ColorField", "TableName", "ConditionField='"
& Me.txtBox & "'")

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
So I would not be able to do this by setting up a table with the condition
in
one column and the color# in the second column.
I would need to specify the condition in the line of code?

This list has the potentional of expanding; what I wrote was just a
quick-and-dirty example.

Sorry I didn't clarify that before.

"Wayne Morgan" wrote:

Select Case Me.txtBox
Case "X1", "X2", "X3"
Me.txtBox.BackColor = 255
Case "Y1", "Y2"
Me.txtBox.BackColor = 100
Case "Z1"
Me.txtBox.BackColor = 301
Case "Z2"
Me.txtBox.BackColor = 302
Case Else
Me.txtBox.BackColor = 16777215
End Select

In the first statement, you may be able to get by with

Case "X1" To "X3"

so, if you have a lot more of these (i.e. X4, X5, X6, etc), that may be
the
shorter way to write it. With just 3 elements though, it's not much
shorter.
Whether or not this could be simplified (to lessen the typing) will
depend
on how many you have and what sort of pattern they are (i.e. is there a
pattern to them that would be short and easy to code). The Like operator
may
come to mind, but it's not supported in a Select Case statement.

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
Could this work:

Condition BackColor
X1 255
X2 255
X3 255
Y1 100
Y2 100
Z1 301
Z2 302



Would this be more efficient, espically if I have a long list, and more
conditions arise that would require highlighting?

How would I structure the VBA code?






  #9  
Old May 12th, 2005, 05:44 PM
Wayne Morgan
external usenet poster
 
Posts: n/a
Default

PS.

DLookup could get fairly slow. It may be better to include the lookup table
in the query feeding the report. Link the lookup table to the main table on
the condition field and the field in the main table that has the value (i.e.
X1, X2, etc). You would then have the associated color field available for
each record. Place a textbox in the same report section as txtBox and set
this new textbox's Visible property to No. In the Format event code for the
section you could now use:

Me.txtBox.BackColor = Me.txtColorBox

To handle the Else condition as in the Case Else in the Select statement,
set the link between the tables to "include all fields from the main table
and only those fields from the lookup table where the fields match". This
will give you a value of Null for all records that didn't have a color
specified. You would then change the above statement to:

Me.txtBox.BackColor = Nz(Me.txtColorBox, 16777215)

This will return the color 16777215 if a color hasn't been specified.

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
So I would not be able to do this by setting up a table with the condition
in
one column and the color# in the second column.
I would need to specify the condition in the line of code?

This list has the potentional of expanding; what I wrote was just a
quick-and-dirty example.

Sorry I didn't clarify that before.

"Wayne Morgan" wrote:

Select Case Me.txtBox
Case "X1", "X2", "X3"
Me.txtBox.BackColor = 255
Case "Y1", "Y2"
Me.txtBox.BackColor = 100
Case "Z1"
Me.txtBox.BackColor = 301
Case "Z2"
Me.txtBox.BackColor = 302
Case Else
Me.txtBox.BackColor = 16777215
End Select

In the first statement, you may be able to get by with

Case "X1" To "X3"

so, if you have a lot more of these (i.e. X4, X5, X6, etc), that may be
the
shorter way to write it. With just 3 elements though, it's not much
shorter.
Whether or not this could be simplified (to lessen the typing) will
depend
on how many you have and what sort of pattern they are (i.e. is there a
pattern to them that would be short and easy to code). The Like operator
may
come to mind, but it's not supported in a Select Case statement.

--
Wayne Morgan
MS Access MVP


"Tim" wrote in message
...
Could this work:

Condition BackColor
X1 255
X2 255
X3 255
Y1 100
Y2 100
Z1 301
Z2 302



Would this be more efficient, espically if I have a long list, and more
conditions arise that would require highlighting?

How would I structure the VBA code?






  #10  
Old May 12th, 2005, 06:18 PM
Tim
external usenet poster
 
Posts: n/a
Default

The colors won't appear. Here is the code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Select Case Me.txtBOX
Case Me.txtBOX.BackColor = DLookup("color", "condition-color",
"condition='" & Me.txtBOX & "'")
Case Else
Me.txtBOX.BackColor = 16777215
End Select

End Sub
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formatting in form slows down calculations Susan Using Forms 5 June 13th, 2005 11:24 AM
Conditional Formatting Error ddate Worksheet Functions 0 May 5th, 2005 09:00 PM
Conditional Formatting Anomaly Rutgers_Excels General Discussion 1 November 1st, 2004 09:43 PM
Freezing Conditional Formatting Ashish Chamaria Worksheet Functions 3 December 19th, 2003 11:54 AM
Conditional formatting with formula Ann Scharpf Worksheet Functions 5 October 21st, 2003 04:29 AM


All times are GMT +1. The time now is 05:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.