Formatting textboxes based on comparison using VBA

[Estelle Harris]

Hi

I am trying to format the textboxes on an access report I have created from
a database query, when the report is populated from the query I want to
compare two textboxes (Data1 and Data2). If the values are not equal, I
would like Data1 to be formatted differently (e.g. maybe change
backcolor,forecolor etc).

The problem is that I can change the format of the textboxes but it only
seems to be looking at the last row of values in the report, I have 3 rows
of data when the report is generated so I am assuming that we have 3
instances of the Data1 textbox

Here is the VBA code I have created on the reports load event

Private Sub Report_Load()

Dim Rec As Control

For Each Rec In Me.Report
MsgBox (Rec.Name)
If Rec.Name = "Data1" Then
MsgBox ("First If Statement Entered")
MsgBox ("Data1 Value = " & Data1.Value)
If Data1.Value <> Data2.Value Then
MsgBox ("Second If Statement Entered")
Rec.ForeColor = vbRed
End If
End If
Next

End Sub

If somebody could help me out here, am I using the correct event to add the
code to?

How can I loop through all instances of the Data1 textbox to compare the
data in each row (i.e. row1 compare Data1 to Data2 > format accordinly, row2
compare Data1 to Data2 > format accordinly.......etc)

Any help would be much appreciated

Regards

Matt

 

[John Spencer]

You should move your code to the format event of the section that contains
Data1 and Data2. Alternatively you can use conditional formatting on the
control.

If Me.Data1 <> Me.Data2 Then
Me.Data1.ForeColor = vbRed
Else
Me.Data1.ForeColor = vbBlack
End If


Conditional formatting (Menu Format: Conditional Formatting) would use
Expression is : [Data1]<>[Data2]
as the condition for Data1

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..