conditional formatting

[joseph]

Good morning,

I tried to follow the code that was provided in a previous post and I came
up w/ the
following for my db ... (what I'm looking to do is I have a report that
contains about 10 fields, of which, I have a field known as "Date Oath
Received" ... if that field is blank in my report I would like to color code
the entire row yellow, if not, it can be default (white). I would also like
to color code the row red if there is a date
populated in that field "Date Oath Received" that is more than 30 days from
the date in the "Date of Appointment" field

This is what I have so far and nothing happens:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


'this is where I would like to identify specific colors
'for certain items on a report
'------------------------------------------------------
'If the date oath was received is beyond 30 days from
'date of authentication, color = red
'------------------------------------------------------
'if the no oath of office has been received
'color = yellow
'------------------------------------------------------

If Me.[Date Oath Received] >= Date And Me.[Date of Appointment] <=
DateAdd("d", -30, Date) Then
Me.[Date Oath Received].BackColor = vbRed
Else
Me.[Date Oath Received].BackColor = vbWhite
End If

Dim C As Control
For Each C In Me.Section(0).Controls
If TypeOf C Is TextBox Or TypeOf C Is Label Then
C.BackColor = Me.[Date Oath Received].BackColor
End If
Next




End Sub


any help would be most awesome!
Thank you,
Joseph

 

[Marshall Barton]

I tried to follow the code that was provided in a previous post and I came
up w/ the
following for my db ... (what I'm looking to do is I have a report that
contains about 10 fields, of which, I have a field known as "Date Oath
Received" ... if that field is blank in my report I would like to color code
the entire row yellow, if not, it can be default (white). I would also like
to color code the row red if there is a date
populated in that field "Date Oath Received" that is more than 30 days from
the date in the "Date of Appointment" field

This is what I have so far and nothing happens:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


'this is where I would like to identify specific colors
'for certain items on a report
'------------------------------------------------------
'If the date oath was received is beyond 30 days from
'date of authentication, color = red
'------------------------------------------------------
'if the no oath of office has been received
'color = yellow
'------------------------------------------------------

If Me.[Date Oath Received] >= Date And Me.[Date of Appointment] <=
DateAdd("d", -30, Date) Then
Me.[Date Oath Received].BackColor = vbRed
Else
Me.[Date Oath Received].BackColor = vbWhite
End If

Dim C As Control
For Each C In Me.Section(0).Controls
If TypeOf C Is TextBox Or TypeOf C Is Label Then
C.BackColor = Me.[Date Oath Received].BackColor
End If
Next
End Sub

I don't see how the current date fits into your
requirements.

Is "Date of Appointment" the same as "date of
authentication"?

You might get a more desirable visual effect by setting all
the label and text box's BackStyle to Transparent and use
your code to set the section's BackColor.

Try that and see if this logic does a better job:

If Me.[Date Oath Received] >=DateAdd("d", -30, Me.[Date of
Appointment]) Then
Me.Section(0).BackColor = vbRed
ElseIf IsNull(Me.[Date Oath Received]) Then
Me.Section(0).BackColor = vbYellow
Else
Me.Section(0).BackColor = vbWhite
End If

 

[joseph]

Marshall

Thank you for the reply. Yes, Date Oath Received is a different field from
Date of Appointment ... members are appointed and are then required to submit
a oath letter within 30 days of appointment date. That's why I need two
conditions ... color yellow when Date Oath Received is null and Date Oath
Received > 30 days from Date of Appointment is RED ...

I inserted your code ... I clicked debug ... was cool, then clicked the
symbol for run ... it ran through, the screen "flickered" and the report
displayed with RED backcolor ... THANK YOU ...

however, I have not been able to repeat it ... when go back to the beginning
of my db, click on reports, select the report that I have initiated this
formatting, it doesn't work ...

why can't I get it to run in VBA again?

Did I need the section of code that read Dim C as control ...etc.etc.?

I tried to follow the code that was provided in a previous post and I came
up w/ the
following for my db ... (what I'm looking to do is I have a report that
contains about 10 fields, of which, I have a field known as "Date Oath
Received" ... if that field is blank in my report I would like to color code
the entire row yellow, if not, it can be default (white). I would also like
to color code the row red if there is a date
populated in that field "Date Oath Received" that is more than 30 days from
the date in the "Date of Appointment" field

This is what I have so far and nothing happens:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


'this is where I would like to identify specific colors
'for certain items on a report
'------------------------------------------------------
'If the date oath was received is beyond 30 days from
'date of authentication, color = red
'------------------------------------------------------
'if the no oath of office has been received
'color = yellow
'------------------------------------------------------

If Me.[Date Oath Received] >= Date And Me.[Date of Appointment] <=
DateAdd("d", -30, Date) Then
Me.[Date Oath Received].BackColor = vbRed
Else
Me.[Date Oath Received].BackColor = vbWhite
End If

Dim C As Control
For Each C In Me.Section(0).Controls
If TypeOf C Is TextBox Or TypeOf C Is Label Then
C.BackColor = Me.[Date Oath Received].BackColor
End If
Next
End Sub

I don't see how the current date fits into your
requirements.

Is "Date of Appointment" the same as "date of
authentication"?

You might get a more desirable visual effect by setting all
the label and text box's BackStyle to Transparent and use
your code to set the section's BackColor.

Try that and see if this logic does a better job:

If Me.[Date Oath Received] >=DateAdd("d", -30, Me.[Date of
Appointment]) Then
Me.Section(0).BackColor = vbRed
ElseIf IsNull(Me.[Date Oath Received]) Then
Me.Section(0).BackColor = vbYellow
Else
Me.Section(0).BackColor = vbWhite
End If

 

[Marshall Barton]

Thank you for the reply. Yes, Date Oath Received is a different field from
Date of Appointment ... members are appointed and are then required to submit
a oath letter within 30 days of appointment date. That's why I need two
conditions ... color yellow when Date Oath Received is null and Date Oath
Received > 30 days from Date of Appointment is RED ...

I inserted your code ... I clicked debug ... was cool, then clicked the
symbol for run ... it ran through, the screen "flickered" and the report
displayed with RED backcolor ... THANK YOU ...

however, I have not been able to repeat it ... when go back to the beginning
of my db, click on reports, select the report that I have initiated this
formatting, it doesn't work ...

why can't I get it to run in VBA again?

Did I need the section of code that read Dim C as control ...etc.etc.?

If the control's BackStyle is Transparent and you set the
section's BackColor, then, No, you do not ahve any use for
the loop.

I am concerned when you say that you clicked Run. That
implies that you cold be editing code while the report is
being previewed, which is a common cause of corruption. You
should ***always*** switch to design view before making
**any** changes to a form or report's module.

Did you try the report with rexords to show all the
different conditions?

Open the report in design view and check that your code is
still there. If it appears to be there, post a Copy/Paste
of the procedure so I can review it.

 

[joseph]

Okay, I think I got ya about clicking run ... I closed out of my db (saved
everything) ... reopened, and opened report and it displayed red, however, it
doesn't follow my code, it applies the red backcolor to alternating records

here is the code, thank you Marshall, I'm out for the day til the am

Private Sub Report_Load()

'this is where I would like to identify specific colors
'for certain items on a report
'------------------------------------------------------
'If the date oath was received is beyond 30 days from
'date of authentication, color = red
'------------------------------------------------------
'if the no oath of office has been received
'color = yellow
'------------------------------------------------------

If Me.[Date Oath Received] >= DateAdd("d", -30, Me.[Date of Appointment]) Then
Me.Section(0).BackColor = vbRed
ElseIf IsNull(Me.[Date Oath Received]) Then
Me.Section(0).BackColor = vbYellow
Else
Me.Section(0).BackColor = vbWhite


End If

End Sub

 

[Marshall Barton]

Okay, I think I got ya about clicking run ... I closed out of my db (saved
everything) ... reopened, and opened report and it displayed red, however, it
doesn't follow my code, it applies the red backcolor to alternating records

here is the code, thank you Marshall, I'm out for the day til the am

Private Sub Report_Load()

'this is where I would like to identify specific colors
'for certain items on a report
'------------------------------------------------------
'If the date oath was received is beyond 30 days from
'date of authentication, color = red
'------------------------------------------------------
'if the no oath of office has been received
'color = yellow
'------------------------------------------------------

If Me.[Date Oath Received] >= DateAdd("d", -30, Me.[Date of Appointment]) Then
Me.Section(0).BackColor = vbRed
ElseIf IsNull(Me.[Date Oath Received]) Then
Me.Section(0).BackColor = vbYellow
Else
Me.Section(0).BackColor = vbWhite
End If

I-have never used the new A2007 Load event and have no idea
what it's good for.

Go back to using the section's Format event and display the
report in Preview.

 

[joseph]

Marshall,

Thank you for your assistance ... once I changed the alternate color for the
detail section to "no color" and changed the code to read =DateAdd("d", 30,
....

everything worked just fine

Thank you thank you thank you

Joseph

Okay, I think I got ya about clicking run ... I closed out of my db (saved
everything) ... reopened, and opened report and it displayed red, however, it
doesn't follow my code, it applies the red backcolor to alternating records

here is the code, thank you Marshall, I'm out for the day til the am

Private Sub Report_Load()

'this is where I would like to identify specific colors
'for certain items on a report
'------------------------------------------------------
'If the date oath was received is beyond 30 days from
'date of authentication, color = red
'------------------------------------------------------
'if the no oath of office has been received
'color = yellow
'------------------------------------------------------

If Me.[Date Oath Received] >= DateAdd("d", -30, Me.[Date of Appointment]) Then
Me.Section(0).BackColor = vbRed
ElseIf IsNull(Me.[Date Oath Received]) Then
Me.Section(0).BackColor = vbYellow
Else
Me.Section(0).BackColor = vbWhite
End If

I-have never used the new A2007 Load event and have no idea
what it's good for.

Go back to using the section's Format event and display the
report in Preview.