Formatting textboxes based on comparison using VBA

  • Thread starter Thread starter Estelle Harris
  • Start date Start date
E

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
 
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
..
 
Back
Top