Select Case Me!txtBal
Case Is < 2500
Me!txtBal.BackColor = vbWhite
Case Is < 5000
Me!txtBal.BackColor = vbGreen
Case Is < 7500
Me!txtBal.BackColor = vbYellow
Case Is >= 7500
Me!txtBal.BackColor = vbRed
End Select
or maybe this to format fields based on the value in another field (Check14).
If Me!Check14 = True Then
Me!txtDesc.BackColor = vbRed
Me!Acct.ForeColor = vbRed
Else
Me!txtDesc.BackColor = vbBlue
Me!Acct.ForeColor = vbBlue
End If
Which gives me the following on one record
But this on the next record
This works well in Single Form view, but not so well in Continuous Form View or Datasheet View.
Datasheet View has no formatting at all.
The problem is that formatting with VBA affects all records the same. This is fine in Single Form view, because you can only see one.
Conditional Formatting
So to solve this problem, Microsoft introduced Conditional Formatting to Access Forms and Reports. I’m going to concentrate on forms here, but the same applies to reports.Conditional Formatting is built into the form controls themselves. So to set it, you need to open the form in Design View to get at the control properties.
You need to select the control (in this case a textbox) , the FORMAT tab on the Ribbon, and then Conditional Formatting.
You’ll get a dialog box like this.
Click the New Rule button and you will get yet another dialog box
In this article, I’m goin to concentrate on comparing to values within the current record, so I’ll leave the top rule type selected.
The next step is to choose the Field Value Is drop down box. It will have 3 choices:
- Field Value Is: Compares the selected control to some hard coded value(s)
- Expression Is: Compares the selected control to the value of another control.
- Field Has Focus: Determines whether the control is currently selected.
Field Value Is
Comparing the selected control to some hard coded value(s)
So first, I’m going to change the formatting a field (Balance) based on the value it contains. If you recall from above, the Balance field had the following conditions.txtBal < 2500 –> White
txtBal < 5000 –> Green
txtBal < 7500 –> Yellow
txtBal >= 7500 –> Red
I just need to replicate this logic in the Formatting Rule dialog box. First, I’ll make the following selections:
Next, I’ll set the background color
to white
Then click OK.
Next, create another New Rule for the next condition
Giving you this result.
Complete the rest of the conditions. It should look like this.
Which results in this Continuous View
I can also modify multiple properties for each rule
Expression Is:
Comparing the selected control to the value of another control
Another common problem is to change formatting of a field base on the value in another field. Above, in VBA, I formatted the Acct and Desc fields with logic as follows:If the check box is checked (True) Then
the background color of Desc becomes Red
the text color of Acct becomes Blue
If the check box is not checked (False) Then
the background color of Desc becomes Blue
the text color of Acct becomes Red
End If
I can use the Expression Is option to accomplish this.
Select the field you want to format (Desc) and open the Conditional Formatting editor. Create a New Rule.
Select Expression Is
The ellipsis at the end will open the Expression Builder.
Make the following selections:
The result is [Check14] = True. I don’t have to use the Expression Builder. I could just type it in the expression field. But the Expression Builder is a great tool to investigate the range of expressions that can be used for formatting.
Then finish the formatting
Create another rule for the False Condition [Check14] = False, and format it appropriately.
Repeat the process for field Acct.
And your formatting is done.
Field Has Focus
Determines whether the control is currently selected.
Lastly, I can format based on whether or not a form control has the focus. This is great for highlighting the currently selected field.
Clicking anywhere in the table will highlight the selected field. Now, I have to set the conditional formatting for each field, which would be tedious except that Access allows me to set the formatting for multiple fields at the same time.
In Design View, select all the textboxes in the Detail section.
Then go to the Conditional Formatting editor. Notice how the selection for formatting says Multiple.
Create a New Rule and selecting Has Focus and formatting it as shown
Click OK here and in the Rules Manager and I’m done.
Highlight Selected Record
To highlight the selected record, I do essentially the same thing with a few modifications.
Add an unbound textbox control (txtBackColor3) to the form and put it behind the other control. Make sure this unbound control is the same shape as the Detail section. (I’ve shown it below the other text boxes here to illustrate.)
Then set the Back Style property of the other textboxes to Transparent.
It also requires one bit of VBA code. In the OnCurrent event of the form, add this:
Private Sub Form_Current()
If Not Me.NewRecord Then
Me!txtBackColor3.ControlSource = "=[txtGLID] = " & Me!GLID
Else
Me!txtBackColor3.ControlSource = "=False"
End If
End Sub
What does this do?
The textbox that displays the primary key of the record (GLID) is txtGLID. If the value of txtGLID matches the GLID of the underlying recordset, set the value of txtBackColor3 to TRUE. If it’s not, set the value to FALSE.
Now, I can make a Conditional Format that tests the value of txtBackColor3 and sets the Fill to Green if TRUE.
Now just make sure the txtBackColor3 control is moved behind the other controls.
And it’s done.
If the conditional formatting of the other textboxes are set to green fill when it has the focus (as in Field Has Focus example above), the currently selected field will also be green. If not, the selected field in the selected row will be white.
Sample Database
You can find a sample database illustrating all of these techniques here:http://www.rogersaccesslibrary.com/forum/topic375.html
No comments:
Post a Comment