formating a field based on a condition

[billheath]

How do you set field colrs based on a formula.

I primarily took this one from the on-line help, but I can't get it to work. This is in a function and "Fld" is passed to the function as the field name. I use A to capture the ME!Fld. However at run time A gets converted to the value of the expression "RGB(255, 0, 0)" or 255. What I want to do is set the field format. I know there is a way.

fld = "Me!" & fld
A = fld
Dim lngRed As Long, lngYellow As Long, lngWhite As Long, lngBlack As Long
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)




With rst

.Edit
If strPart < !Cur_Val Then
A.BorderColor = lngRed
A.ForeColor = lngRed
A.BackColor = lngYellow
Else
A.BorderColor = lngBlack
A.ForeColor = lngBlack
A.BackColor = lngWhite

End If


.Update
End With

 

[PHV]

I'd replace this:
fld = "Me!" & fld
A = fld
with this:
Set A = Me.Controls(fld)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

Hey Bill .. have you tried conditional formatting? also for code vb has color constants. vbRed, vbBlack, vbWhite, vbYellow, vbMagenta, vbBlue, vbGreen, vbCyan.


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[billheath]

Thanks, everyone.
PH, when i tried "Set A = Me.Controls(fld)", I got the error message "Type Mismatch" on that line. I have A dimmed as a field.
Not sure what it should be.

Yes, I have tried conditional formating. Not sure how to compare todays data with yesterdays without using code.

Thanks, Bill

 

[PHV]

I have A dimmed as a field
You confuse Field (in a table or recordset) and Control (in a form).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[billheath]

You are right. I wasn't thinking.

Thanks, It worked that time.

 

[MazeWorX]

Here for future reference is an expression that could be used as conditional formatting

You select 'Expression is'

IIf([Text4]>(Date()-1),True,False) < so if the value of Text4 is greater than Today - 1 (yesterday) the expression returns True else it returns false.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>