When displaying several records, it is sometimes useful to
modify the presentation of those records which meet certain
criteria (such as making negative numbers red).
| MS Access
| Visual Basic
This is the newValue event attached to the
It is called each time a new value is displayed.
method newValue(var eventInfo Event)
if self <> Correct_Age_UI_Field
then self.font.color = Red
else self.font.color = Black
MS Access 97
MS Access does not provide any way to modify
a single record
when multiple records are displayed.
In both cases,
- When data is displayed,
only the form's On Current event
is called on a per record basis.
- When a field is modified by hand (typing),
the After Update event is called.
ComponentName.ForeColor = value
modifies the color for all the records, not just the current record.
I partially solved the problem by creating an edit field where the
Control Source was set to
=IIf([Correct Age]=[Computed Age],"","*")
This basically works ... except when the scroll bar is used to
see all the records. When the scroll bar tab is dragged,
the asterisks tend to disappear.
(Generally, you'll loose a few at a time.)
Fortunately, they reappear when the mouse is released.
A Free Third Part Fix
FormatByCriteria (free - by Stephen Lebans)
provides that functionality.
It works by drawing a background around the
appropriate control, record, or column.
The implementation directly calls the operating system -
user32, kernel32, gdi32.
This is necessary to determine the position of the scroll bar.
Visual Basic 6.0
Author: Robert Clemenzi -