Conditional formatting a row in data sheet view

[mtagliaferri]

I need to highlight certain rows in a data sheet form to get immediate attention according to some date parameters:
- how can I highlight the entire row in a specific colour if a date is gone over 21 days as of current date,
or/and
- how can I highlight the entire row in a specific colour if one field in that record has a particular values (3 letter code "AAA")

Has Access have this option?

 

[isladogs]

Not possible in datasheet view AFAIK

Change to a continuous form and use conditional formatting for each field using those criteria.
Or change all field to have a transparent background and place a hidden box control behind them.
When your conditions are met, change the box which is the colour you want to be visible

 

[mtagliaferri]

.
Or change all field to have a transparent background and place a hidden box control behind them.
When your conditions are met, change the box which is the colour you want to be visible

You mean that this is applicable to a datasheet view or I will still have to a continuous form?

 

[pbaldy]

I believe Conditional Formatting will work in datasheet view, though I'm not on a computer right now so can't test. You'd apply the condition to all the textboxes.

 

[isladogs]

I was also away from my computer when I replied earlier.
I was wrong - CF does work in datasheet view as well.

 

[missinglinq]

...use conditional formatting for each field using those criteria...

And note that you don't have to do this individually, for each Field, you can do it for all Fields in one fell swoop. In Form Design View

1. Hold down <Shift> and Left Click on each Control in turn.
2. In pre-2007, on the Menu go to Format - Conditional Format
3. In 2007 and later, on the Ribbon, click on the Design Tab, then Click on the Conditional Icon
4. Under Condition1 use the Down Arrow to select Expression Is
5. Enter your criteria
6. Click on the 'Paint Bucket' Icon
7. Select the color you want.
8. Click OK
   

You're done!

Linq ;0)>

 

[Galaxiom]

I was also away from my computer when I replied earlier.
I was wrong - CF does work in datasheet view as well.

I expect your initial conclusion is because ConditionalFormatting cannot be applied to a table and you seem to think Datasheet Form is a view of the table. You came to the same mistaken conclusion here recently and I replied on the same thread.

Datasheet is actually a type of Continuous Form. What you see are Controls and Labels formatted to look like a table. ConditionalFormatting can be applied to those controls.

Datasheet Form even has Header and Footer sections. They aren't displayed but controls on them can be referred to in code.

 

[isladogs]

I expect your initial conclusion is because ConditionalFormatting cannot be applied to a table and you seem to think Datasheet Form is a view of the table. You came to the same mistaken conclusion here recently and I replied on the same thread.

Datasheet is actually a type of Continuous Form. What you see are Controls and Labels formatted to look like a table. ConditionalFormatting can be applied to those controls.

Datasheet Form even has Header and Footer sections. They aren't displayed but controls on them can be referred to in code.

Thanks for your comment Greg but you are wrong about my initial conclusion.

As already stated, I was away from my computer when I posted my comment and made a mistake which I later corrected.
My memory was at fault - not my understanding.

I have now replied to the point you raised on the other thread
Access 'features' that should be deprecated

Sorry Greg - but

you came to the same mistaken conclusion

about my understanding on that thread as well.

 

[Galaxiom]

Thanks for your comment Greg but you are wrong about my initial conclusion.

Sorry Greg - but about my understanding on that thread as well.

Thanks for clearing that up. I expect you can appreciate how I may have come to that impression.

 

[isladogs]

I expect you can appreciate how I may have come to that impression.

Not really, no!
Anyway, I think we've probably done this point to death.

Do have a look at Allen Browne's code in the link I mentioned.
The new (temporary) link provided by Allen Browne by email is:

http://new.allenbrowne.com/func-DAO.html#StandardProperties

Apparently he is transitioning to a new server

 

[mtagliaferri]

Thanks everyone, I have managed to create some conditional formatting, however I need some more twitching to make it perfect, I have the following fields:
[Date], [Event], [Expire]
I have managed to format [Expire] to a red colour if expire is over Now()
Now I need:
if [Event] = "SCK" and Now() is more than 7 days from [Date] I need [Date] to be Yellow or Orange for example.....
How can I get this done?

 

[isladogs]

Add another condition to match your description

You can have multiple conditions to match your needs.

 

[mtagliaferri]

I am struggling with the code:banghead:

 

[isladogs]

In form design view, select the CF wizard

Click New Rule
Change left drop down to Expression Is

In the right hand box type:
[Event]="SCK" And [Date]+7<Now()

then format the font colour or background colour as you want

Click OK.
That's it

 

[CJ_London]

problem may because you are using date as a field name - it is a reserved word and means today (i.e. like now() but with a time value of zero - midnight)

 

[mtagliaferri]

Thanks Colin and CJ_London,
it work perfectly, CJ_London my field name is actually [EventDate] I just wrote [Date] to make it simple, I have in the past fallen into this error!!!

 

[isladogs]

Suggest you always use your real field names as then you may be able to copy and paste the solution unedited

 

[CJ_London]

it will also stop you getting lectures about using reserved words and make you look more professional