MS Access VBA to see if a conditional formatting rule is currently active for a control

Question

I have a conditional formatting rule that enables some fields depending on choices earlier. The fields are disabled by default. Once these fields are enabled, they also require input. However since they are conditional I cannot make them required on the database level.

What I've tried is to check in the submit handler if the control is enabled and not empty.

Public Sub SaveButton_Click()
    For Each ctl In Me.Controls
        If (ctl.Tag = "ConditinallyRequiredField") Then
            If (ctl.Enabled = True) Then
                Debug.Print "This is never reached"
                ' Check for empty values.
                If (Len(ctl.Value & vbNullString) = 0) Then
                    MsgBox "One or more required fields are missing input values."
                    GoTo stopSubmit
                End If
            End If
        End If
    Next ctl

    ... Do submit
End Sub

But ctl.Enabled is always false, no matter at what point in time I check it. So it looks like the conditional formatting overrides it without affecting the actual property.

So since ctl.Enabled apparently always remains false, I would like to check if there is a conditional formatting rule active for the given control (there is only one). But so far I have not found to do so. Can it be done?

Answer 1

After a few hours of fiddling around I've come up with a method that uses the SetFocus method to determine if a field is enabled or not. Disabled fields cannot receive focus and will throw an error.

This concept has been wrapped in a function to test and return True or False.

' Helper function. Returns True if the textfield could receive focus.
Private Function TestEnabled(ctl As Control) As Boolean
    On Error GoTo noFocus
    ' Assume the textfield can be focussed.
    TestEnabled = True
    ' Try setting the focus.
    ctl.SetFocus
Exit Function
noFocus:
    ' If the textfield could not receive focus it is not enabled.
    TestEnabled = False
End Function

In the submit handler it is wrapped in an if-statement to test. If the field is enabled but empty, the code breaks but the field remains focussed. This is an added bonus giving a direct guide to the missing field. After testing all fields, the focus is reset to the submit button (just as if the user clicked it) to prevent the last field of the check to remain focussed.

' Submit handler
Public Sub SaveButton_Click()
    For Each ctl In Me.Controls
        ' Only test the tagged fields to prevent labels, etc. from getting tested.
        If (ctl.Tag = "ConditinallyRequiredField") Then
            If (TestEnabled(ctl)) Then
                ' Check for empty values.
                If (Len(ctl.Value & vbNullString) = 0) Then
                    MsgBox "One or more required fields are missing input values."
                    GoTo stopSubmit
                End If
            End If
        End If
    Next ctl
    ' Set Focus to the save button again.
    Me.SaveButton.SetFocus

    ' ... Do submit
End Sub

Note that June7's suggestion of simply testing the same expression from the conditional formatting again in VBA also worked. But since it was based on DLookup that already caused a noticeable slowdown when working via VPN, I wanted to prevent having to run that expression another time.

Also note that this does not provide an answer to the question, but did solve my problem :)

Answer 2

Due to dynamic nature of CF, I doubt can determine if the conditional format is applied at any particular time. None of the CF properties/methods offer this indicator. Most likely why there is no example code to be found. The same condition used in CF rule to enable/disable can be used in VBA to determine control's state.

Can determine if control has any CF rules.
ctl.FormatConditions.Count
Only textboxes and comboboxes can have CF so make sure loop code only tests those controls. Anything else will trigger a "doesn't support this property" error.

Answer 3

They say it's never too late, so here is my solution regarding this topic: N.B. It only works for "expression" type of CF, and all conditions must use the fullpath of the control - [Forms]![FormName]![ControlName] = some_condition, rather the short path - [ControlName] = some_condition. Having said that, it's as easy as looping through all conditions, test if they EVAL to TRUE and, when the first one does - we have a winner.

Public Function GetActiveFC(Ctrl As Access.control) As FormatCondition
Const C_PROC_NAME = "mdl_Functii2022\GetActiveFC"

On Error GoTo Eroare

Dim I As Integer

If TypeOf Ctrl Is Access.TextBox Or TypeOf Ctrl Is Access.ComboBox Then
    If Ctrl.FormatConditions.Count = 0 Then
        Set GetActiveFC = Nothing           'there are no FCs
    Else
        For I = 0 To Ctrl.FormatConditions.Count - 1
            If Eval(Ctrl.FormatConditions(I).Expression1) Then
                Set GetActiveFC = Ctrl.FormatConditions(I)
                Exit For
            End If
        Next
    End If
Else
    Set GetActiveFC = Nothing       'the control does not allow FC
End If
    
Iesire:
Exit Function

Eroare:
Debug.Print C_PROC_NAME, Err.Number, Err.Description
Set GetActiveFC = Nothing
Resume Iesire
End Function

And to use it:

Dim FC as FormatCondition
Set FC = GetActiveFC([ControlName])