conditional format in datasheet (1 Viewer)

[boerbende]

Dear readers

I have a table with
ID status color
1 approved 65280 (=green)
2 rejected 255 (=Red)
etc

In a datasheets some documents have status approved, some have status rejected (and many more statuses)

Is it possible to have the conditional format using the colors I have in the column color?

Many thanks

Ben

 

[CJ_London]

255 is the 'standard' red (vbred) and 65280 the 'standard' green (vbgreen) so you can just set the conditional formatting and select those colors (only one required, set the normal backcolor to the other one)

If you want to use vba code to set conditional formatting you would set the control backcolor to vbred and then run this code

dim fc as formatcondition
set fc=mycontrol.FormatConditions.Add (acExpression, 0, "[myControl]='Approved'", "")
fc.backcolor=vbgreen 'or 65280 if you prefer

change myControl to the name of your status control

The format condition property is part of the control, so setting in vba will only set it whilst the form is open. Once closed, unless you have saved the form design, you will need to run the vba code again next time the form is opened.

If you want to set the format conditions based on colors determined in a table you would need to open a recordset of that table and loop through the records adding each format condition. If so, note that there is a 'bug' when trying to add more than three conditional formats in vba - see this link for a workaround

https://access-programmers.co.uk/forums/showthread.php?t=271679