Pitfalls of conditional formatting in MS Access
Before each MS Access developer, sooner or later there is a question of visual design of forms. In the end, the customer needs a friendly and functional interface, and the worst thing is that he most likely has become accustomed to Excel. And here Access suffers one huge defeat - we can not access the cells. The logic of the DBMS is that the user has to deal with a dynamic data set based on a SQL query or table, which means that we are very limited in formatting. Placing on the form a field connected to a table or a query, we get the whole set of values ​​of this field, and the formatting also applies to the whole set of values, but not to a single cell. By and large, there is no such thing as a cell. There is only one tool that allows us to work with each value separately - conditional formatting.
In the Access interface, the Conditional Formatting button can be found on the Format tab, having previously placed focus on the formatted control element (control). UV can be subjected to only two types of controls: Text Box and Combo Box.
By clicking on the button, we will be able to set up conditional formatting rules in the Conditional Formatting Rules Manager, with the rules below in the list having higher priority.
')
Example. We have two rules:
If we enter 100, the value will satisfy both conditions, and the second rule will be executed, the one that is lower in the list. The background will turn green.
At the VBA level, the tool is represented by two objects: FormatCondition and FormatConditions . The first, in fact, stores the formatting conditions, and the second is a collection of the first.
FormatConditions is also a property for acTextBox and acComboBox controls.
Here, in fact, all the basic concepts, but it was not there! Access does not allow adding new rules through VBA if our control has at least three of them. And if your business problem requires marking in five colors? And if under certain conditions you need to disable the control? Then you have to work with your hands, which is not so scary as it seems. How to do it optimally, I'll tell you now.
First, we write the procedure for creating the first three rules:
In this case, we create rules for all TextBox and ComboBox objects, however, depending on the task, we can write a check for additional conditions. In all three cases, rules based on the expression are added, as indicated by the acExpression parameter, and any arbitrary string is used as the text of the expression, for example, “blablabla”, the second parameter is omitted because you can skip it. Such parameters were used, because this is the easiest and shortest version of the code for creating formatting rules, we will customize them later.
Next, open the Conditional Formatting Manager and in the “Show conditional formatting rules for:” drop-down list, go through all the required fields and add the rules. When adding a rule, by default, we offer the condition “Field value between”, leave as is, fill both empty fields with any letters or numbers, for example “1” and “1”, click OK and create the following. Thus, we save time when manually entering without spending it on unnecessary actions.
When the creation of the required number of rules for all controls is completed, we massively modify these rules in VBA.
To do this, create a modification procedure:
Everything, the rules are ready, the content is formatted. It should be borne in mind that conditional formatting requires a considerable amount of time for calculation, so it is best to use it on forms to which Refresh, Redraw, Recalc and Requery will not be applied. If at the stage of form opening the user can still wait a bit to communicate with her for a long time, then the hangs when redrawing conditional formatting while working with the form will make this work almost impossible.
This example shows the mass creation of a large number of identical rules for different controls. Naturally, depending on the task, it will be necessary to do many different checks, and this method will not work for a large number of controls, with different sets of rules. Although, if these sets change over time, it may turn out that it will be much easier to register the settings matrix in the code and rewrite the constants than running the mouse around the screen and pressing different buttons.
Useful links:
» Types of controls
» Properties and methods of the FormatCondition object
» Properties and methods of the FormatConditions object
» FormatConditions.Add Method
In the Access interface, the Conditional Formatting button can be found on the Format tab, having previously placed focus on the formatted control element (control). UV can be subjected to only two types of controls: Text Box and Combo Box.
By clicking on the button, we will be able to set up conditional formatting rules in the Conditional Formatting Rules Manager, with the rules below in the list having higher priority.
')
Example. We have two rules:
- The value must be greater than 50, then the background is red.
- The value should be less than 200, then the background is green.
If we enter 100, the value will satisfy both conditions, and the second rule will be executed, the one that is lower in the list. The background will turn green.
At the VBA level, the tool is represented by two objects: FormatCondition and FormatConditions . The first, in fact, stores the formatting conditions, and the second is a collection of the first.
FormatConditions is also a property for acTextBox and acComboBox controls.
Here, in fact, all the basic concepts, but it was not there! Access does not allow adding new rules through VBA if our control has at least three of them. And if your business problem requires marking in five colors? And if under certain conditions you need to disable the control? Then you have to work with your hands, which is not so scary as it seems. How to do it optimally, I'll tell you now.
First, we write the procedure for creating the first three rules:
Sub setFormatConditions() ' Dim fcnVar As FormatCondition ' FormatCondition Dim frmVar As Form ', Dim ctlVar As Control ' Dim btCtlType As Byte ' On Error Resume Next ' , ' , Set frmVar = Forms![ ] With frmVar ' For Each ctlVar In .Controls btCtlType = ctlVar.ControlType If (btCtlType = acComboBox) Or (btCtlType = acTextBox) Then ' ctlVar.FormatConditions.Delete Set fcnVar = ctlVar.FormatConditions.Add(acExpression, , "blablabla") Set fcnVar = ctlVar.FormatConditions.Add(acExpression, , "blablabla") Set fcnVar = ctlVar.FormatConditions.Add(acExpression, , "blablabla") End If 'btCtlType = acComboBox Next ctlVar 'In .Controls End With 'frmVar Set fcnVar = Nothing Set ctlVar = Nothing Set frmVar = Nothing End Sub In this case, we create rules for all TextBox and ComboBox objects, however, depending on the task, we can write a check for additional conditions. In all three cases, rules based on the expression are added, as indicated by the acExpression parameter, and any arbitrary string is used as the text of the expression, for example, “blablabla”, the second parameter is omitted because you can skip it. Such parameters were used, because this is the easiest and shortest version of the code for creating formatting rules, we will customize them later.
Next, open the Conditional Formatting Manager and in the “Show conditional formatting rules for:” drop-down list, go through all the required fields and add the rules. When adding a rule, by default, we offer the condition “Field value between”, leave as is, fill both empty fields with any letters or numbers, for example “1” and “1”, click OK and create the following. Thus, we save time when manually entering without spending it on unnecessary actions.
When the creation of the required number of rules for all controls is completed, we massively modify these rules in VBA.
To do this, create a modification procedure:
Sub modifyFormatConditions() ' Dim fcnVar As FormatCondition ' FormatCondition Dim frmVar As Form ', Dim ctlVar As Control ' Dim i As Integer On Error Resume Next ' , ' , Set frmVar = Forms![ ] With frmVar ' For Each ctlVar In .Controls btCtlType = ctlVar.ControlType If (btCtlType = acComboBox) Or (btCtlType = acTextBox) Then ' , 0 For i = 0 To 20 ' Modify , Add, 'Modify Nothing, - ddd = ctlVar.FormatConditions(i).Modify(acExpression, , "ololo") Set fcnVar = ctlVar.FormatConditions(i) ' , fcnVar.BackColor = vbRed Next i End If 'btCtlType = acComboBox Next ctlVar 'In .Controls End With 'frmVar Set fcnVar = Nothing Set ctlVar = Nothing Set frmVar = Nothing End Sub Everything, the rules are ready, the content is formatted. It should be borne in mind that conditional formatting requires a considerable amount of time for calculation, so it is best to use it on forms to which Refresh, Redraw, Recalc and Requery will not be applied. If at the stage of form opening the user can still wait a bit to communicate with her for a long time, then the hangs when redrawing conditional formatting while working with the form will make this work almost impossible.
This example shows the mass creation of a large number of identical rules for different controls. Naturally, depending on the task, it will be necessary to do many different checks, and this method will not work for a large number of controls, with different sets of rules. Although, if these sets change over time, it may turn out that it will be much easier to register the settings matrix in the code and rewrite the constants than running the mouse around the screen and pressing different buttons.
Useful links:
» Types of controls
» Properties and methods of the FormatCondition object
» Properties and methods of the FormatConditions object
» FormatConditions.Add Method
Source: https://habr.com/ru/post/310062/
All Articles