Conditional formatting a row in data sheet view (1 Viewer)

mtagliaferri · Jul 16, 2017

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 · Jul 16, 2017

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 · Jul 16, 2017

ridders said:
.
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 · Jul 16, 2017

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 · Jul 16, 2017

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

missinglinq · Jul 17, 2017

ridders said:
...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

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

You're done!

Linq ;0)>

Galaxiom · Jul 18, 2017

ridders said:
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 · Jul 18, 2017

Galaxiom said:
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 · Jul 18, 2017

ridders said:
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 · Jul 18, 2017

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 · Jul 19, 2017

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 · Jul 19, 2017

Add another condition to match your description

You can have multiple conditions to match your needs.

mtagliaferri · Jul 19, 2017

I am struggling with the code:banghead:

isladogs · Jul 19, 2017

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 · Jul 19, 2017

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 · Jul 20, 2017

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 · Jul 20, 2017

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

CJ_London · Jul 20, 2017

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

Conditional formatting a row in data sheet view (1 Viewer)

mtagliaferri

Registered User.

isladogs

MVP / VIP

mtagliaferri

Registered User.

pbaldy

Wino Moderator

isladogs

MVP / VIP

missinglinq

AWF VIP

Galaxiom

Super Moderator

isladogs

MVP / VIP

Galaxiom

Super Moderator

isladogs

MVP / VIP

mtagliaferri

Registered User.

isladogs

MVP / VIP

mtagliaferri

Registered User.

isladogs

MVP / VIP

CJ_London

Super Moderator

mtagliaferri

Registered User.

isladogs

MVP / VIP

CJ_London

Super Moderator

Similar threads

Users who are viewing this thread