E
Emma Hope
Hi All,
I have a form (frmMainform) and within that form is a subform in datasheet
format (frmPolicies). The subform can have many records but only has four
fields 'Reason', 'Date', 'Comments' and 'Complete'. I need to do something
like the following:
1. If the 'Complete' field has a date i.e. is not null, the entire record
needs to be highlighted, preferably text to grey or backcolour to grey. (I
have tried conditional formatting but can only make the 'Complete' field turn
grey, not the others).
2. If the 'Complete' field is empty but the 'Date' field is completed, i
would like to compare it with today's date and if it is more than 30 days old
the entire record turns green (again text or back colour don't mind) and if
it is more than 90 days old turn it to red. Again i suspect i could do a
formula for the coditional formatting but this would only affect the single
field.
I thought i could do some code on the subform datasheet 'OnCurrent' to the
effect of:
If [Complete] is not null then
[Complete].backcolor = RGB(100,100,100)
[Date].backcolor = RGB(100,100,100)
[Comments].backcolor = RGB(100,100,100)
[Reasons].backcolor = RGB(100,100,100)
'(haven't worked out what the RGB for grey is yet so i'm just making those
numbers up)
End if
but it does not work. Is there something really obviously wrong with this or
am i just approaching it from completely the wrong angle?
3. This might be asking a bit too much, but if it is at all possible, i
would also like to do the following, if all of the subforms 'Complete' fields
are filled in, i.e. all the records on the subform are completed and turned
to grey (like above) the parent forms records could also be turned to grey?
Would this be possible?
Thanks for any help you can give.
I have a form (frmMainform) and within that form is a subform in datasheet
format (frmPolicies). The subform can have many records but only has four
fields 'Reason', 'Date', 'Comments' and 'Complete'. I need to do something
like the following:
1. If the 'Complete' field has a date i.e. is not null, the entire record
needs to be highlighted, preferably text to grey or backcolour to grey. (I
have tried conditional formatting but can only make the 'Complete' field turn
grey, not the others).
2. If the 'Complete' field is empty but the 'Date' field is completed, i
would like to compare it with today's date and if it is more than 30 days old
the entire record turns green (again text or back colour don't mind) and if
it is more than 90 days old turn it to red. Again i suspect i could do a
formula for the coditional formatting but this would only affect the single
field.
I thought i could do some code on the subform datasheet 'OnCurrent' to the
effect of:
If [Complete] is not null then
[Complete].backcolor = RGB(100,100,100)
[Date].backcolor = RGB(100,100,100)
[Comments].backcolor = RGB(100,100,100)
[Reasons].backcolor = RGB(100,100,100)
'(haven't worked out what the RGB for grey is yet so i'm just making those
numbers up)
End if
but it does not work. Is there something really obviously wrong with this or
am i just approaching it from completely the wrong angle?
3. This might be asking a bit too much, but if it is at all possible, i
would also like to do the following, if all of the subforms 'Complete' fields
are filled in, i.e. all the records on the subform are completed and turned
to grey (like above) the parent forms records could also be turned to grey?
Would this be possible?
Thanks for any help you can give.