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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 08:43 PM |
Freezing Conditional Formatting | Ashish Chamaria | Worksheet Functions | 3 | December 19th, 2003 10:54 AM |
Conditional formatting with formula | Ann Scharpf | Worksheet Functions | 5 | October 21st, 2003 04:29 AM |