Color Coding

[Stu Campbell]

I have a table with fields showing 5 Deliverable Dates for a project. Each
Deliverable Date has a cooresponding field for"% complete". I would like to
clor code a status field based on Deliverable Date and % Complete. For
example:
Deliverable 1 = 1/1/06
Deliverable 1 % Complete = 100
Deliverable 2 = 3/1/06
Deliverable 2 % Complete = 80
I would like status field to color red showing Deliverable 2 not 100%
complete and date has past. So I need coding to determine which delivery %
has not reached 100 and then compare that Deliverable date to todays date.

Can you help?

 

[tina]

you can't do it in a table. bind your table to a form, open the form in
Design view, and take a look at the Conditional Formatting option, under
Format on the menu bar.

hth

 

[Stu Campbell]

Tina,
Just now getting back to this project. I would liek to do this within a
Report and not a Form. Is this possible? What if I need to have more than 3
conditions...anyway to expand this?

 

[butboris via AccessMonster.com]

HI Stu.

Here is a code that could be placed in the "After Update" event of the
Reports text box in question.

This has been take from http://www.rogersaccesslibrary.com the download was
called conditional formatting, I would suggest looking at this download as it
is most useful

I hope mr Roger is not offended with me for showing his code!

Private Sub txtBal_AfterUpdate()

If IsNull(Me!txtBal) Then
' Forecolor is immaterial - you can't see it anyway!
Else
Select Case Me!txtBal
Case Is < 1000
Me!txtBal.ForeColor = vbBlack
Case 1000 To 1999
Me!txtBal.ForeColor = vbRed
Case 2000 To 2999
Me!txtBal.ForeColor = vbGreen
Case 3000 To 3999
Me!txtBal.ForeColor = vbYellow
Case 4000 To 4999
Me!txtBal.ForeColor = vbBlue
Case 5000 To 5999
Me!txtBal.ForeColor = vbMagenta
Case 6000 To 6999
Me!txtBal.ForeColor = vbCyan
Case Is > 6999
Me!txtBal.ForeColor = vbWhite
End Select
End If
End Sub

Tina,
Just now getting back to this project. I would liek to do this within a
Report and not a Form. Is this possible? What if I need to have more than 3
conditions...anyway to expand this?

you can't do it in a table. bind your table to a form, open the form in
Design view, and take a look at the Conditional Formatting option, under

[quoted text clipped - 17 lines]

 

[tina]

textbox controls in a report don't have AfterUpdate events, because a report
control is not interactive. however...

it's actually easier to control how individual records are displayed in a
report than it is in a form - with a greater range of options than is
offered in the Conditional Formatting dialog in form design. you can add
code to the report Detail section's OnFormat or OnPrint event, something
along the lines of

If Me!DeliverableControlName < 1 And _
Me!DateControlName < Date Then
Me!StatusControlName.ForeColor = vbRed
Else
Me!StatusControlName.ForeColor = vbBlack
End If

note that you posted your question to the access.formscoding NG. suggest you
post future questions about reports to the access.reports NG, so the initial
answers you get are more appropriate to reports.

hth

HI Stu.

Here is a code that could be placed in the "After Update" event of the
Reports text box in question.

This has been take from http://www.rogersaccesslibrary.com the download was
called conditional formatting, I would suggest looking at this download as it
is most useful

I hope mr Roger is not offended with me for showing his code!

Private Sub txtBal_AfterUpdate()

If IsNull(Me!txtBal) Then
' Forecolor is immaterial - you can't see it anyway!
Else
Select Case Me!txtBal
Case Is < 1000
Me!txtBal.ForeColor = vbBlack
Case 1000 To 1999
Me!txtBal.ForeColor = vbRed
Case 2000 To 2999
Me!txtBal.ForeColor = vbGreen
Case 3000 To 3999
Me!txtBal.ForeColor = vbYellow
Case 4000 To 4999
Me!txtBal.ForeColor = vbBlue
Case 5000 To 5999
Me!txtBal.ForeColor = vbMagenta
Case 6000 To 6999
Me!txtBal.ForeColor = vbCyan
Case Is > 6999
Me!txtBal.ForeColor = vbWhite
End Select
End If
End Sub

Tina,
Just now getting back to this project. I would liek to do this within a
Report and not a Form. Is this possible? What if I need to have more than 3
conditions...anyway to expand this?

you can't do it in a table. bind your table to a form, open the form in
Design view, and take a look at the Conditional Formatting option,

under
[quoted text clipped - 17 lines]

Can you help?