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
If Not Me.Check4 Is Null Then
Me.Check4.Value = False
If Not Me.Check6 Is Null Then
Me.Check6.Value = False
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:
to set the value property of any number of controls
to change the enabled state of any number of controls
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
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
. 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:
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
If focusedControl.Parent Is Screen.ActiveControl.Parent Then
If Not Screen.ActiveControl Is focusedControl Then
If Not Nz(focusedControl.Value, "Null") = _
Nz(Nn(focusedControlCriteriaValue, focusedControl.Value), "Null") Then
On Error Resume Next
For i = 0 To UBound(controlsToHandle)
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)
On Error GoTo 0
Public Function Nn(Value As Variant, valueIfNothing As Variant) As Variant
On Error GoTo ErrorHandler
If Value Is Nothing Then
Nn = valueIfNothing
Nn = Value
On Error GoTo 0
Nn = Value
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 “
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()
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.
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" 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
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)"
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:
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.
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.