You *did* say in your original post that this was a report, not a form.
The Format event works only for report sections. To change format
depending
of cell values in a continuous form, you must use conditional formatting.
Just right-click on the textbox you wish to format, select Conditional
Formatting, and go from there.
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Hi!
Thanks. I was able to make some progress and soon I will start walking
and
running on my own but if you could please clarify a few things (its a
little
too early for me to use Access object browser);
I adjusted your codes like this;
Private Sub Command7_Click()
If Col2.Value = 7 Then
Col2.ForeColor = vbBlue
Col2.BackColor = vbGreen
Col2.FontBold = True
Else
Col2.ForeColor = vbBlack
Col2.BackColor = vbWhite
Col2.FontBold = False
End If
End Sub
When I clicked the button nothing happened.
Then I If Not;
Private Sub Command7_Click()
If Not Col2.Value = 7 Then
Col2.ForeColor = vbBlue
Col2.BackColor = vbGreen
Col2.FontBold = True
Else
Col2.ForeColor = vbBlack
Col2.BackColor = vbWhite
Col2.FontBold = False
End If
End Sub
The whole column went green.
It is probably due to the report pulls one column data like one single
thing
from the table and puts it onto one text box for viewing.
Then what actually 'If Col2.Value = 7' means?
Isn't there a code phrase to target individual numbers in the text box?
Or do we have to redesign the report with many individual text boxes?
Thank You.
M Varnendra
:
The event is the Format event of the report section which contains the
textbox you want to modify.
With your report in design view, double-click on the grey section
header
above your textbox. It probably has a down-arrow and "Detail" at the
left-hand side.
A properties window should appear and near the bottom you will see "On
Format". Type a single "[" in the corresponding cell (it will turn
into
"[Event Procedure]") then click on the button with 3 dots to the
right.
The VBA window should appear, showing the following:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
|
End Sub
...with the cursor just above "End Sub"
Inside your new event procedure, enter the following code:
Dim i as integer
Dim t as Textbox
For i = 1 to 5
Set t = Me("Col" & i)
if t.Value = 7 Then
t.ForeColor = vbBlue
t.BackColor = vbGreen
t.FontBold = True
Else
t.ForeColor = vbBlack
t.BackColor = vbWhite
t.FontBold = False
End If
Next i
This will examine the value for each of the textboxes named
Col1...Col5
in
the current record and set its formatting depending on whether its
value
is
7.
Instead of using the colour constants (vbBlue, etc) you can use the
RGB
function, which constructs any colour from its red, green and blue
components.
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Hi!
Where exactly should I place your codes and what exactly is the
event
and
what I have to do trigger the code please?
Thank You.
:
On Wed, 10 Dec 2008 09:21:47 -0500, "Douglas J. Steele"
Actually, I think there is a way. Remember how we used to write
code
in the Details_OnFormat event of a report to turn the background
from
white to lightgreen and back to white for each row (using Mod 2)?
The
OP can use this same event to inspect the values of the current
record
and set the background accordingly. For an example see the
"Customer
Address Book" report in Access 2007's Northwind database:
Private Sub Detail_Format(Cancel As Integer, FormatCount As
Integer)
Dim ctl As Control
Dim strV
For Each ctl In Me.Section(0).Controls
With ctl
If .Tag = "W" Then
strV = Right(ctl, 1)
Debug.Print ctl.Name
Debug.Print strV
Select Case strV
Case "R"
.BackColor = vbGreen
Case "L"
.BackColor = vbYellow
Case Else
.BackColor = vbRed
End Select
End If
End With
Next ctl
End Sub
-Tom.
Microsoft Access MVP
The only way would be to use Conditional Formatting. It's not
something
you
can do using VBA.
