Question How to set conditional formatting for a field just once? (1 Viewer)

pdipietro

New member
Local time
Today, 19:27
Joined
Apr 12, 2019
Messages
8
Well, this is my first post in this forum.

I have a medium complex database, in Access 2019, with more than 100 tables, a lot of relationships and, of course, a large set of forms and reports.

Those forms and reports often uses the same fields from the same tables.

Just as a sample, I have a person Table with a PersonalID fields, defined by the Italian Departmen of Finance. Each citizen has exactly one.
It must follow this pattern(in Access): AAAAAA00A00A000A with a length of exactly 16.
And, if it conformed to the pattern, there is a microservice to validate it with the Finance Department: correct or not correct.

Well, I would like to show an incorrect PersonalID fields in red, wherever it is used, defining just once this conditional formatting.

AFAIK, at the moment I need to rewrite the conditional formatting rule every time I use the field in a form and in a report. And I cannot define it for a table view.

This is not only annoying, but also prone of mainteinance errors.

Is there anyone that has an idea on how to solve the problem or to find a workaround for it?

TIA

Paolo
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:27
Joined
Oct 29, 2018
Messages
21,358
Hi Paolo. Welcome to the forum.
Conditional Formatting applies only to controls, so you won’t be able to avoid applying the condition to each control. However, you can create a function for validating the field’s value in your control to see if it meets the condition or not, which you can then call in your CF expression. This should at least save you some keystrokes.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:27
Joined
Feb 19, 2013
Messages
16,553
does it need to be conditionally formatted? - only essential for continuous forms/datasheet.

If not you could call a function, passing the control as a parameter to set the back colour if required - something like


Code:
function checkFormat(ctrl as Control)
dim valid as Boolean

    'code to check validity e.g.
    valid=len(ctl)<>16
    if valid then valid=check chars etc

    if not valid then ctrl.backcolor=vbred

end function
you would need to call it in your form current event and the control after update event

for the latter, rather than using the afterupdate event to call the function you can use it in the properties i.e. instead of using [Event Procedure], use

=checkFormat(ctlname)

might even be relevant to have this code before you call the microservice
 

pdipietro

New member
Local time
Today, 19:27
Joined
Apr 12, 2019
Messages
8
thank you, both the answers are usefull!

Trying to go further, what if I create a module named "Validation" and inside a function named with the name of each table?

Like

Function Persone( row as Persone) as boolean
' the function shoudl return true or false after checking each column and, if the column in in error
 
Last edited:

Users who are viewing this thread

Top Bottom