Manage business logic in Access forms

• Download source code - 97.3 KB

Introduction: Poor databinding in Access

I guess that the three most important properties of all basic entry controls are the value-, the enabled- and the visible-property. These properties often respond to the business logic they represent. For example, a textbox will update its value-property to show the recalculated total amount of an order after a purchase item was added and a credit card number textbox will be disabled or hidden when the user clicks a radio button to pay by bank transfer.

Lucky one, if the technology you are working with allows you to link the property values of your controls directly to the business logic, as it is possible in WPF. Pitiable one if you are still using a technology which needs you to do everything by hand in code-behind, as it needs to be done in Microsoft Access.

Me, I am both lucky and pitiable, because I “enjoy” both. I grew up with Access-VBA, went on to WinForms, and later moved on to WPF, when some weeks ago I got a flash back when I was honored to work on a project which contained a form populated by dozens of primitive data-entry controls: most of them being checkboxes, textboxes, and combos. The business logic behind required that almost every user action had an impact on the other controls. One being set to false, the other one being enabled, and the next one got invisible. In respect to the large number of controls being involved and the fact that the domain experts themselves were still in experimental phase, I saw a maintenance nightmare in front of me manifested like this:

Private Sub Check0_Click()
If Me.Check0.Value = True Then
    If Not Me.Check2 Is Null Then
        Me.Check2.Value = False
    End If
    If Not Me.Check4 Is Null Then
        Me.Check4.Value = False
    End If
    If Not Me.Check6 Is Null Then
        Me.Check6.Value = False
    End If
    ' To be continued...
End If 
End Sub

The SetControl-Functions

Being unable to add advanced databinding to Access, I tried the second best: creating a set of re-usable functions, being as flexible as possible to set the property values of the controls. By doing so, the code above transforms to this:

SetControlsValue Me.Check0, False, True, False, Me.Check2, Me.Check4, Me.Check6

In total, there exists three functions:

• SetControlsValue

to set the value property of any number of controls

• SetControlsEnabled

to change the enabled state of any number of controls

• SetControlsVisible

to change the visible state of any number of controls

SetControlsValue-function in detail

Let’s look at some more variants of the SetControlsValue-function by modifying some parameters from the function call above:

SetControlsValue Nothing, False, True, False, Me.Check2, Me.Check4, Me.Check6

The first parameter represents the control which must be the active control for the function to execute. If you want the function always being executed when being called, you set the first parameter to Nothing.

SetControlsValue Me.Check0, Nz(Me.Check0,False), True, False, Me.Check2, Me.Check4, Me.Check6

The second parameter represents the property-value for the controls to set. You can reference another control’s value or use an inline expressions like Iif, Nz, and so on instead of a fixed value.

SetControlsValue Me.Check0, False, Nothing, False, Me.Check2, Me.Check4, Me.Check6

The third parameter acts as a filter. In the very first example (1) above, the function only executed if “Me.Check0.Value = True” was true. If you always want the function to execute, you set the third parameter to

Nothing

. Note that this filter only works if the function call is bound to a specific control defined by the first parameter. This means that in function call (2), the criteria filter is being ignored.

SetControlsValue Me.Check0, False, True, Nothing, Me.Check2, Me.Check4, Me.Check6

The fourth parameter is another filter. This time it applies to the controls whose value-property may be changed. If set to Nothing, the controls property-value will always change.

All other parameters represent the controls whose value may be changed. The function call needs at least one control and accepts as many as needed.

Below you see the code of the SetControlsValue-function in detail. I added some comments to the code to help you better understand what happens:

'Sets value property of multiple controls
'focusedControl     : If not nothing, this control must be the active control
'                     for the function to execute. In modal forms the function is always executed.
'newValue           : New value of the property
'focusedControlCriteriaValue  : If not nothing, only if the focused control's value
'                     corresponds to this value the function will execute
'controlToHandleCriteriaValue : If not nothing, only those controls whose value
'               correspond to the criteria value will get the new value
'controlsToHandle   : Controls whose value property may be changed.
Public Sub SetControlsValue(focusedControl As Control, newValue As Variant, _
       focusedControlCriteriaValue As Variant, controlToHandleCriteriaValue As Variant, _
       ParamArray controlsToHandle() As Variant)
    
    Dim i As Integer
    
    If Not focusedControl Is Nothing Then
        ' Workaround: in modal forms the Screen.ActiveControl remains the control on the calling form
        If focusedControl.Parent Is Screen.ActiveControl.Parent Then
            If Not Screen.ActiveControl Is focusedControl Then
                Exit Sub
            End If
        End If
        ' Apply focusedControl filter
        If Not Nz(focusedControl.Value, "Null") = _
               Nz(Nn(focusedControlCriteriaValue, focusedControl.Value), "Null") Then
            Exit Sub
        End If
    End If
           
    On Error Resume Next
        For i = 0 To UBound(controlsToHandle)
            ' Do not apply new value to the control which executed the function
            If Not controlsToHandle(i) Is focusedControl Then
                If Nz(controlsToHandle(i).Value, True) = _
                        Nz(Nn(controlToHandleCriteriaValue, controlsToHandle(i).Value), True) Then
                    controlsToHandle(i).Value = newValue
                    If Err.Number <> 0 Then
                        MsgBox ("Error when trying to set the value ' " & newValue & "' for control '" & _
                                controlsToHandle(i).Name & "'." & _
                                vbNewLine & vbNewLine & "Original error message:" & _
                                vbNewLine & Err.Description)
                    End If
                End If
            End If
        Next
    On Error GoTo 0
   
End Sub

'Similar to Nz, except that a value of Nothing (instead of Null) will be replaced
Public Function Nn(Value As Variant, valueIfNothing As Variant) As Variant
    On Error GoTo ErrorHandler
        If Value Is Nothing Then
            Nn = valueIfNothing
        Else
            Nn = Value
        End If
    On Error GoTo 0
    
    Exit Function
    
ErrorHandler:
    Nn = Value
End Function

When implementing the functions, it was somewhat tricky to enable the user to enter the optional parameter values for filtering, because the optional modifier can’t be used in combination with the paramArray needed to define the signature for an unlimited number of controls to set. Inspired by .NET, I opted for “Nothing” to indicate that no filter should be applied. Unfortunately, testing for the filter passed as variant for “Nothing” in VBA is not straightforward, because you can only test “Is Nothing” for objects; trying to test it when the variant wraps a primitive data type like an integer raises an error. I also found no way to check if the variant was holding an object or a primitive data type. This is why I needed to work with “On Error” in one place. Tell me, if you know a better way!

Avoid procedure spamming when attaching events to controls

I know it is comfortable using the designer in the property-sheets window to produce events by double clicking looking like this:

Private Sub txt0_Click()
End Sub

Unfortunately, this way you are going to spam your code behind with lots of procedures which favours fragmented, repeatable code hard to maintain. Whenever possible, try to create a central procedure catching all related events. Do this by creating a function in the code-behind first. Then return to the designer and attach this function to the controls intended to use it. You do that by navigating to the event in the Property Sheet of the control. But instead of double clicking and producing a new event related to just this control, you enter the function name into the line of the chosen event like this:

=ApplyRules_SetValueDifferentControls()

"ApplyRules_SetValueDifferentControls" is the name of a function in the code-behind. If the event contains parameters, the function needs to declare the same parameters in its signature and inside the Property-Sheet you also need to define the parameters inside the string you enter. The conversion to the correct data type is done by VBA later. Of course a string means that the parameters are static.

But you can solve this problem if you attach the function to the control event in the code-behind instead of using the designer. This way you can dynamically build the string and assign an individual event string to each control.

In case you have many controls to handle, to dynamically attach the event-handler to the controls in the code behind is my preferred way. To identify which control to attach to an event, you can use the Tag-property, available by any control. Sometimes you also want to assign the same event-function to different events regarding the type of the control. You can do this by differentiating between the type of the control.

Below is a code snippet taken from the demo project where you see how dynamically attaching events to controls looks like:

For Each ctl In Me.Controls
    If ctl.Tag = "InputControl" Then
        If TypeOf ctl Is CheckBox Then
            ctl.OnClick = "=ApplyRules_SetCheckBoxValues()"
        ElseIf TypeOf ctl Is TextBox Then
            ctl.OnKeyUp = "=ApplyRules_CopyText("CStr(ctl.TabIndex) & ", -1)"
        End If
    End If
Next

This way you avoid one procedure definition per control event and facilitate the use of centralized procedures handling one or more events of many controls. This makes your code more compact and maintainable.

Another useful feature to help you generate more generic event handling is:

Screen.ActiveControl

which makes the function-call sensitive to the control which triggered the event like this (code snippet taken from demo project):

SetControlsValue Screen.ActiveControl, Screen.ActiveControl.Text, Nothing, Nothing, Me.txt0, 
Me.txt1, Me.txt2, Me.txt3, Me.txt4

In summary, applying these techniques combined with the SetControls-functions enables you to considerably reduce the amount of code to write for event handling.

The demo project

I propose you now to take a look at the demo project to get a better understanding of how it works in real.

The demo project shows all three functions in action, whereby the functions themselves reside inside the only module. This combined with the info you already got will help you to get started very fast. You can even further extend the function collection if you have other control properties whose values change often.

For sure, you will never completely get rid of "If's" and other flow-control structures in your event handling code, but applying these techniques will help you reduce them significantly and help make your code more maintainable over time.

Update

When using the SetControl-functions in a modal form (a form that does not permit the user to switch to another form when open), the focused control still remains on the form which opened the modal form. In this situation, using Screen.ActiveControl in the SetControl-functions does not work as described above. I fixed this by adding a check inside each function looking if the Screen.ActiveControl resides on the same form as the focusedControl. In a modal form, this is not the case and the focusedControl defined as a parameter will be ignored.