How to make record Red if field contains "Confidential"?

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

Guest

If my "Remarks" field contains the word Confidential, I want to turn the
whole record Red, other wise I want to leave it as is? As a new user to
Microsoft Access, I am not familiar with how to build such an expression.
 
Use conditional formatting in design-view for your report. The entries are
pretty obvious.
 
You can set the Conditional Formatting for each text box in the detail
section of your report. You would need something like Expression Is:
[Remarks] Like "*Confidential*"
Another method is to use code in the On Format event of the detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error Resume Next 'some controls don't have a forecolor
Dim ctl As Control
Dim lngColor As Long
If InStr(Me.Remarks & "", "confidential") > 0 Then
lngColor = vbRed
Else
lngColor = vbBlack
End If
For Each ctl In Me.Section(0).Controls
ctl.ForeColor = lngColor
Next
End Sub
 
Maybe to you, but not to me! I can easily turn the Remarks field to red, but
I don't see any thing that will let me turn the whole record red?
 
Thanks Duane, I will give the On Format a try!

Duane Hookom said:
You can set the Conditional Formatting for each text box in the detail
section of your report. You would need something like Expression Is:
[Remarks] Like "*Confidential*"
Another method is to use code in the On Format event of the detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error Resume Next 'some controls don't have a forecolor
Dim ctl As Control
Dim lngColor As Long
If InStr(Me.Remarks & "", "confidential") > 0 Then
lngColor = vbRed
Else
lngColor = vbBlack
End If
For Each ctl In Me.Section(0).Controls
ctl.ForeColor = lngColor
Next
End Sub

--
Duane Hookom
MS Access MVP
--

David L. said:
If my "Remarks" field contains the word Confidential, I want to turn the
whole record Red, other wise I want to leave it as is? As a new user to
Microsoft Access, I am not familiar with how to build such an expression.
Click to expand...
Click to expand...
 
You would either use Duane's suggestion, or use conditional formatting for
EACH control in the record.
 
David,
Just a small point but fairly significant... Reports have controls. "whole
record" has no meaning in an Access report. We must guess that you mean
"text boxes" or "controls" or "bound controls".

My guess is that you want to change the foreground color from black to red
for all the bound controls in the report's detail section if one of the
fields in the report's record source contains the word "confidential". I
think this is a fairly safe assumption.
 
Duane, thanks a million I have it working just like I want it now!

Duane Hookom said:
You can set the Conditional Formatting for each text box in the detail
section of your report. You would need something like Expression Is:
[Remarks] Like "*Confidential*"
Another method is to use code in the On Format event of the detail section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error Resume Next 'some controls don't have a forecolor
Dim ctl As Control
Dim lngColor As Long
If InStr(Me.Remarks & "", "confidential") > 0 Then
lngColor = vbRed
Else
lngColor = vbBlack
End If
For Each ctl In Me.Section(0).Controls
ctl.ForeColor = lngColor
Next
End Sub

--
Duane Hookom
MS Access MVP
--

David L. said:
If my "Remarks" field contains the word Confidential, I want to turn the
whole record Red, other wise I want to leave it as is? As a new user to
Microsoft Access, I am not familiar with how to build such an expression.
Click to expand...
Click to expand...
 
Thank you Rick, I now see exactly what you were saying! Your suggestion
works just like you told me!
 
Back
Top