Continuous forms having different properities for each record

[Rachel Hayes]

I'm trying to apply a conditional formatting property to one field of my continuous form. I want the date field to go red if the date listed is more than 3 months old. I've managed to write some code for this but I can't get the code to be applied to individual records. The code works but only once an individual record has been selected, and it then applies the change in colour to EVERY record on the form. Is there a way that I can get the dates to turn red as the form is opened without having to select each individual record, therefore highlighting to the user which records they need to pay attention to?

Thanks for any help.

Rachel

[beacon]

Hi Rachel,

Welcome to Bytes!

You should be able to put the code in the Form_Load event. Can you post the code you've written so far?

[parodux]

Insert this expression on your conditional formatting:

DateDiff("m";[YourDateField];Date())>3
 

[Rachel Hayes]

Thanks for such quick replies!

This is the code I'm using;


If Date - [PerDateReqInfo] > 90 Then
[PerDateReqInfo].ForeColor = vbRed
Else
[PerDateReqInfo].ForeColor = vbBlack
End If

If I put this on 'On Load Form' it makes every date field in the form Red. I've tried to put it on 'On Current' and this works to a certain extent. It changes the colour from Red to Black but the user has to select each record to make this change happen. I want the form to load and the records over 3 months to already be red, thus drawing attention to them.

I'm hoping this is possible, but my knowledge of VBA is, oh about 1 week old!!

Thanks for any help.

Rachel

[beacon]

After re-reading your post, I think parodux's suggestion would probably serve you best. I overlooked the part about you not wanting to have to touch each record. There may be a way to loop through the controls to get the formatting to work, but it's probably more hassle than it's worth.

I think you should use the Conditional Formatting option under the Format menu. Enter your expression, or parodux's expression, and it should work the way you want.

[Rachel Hayes]

I'm really sorry, I know I'm being dense!! But I can't find the Conditional Formatting option, it is within the Access Properities within design view? Should I be looking under the 'form' properties or the actual field properities?

Sorry!

[parodux]

Sorry I was just elsewhere... Right click on the field in design view!~)

[missinglinq]

As was finally suggested, this kind of thing can only be done, in a Continuous View or Datasheet View Form, thru Conditional Formatting from the Format Menu! Trying to do this in code, regardless of the event used, will, as you've found out, format the field in all records, based on the value in the field in the current record!

The only type of formatting, thru code, that can be done with these kinds of forms involves the Locked or Locked/Enabled Properties.

Using CF thru coding, when it can be done, in a Single View form, or in Datasheet or Continuous View form, as noted in the previous paragraph, has to be done using the Form_Current Event.

Linq ;0)>

[Rachel Hayes]

Thank you, I got it to work in the end!

Rachel