How to Edit a Calculated Yes/No Field?

Introduction

One feature painfully missing in Access is the ability to edit calculated fields. To do so anyway requires unbound control techniques, i.e. using the form events to write the calculated field into an editable control, and to propagate updates to the underlying field... with one exception!

Changing the state a check box can happen in only two ways: the user clicks on the control (or its label), or presses the space bar while it has the focus. By trapping both events, the form can perform the underlying edit. The same can be said for option buttons and for toggle boxes, the two other controls dealing with yes/no fields.

The technique is useful for data where yes/no information is stored as 'Y'/'N', 1/0, or any other data type incompatible with the underlying -1/0 representation used throughout Access, including Jet and Visual Basic. It can also be put to good use in some special demonstrated in the attached demo database.

This article shows how the solution is reached step-by-step; experienced developers or hasty readers might simply skip to the end and study the final class module directly. It can be easily adapted for option buttons and toggle buttons as well.

Combo Box Mechanics (Form View)

Even if you never paid close attention, you will have registered, at some level, that checking a check box is a two-step process. When the mouse button is depressed, the background turns to grey. You can then move the mouse and notice that this clue disappears whenever the mouse leaves the active zone consisting of the check box control and its label. The change in state only occurs when the button is released, provided the mouse is still in — or has returned to — an active zone.
 
Something similar happens when using the space bar. After pressing it, the background clue appears, and the action occurs only when the bar is released. Naturally, you can't move the bar to change your mind, but pressing any other key — not only the Escape key as one might believe — clears the clue and cancels the event.

When the underlying field is calculated, all the clues work as expected but releasing the mouse button or the space bar generates a soft error: no message box, just a line in the status bar explaining that the control cannot be edited. As expected, the “before update” event isn't fired, but — and this is the real problem — neither is the “click” event! The user clicks, Access obviously recognises and handles the fact, but doesn't propagate the event to the VB module.

This is frustrating but not surprising. The “click” event is always slightly dishonest, in a helpful way perhaps but dishonest nonetheless: the event is also triggered when the user doesn't click anywhere, but uses the space bar! Formally, no click occurred, but since too many sloppy developers assume that clicking is the only modern way to edit a check box, the event has been overloaded. In reality, the event occurs after both the “before update” and “after update” events. As a matter of fact, cancelling “before update” also cancels the “click” event…

This would have been too easy, and this article would not exist. Since there is no “click” event to work with, what other events are there?

An alternative: the Double-Click

Double-clicking a check box doesn't do anything, so this event can be used as an alternative. However, it simply doesn't feel right. The first form in the demo database (Form1) demonstrates this: the “double-click” event is used on a text box, a check box, and a combo box. In each case, the result is to toggle an underlying field (unbound in the case of the check box) between the values 'Y' and 'N'.

Please try it for yourself, and decide if double-clicking is something that users will pick up easily or not. My preference goes to the combo box with added double-click awareness. I use this short-cut so often in Access that it comes naturally. Incidentally, this is the topic of a short “tips & tricks” article: Do you Double-click on Combo Boxes?

Implementing a combo box to edit a 'Y'/'N' coded field is so easy that it must count as the first viable solution. However, it doesn't quite fit the general objective of the current pursuit.

Another alternative is the transparent button. They are very useful to trap a mouse click in several situation, but not this one because all visual clues pertaining to the check box disappear. For example, it is no longer visibly selected. Perhaps a transparent button can be used to create original graphical replacements for the check box, and that could be the topic for another article.

Trapping the Click Event  in Form View

The event should happen when the mouse button is released. So “mouse up” is the event to use, but how to determine whether the mouse is over the active zone? Ideally, the method should be entirely in accordance with the clues already provided by Access.

The “mouse up” event receives the current mouse coordinates as arguments, relative to its own top and left coordinates. In other words, a click happens when:

    X between 0 and Width and Y between 0 and Height

To mimic the behaviour of bound check boxes, the label must be included, but the coordinates are still relative to the check box itself. The calculation needs to take the label offset into account.

    X between Label.OffsetX and Label.OffsetX + Label.Width and
    Y between Label.OffsetY and Label.OffsetY + Label.Height

Note: the offset needs to be calculated from the control's and the label's Left and Top properties; the promising control properties LabelY and LabelY are only used to store the default offset applied to new controls.
The actives zone of a check box have been revealed in the figure by overlaying a rectangle exactly over it. It is surprising to see that the active zone is larger than the check box, but clicking slightly below or to the left of a check box works.

A pixel-by-pixel survey shows however that the conditions outlined above are not entirely accurate.

A click doesn't occur when X = Width or Y = Height of the control (it does for the label!). Conversely, it does occur one pixel “before” at the left and top. This corresponds to values -15 for X or Y, on a monitor at 96 DPI. Access uses X and Y coordinates in twips (there are 1440 twips in an inch) and the actual number of twips per pixel should be obtained from Windows (this will be addressed later); for now, let's use the magical number 15 to represent a pixel.

In the listing below, assume that the source table contains a field FieldYN (either 'Y' or 'N') and that the check box CHK has the control source: “= (FieldYN='Y')”; it also has a linked label LBL. The source expression yields a boolean, but, being calculated, it can't be updated. The following code updated it regardless:

Private Sub CHK_MouseUp( _
    Button As Integer, _
    Shift As Integer, _
    X As Single, _
    Y As Single)
    
    Const OnePixel = 15   ' at 96 DPI
    
    If -OnePixel <= X And X < CHK.Width _
        And -OnePixel <= Y And Y <= CHK.Height _
    Or LBL.Left - CHK.Left <= X _
        And X <= LBL.Left + LBL.Width - CHK.Left _
        And LBL.Top - CHK.Top <= Y _
        And Y <= LBL.Top + LBL.Height - CHK.Top _
    Then
        If FieldYN = "Y" Then
            FieldYN = "N"
        Else
            FieldYN = "Y"
        End If
    End If

End Sub

Select all Open in new window

That wasn't too hard, and it provided an opportunity to visit some of the metrics used in controls and mouse events.

The form Form2 in the demo database demonstrates this technique. It works only in form view. If you switch to datasheet view, the “mouse up” event should read the metrics of the datasheet grid. Before looking into that, let's get the keyboard out of the way.

Interlude at the Space Bar

Since the “click” event isn't propagated by Access for uneditable check boxes, the “key press” might not be either. Luckily, there is no special overloading of this event, and it can be trapped quite easily:

If KeyAscii = vbKeySpace Then ... 

Select all Open in new window

But if the goal is to mimic the standard behaviour as closely as possible, something a little more subtle is needed. As such, the “key press” event will not allow cancelling the event by striking another key.

The trick is to wait for the “key up” event, while monitoring “key down” events. The only accepted sequence of events is: “space bar down, space bar up”. The detection of the sequence only requires one module-level flag, mfSpaceBar in the example below.

Dim mfSpaceBar As Boolean

Private Sub CHK_KeyDown(KeyCode As Integer, Shift As Integer)
    mfSpaceBar = (KeyCode = vbKeySpace)
End Sub

Private Sub CHK_KeyUp(KeyCode As Integer, Shift As Integer)
    If mfSpaceBar And KeyCode = vbKeySpace Then
        ' update FieldYN here
    End If
End Sub

Select all Open in new window

Again, nothing too complicated, and it works both in form view and in datasheet view. Who needs a mouse anyway? Right? Oh, well…

Datasheet Mechanics

This should be easier: there is no label. Alas, there is no check box either! The X and Y coordinates passed to the mouse events are no longer relative to the active control (in form view terminology), but relative to the cell under the mouse pointer. While the mouse button is down, the “mouse move” events are still channelled to the active control, but the metrics are useless.

A close examination of the windows used internally by Access to manage datasheets reveals that there is only one single control, which is moved around over a one-window grid display, while morphing from text box to combo box as appropriate. Check boxes don't even get that much: a dotted line is drawn inside of the box, and that's all. This was a disappointment, because there are simple Windows function calls to find the control under the mouse pointer.

Keyboard events can be handled at the form-level. There is an option called “key preview” which generates form-level keyboard events before the active control's event, but a similar option for mouse events isn't available. By comparing the mouse position from the control's perspective with that from the form's perspective, one could detect if the mouse is still over the cell that was last clicked.

So let's escalate and use directly the screen coordinates. The Windows library “user32” has a function, GetCursorPos(), to get them when needed. There is only one small problem: the coordinates are in pixel, and not in twips. This time, we cannot use the magical number 15, because we are dealing with more than one pixel. Of course, methods to obtain the value for “logical pixel per inch” on any given monitor are readily available all over the Internet, and are even available directly from the Screen object in VB.net. This means that future versions of Access will no longer need that part of the code.

For more information and code samples, please read on the Microsoft Development Network: DPI and Device-Independent Pixels. Use the search box to find the specific reference pages, for example of the the GetCursorPos Function.

Trapping the Click Event  in Datasheet View

The ideas is as follows: when the mouse button is pressed, the mouse coordinates from the control's perspective are compared to those from the screen's perspective, to obtain the real top left corner of the current grid cell. This offset can then be used during the “mouse up” event to determine whether the mouse is still over the same cell.

All Window API (Application Program Interface) calls can be bundled into a single function, returning the absolute screen coordinates in twips.

Private Type POINTAPI   ' used by GetCursorPos
    X As Long
    Y As Long
End Type

' WinAPI declarations
Private Declare Function GetCursorPos Lib "user32" _
    (ByRef pt As POINTAPI) As Long
Private Declare Function GetDC Lib "user32" _
    (ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" _
    (ByVal hwnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" _
   (ByVal hdc As Long, ByVal nIndex As Long) As Long

' … and two constants for GetDeviceCaps:
Private Const LOGPIXELSX As Long = 88&
Private Const LOGPIXELSY As Long = 90&

' Access-compatible mouse coordinates in twips
Private Type PointAcc
    X As Single
    Y As Single
End Type

' module variables: twips per pixel and current offset
Dim Pix2Twip As PointAcc
Dim Offset As PointAcc

Private Function GetMousePos() As PointAcc

    If Pix2Twip.X = 0! Then
        ' obtain metrics from Windows
        
        Dim lngDC As Long
        Dim lngDpiX As Long
        Dim lngDpiY As Long
        
        ' create a "drawing context" using the form's window
        lngDC = GetDC(Me.hwnd)
        If lngDC <> 0 Then
            lngDpiX = GetDeviceCaps(lngDC, LOGPIXELSX)
            lngDpiY = GetDeviceCaps(lngDC, LOGPIXELSY)
            ReleaseDC Me.hwnd, lngDC
        Else
            ' failure? use default values...
            lngDpiX = 96
            lngDpiY = 96
        End If
        Pix2Twip.X = 1440 / lngDpiX
        Pix2Twip.Y = 1440 / lngDpiY
        
    End If
    
    ' get absolute mouse position; convert to twips
    
    Dim pt As POINTAPI
    
    GetCursorPos pt
    GetMousePos.X = pt.X * Pix2Twip.X
    GetMousePos.Y = pt.Y * Pix2Twip.Y
    
End Function

Select all Open in new window

The rest is easy: calculating the offset on “mouse down” and validating the current cell on “mouse up”: if the metrics agree, the mouse is still over the same cell in the datasheet grid.

Private Sub CHK_MouseDown( _
    Button As Integer, _
    Shift As Integer, _
    X As Single, _
    Y As Single)
    
    ' record cell's top-left coordinates
    With GetMousePos
        Offset.X = .X - X
        Offset.Y = .Y - Y
    End With
    
End Sub

Private Sub CHK_MouseUp( _
    Button As Integer, _
    Shift As Integer, _
    X As Single, _
    Y As Single)
    
    ' white-cross intervention
    If Me.SelWidth > 0 Then Exit Sub
    ' validate coordinates for current cell
    With GetMousePos
        If X > Pix2Twip.X And Y > Pix2Twip.Y _
        And X + Offset.X = .X And Y + Offset.Y = .Y Then
            ' update FieldYN here
        End If
        Offset.X = 0: Offset.Y = 0
    End With

End Sub

Select all Open in new window

The “mouse up” function already includes fine-tuning for two special circumstances.

This solution is demonstrated in Form3 or the demo database. Obviously, it doesn't work in form view, or at least not in a user-friendly way.

With both versions of the mouse event handlers, it is now possible to edit a check box bound to a calculated expression. If it needs to work in both form view and datasheet view, a test can be added in the “mouse up” handler:

    If Me.CurrentView = 1 Then
        ' handle form view -- see Form2
    ElseIf Me.CurrentView = 2 Then
        ' handle datasheet view -- see Form3
    End If

Select all Open in new window

Bundling the Event Handlers

Let's count: there are two keyboard events, two mouse events, and the function to obtain the mouse position. When using sound programming techniques, there is one more, because the code to edit the underlying field or fields should not be duplicated in both the “key up” and “mouse up” events. That's still only six, but if you have a dozen check boxes, the module will become unmanageable.

If you need this functionality in your applications, it's best to create a class module, thus avoiding to cut-and-paste the same code over and over.

Since we are really recreating a missing event, it also makes perfect sense to have the class module throw an “on update” event to replace “on click”. This way, the class handles all the generic processing and event watching, but leaves the important part, the actual editing, to the calling form.

The source of the class module, let's call it claEventsCheckbox, in included in the demo database, and listed below. It used several techniques not explained here, so let me try to give an overview.

A class can be instantiated as many times as needed, each instance is an object, and has it's own independent set of variables. It is accessed though an object variable using the class name as type.

The form is also an object, but of the Access Interface Engine. If it has an associated module, a Visual Basic object — an instance of the form's module — will be created when the form is opened. The VB object can “listen” to all sorts of events generated by the form, through specific sub-procedures called “event handlers”. This is what we did all along in this article. The situation will now look like this:


When the VB form object is instantiated, it can use the “form load” event to create other objects, namely an instance of the class module claEventCheckbox. This new object, let's call it an “event listener”, can be initialised by passing it a control of the form: the check box. Just like the VB form object, the event listener can handle the check box events as they occur. Meanwhile the VB form object can also listen in to any events generated by the event listener itself.

So all the code accumulated in the form's module (in grey in the figure) can be moved to the event class module (in black), which can in turn declare an event: the “update” event replacing the missing “click” event. An active instance of the event listener thus contains all the convoluted logic to detect a “click” and signals its occurrence though one single event (in red in the figure).

This seems awfully complicated until a second check box is added. The form will simply need a second event listener, and manage a second “update” event. That is all. The same code is used as many times as needed, in distinct instances, one for every check box.

Note: This is one of the rare situations where object variables actually need to be set to Nothing in order to release the objects from memory. This is because the VB form object has an object variable holding the event listener, which in turn has a variable pointing back to the form (the blue connector in the figure). These “hand holding” objects confuse the internal garbage collector.


Anyway, this is the listing of the class module:

'———————————————————————————————————————————————————————————————————————————————
' Checkbox Events -- class module
' Published on Experts Exchange, www.experts-exchange.com
' Article: How to Edit a Calculated Yes/No Field?
' URL: http://www.experts-exchange.com/A_3247.html
' Author: Markus G Fischer, Geneva, 2010-04
'———————————————————————————————————————————————————————————————————————————————
'
' Purpose:
' ¯¯¯¯¯¯¯¯
' When a checkbox is bound to an expression, it becomes read-only. This module
' can listen to both keyboard and mouse events and detect when the "click"
' event _should_ happen, and raise a cusom event at that time.
'
' Usage:
' ¯¯¯¯¯¯
' In the form module's declaration section:
' Dim WithEvents eliMyCheckBox As claEventsCheckboy ' name of this module
'
' In the form's "load" event:
' Set eliMyCheckBox = New claEventsCheckboy
' eliMyCheckBox.Watch chkMyCheckBox ' name of the calculated checkbox
'
' In the form module:
' Private Sub eliMyCheckBox_Update()
'     ' perform the update on the base field(s) here
' End Sub
'
' Important:
' The following event properties need to be set to [Event Procedure], and
' cannot call a macro or a user-defined function (the form module can however
' have additional event handlers for any of them): "on mouse down", "on mouse
' up", "on dbl click", "on key down", and "on key up".
'
' Notes:
' ¯¯¯¯¯¯
' Before raising the Update event, the current event is cancelled (by a call
' to DoCmd), so that the soft error message "this control cannot be edited"
' does not appear in the status bar.
'
'
Option Compare Database
Option Explicit

' Delcarations
Public Event Update()               ' the event sent to the form
Private WithEvents chk As CheckBox  ' the checkbox to monitor
Private lbl As Label                ' its associated label
Private frm As Form                 ' the form

' WinAPI declarations
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Type used by GetCursorPos
Private Type POINTAPI
    X As Long
    Y As Long
End Type

' Library function needed in this module
Private Declare Function GetCursorPos Lib "user32" _
    (ByRef pt As POINTAPI) As Long
Private Declare Function GetDC Lib "user32" _
    (ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" _
    (ByVal hwnd As Long, ByVal hdc As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" _
   (ByVal hdc As Long, ByVal nIndex As Long) As Long

' Useful constants for GetDeviceCaps
Private Const LOGPIXELSX As Long = 88&
Private Const LOGPIXELSY As Long = 90&

' Further class declarations
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
' Again a point structure, but in twips
Private Type PointAcc
    X As Single
    Y As Single
End Type
' used for "twips per pixel" and the current offset
Dim Pix2Twip As PointAcc
Dim Offset As PointAcc
' keyboard: monitor the space bar
Dim mfSpaceBar As Boolean

Public Sub Watch(pchkToEdit As CheckBox)
'
' Only public method of the class, used to initialise monitoring.
'
' Usage: <object>.Watch <chek box>
'
    Dim Daddy As Object
    
    Set chk = pchkToEdit
    ' find the associated label
    If chk.Controls.Count Then Set lbl = chk.Controls(0)
    ' find the form (note: the parent could be a tab control)
    Set Daddy = chk.Parent
    Do Until TypeName(Daddy) Like "Form_*"
        Set Daddy = Daddy.Parent
    Loop
    Set frm = Daddy
    
    ' Useful while designing: event sanity check
    Debug.Assert chk.OnKeyDown = "[Event Procedure]"
    Debug.Assert chk.OnKeyUp = "[Event Procedure]"
    Debug.Assert chk.OnMouseDown = "[Event Procedure]"
    Debug.Assert chk.OnMouseUp = "[Event Procedure]"
    Debug.Assert chk.OnDblClick = "[Event Procedure]"
    
End Sub

Private Function GetMousePos() As PointAcc
'
' Calls the WinAPI function returning the mouse (cursor) position, and
' converts the values from pixel to twips.
'
    Dim pt As POINTAPI
    
    GetCursorPos pt
    GetMousePos.X = pt.X * Pix2Twip.X
    GetMousePos.Y = pt.Y * Pix2Twip.Y
    
End Function

Private Sub chk_DblClick(Cancel As Integer)
' disable double-click for checkboxes
    Cancel = True                   ' works in form view
    Offset.X = 0: Offset.Y = 0      ' added for datasheet view
End Sub

Private Sub chk_KeyDown(KeyCode As Integer, Shift As Integer)
' monitor only the spacebar
    mfSpaceBar = (KeyCode = vbKeySpace)
End Sub

Private Sub chk_KeyUp(KeyCode As Integer, Shift As Integer)
' raise the event after space bar "down" and "up" events
    If mfSpaceBar And KeyCode = vbKeySpace Then
        KeyCode = 0   ' prevent event propagation
        RaiseEvent Update
    End If
End Sub

Private Sub chk_MouseDown( _
    Button As Integer, _
    Shift As Integer, _
    X As Single, _
    Y As Single)
'
' stores the current offset (top left position of the current grid cell)
'
    If frm.CurrentView = 2 Then
        ' only needed in datasheet view
        With GetMousePos
            Offset.X = .X - X
            Offset.Y = .Y - Y
        End With
    End If
    
End Sub

Private Sub chk_MouseUp( _
    Button As Integer, _
    Shift As Integer, _
    X As Single, _
    Y As Single)
'
' Determines whether the event "counts" and should generate a "click" event.
'
    Dim fIsClick As Boolean
    
    If frm.CurrentView = 1 Then
        ' Form View:
        ' is the mouse still over the checkbox?
        fIsClick = -Pix2Twip.X <= X And X < chk.Width _
              And -Pix2Twip.Y <= Y And Y <= chk.Height
        ' or over the label?
        If Not lbl Is Nothing And Not fIsClick Then
            fIsClick = lbl.Left - chk.Left <= X _
                And X <= lbl.Left + lbl.Width - chk.Left _
                And lbl.Top - chk.Top <= Y _
                And Y <= lbl.Top + lbl.Height - chk.Top
        End If
        If fIsClick Then
            DoCmd.CancelEvent
            RaiseEvent Update
        End If
        
    ElseIf frm.CurrentView = 2 Then
        ' Datasheet View:
        ' no edit occurs when cells are selected
        If frm.SelWidth > 0 Then Exit Sub
        ' compare control's coordinate with screen's coordinates
        With GetMousePos
            If X > Pix2Twip.X And Y > Pix2Twip.Y _
            And X + Offset.X = .X And Y + Offset.Y = .Y Then
                DoCmd.CancelEvent
                RaiseEvent Update
            End If
        End With
    End If
    
End Sub

Private Sub Class_Initialize()
'
' Gets the monitor's DPI settings, in order to calculate "twips per pixel"
' values in both X and Y directions.
'
    Dim lngDC As Long
    Dim lngDpiX As Long
    Dim lngDpiY As Long
    
    ' get a "drawing context" compatible with the application window
    lngDC = GetDC(Application.hWndAccessApp)
    If lngDC <> 0 Then
        ' request both DPI settings
        lngDpiX = GetDeviceCaps(lngDC, LOGPIXELSX)
        lngDpiY = GetDeviceCaps(lngDC, LOGPIXELSY)
        ' release used memory
        ReleaseDC Application.hWndAccessApp, lngDC
    Else
        ' failure? use default values (damage control)
        lngDpiX = 96
        lngDpiY = 96
    End If
    ' store the conversion factors
    Pix2Twip.X = 1440 / lngDpiX
    Pix2Twip.Y = 1440 / lngDpiY
    
End Sub

Private Sub Class_Terminate()
'
' Relase objects. This is actually necessary for the Form object to avoid the
' "hand-holding" bug: this class instance holds the form; the form holds this
' class instance. This can prevent both from being released from memory when
' the form is closed, and even when the database is closed. This ultimately
' prevents Access from closing alltogether.
'
    Set chk = Nothing
    Set lbl = Nothing
    Set frm = Nothing
    
End Sub

Select all Open in new window

(open in a new tab)

Usage instructions are included. The simplest form module using it would be like this:

' declaration of a typed object variable
Dim WithEvents Listener As claEventsCheckbox

Private Sub Form_Close()
    ' release the object from memory
    Set Listener = Nothing
End Sub

Private Sub Form_Load()
    ' create a new instance and initialise it
    Set Listener = New claEventsCheckbox
    Listener.Watch MyCheckbox
End Sub

Private Sub Listener_Update()
    ' handle the update event here...
End Sub

Select all Open in new window

Note: five event properties of MyCheckbox need to be set to “[Event Procedure]”, namely “on key down”, “on key up”, “on mouse down”, “on mouse up”, and “on double click”. If one of them is missing, the code will stop at the corresponding Debug.Assert (lines 105–109) of the initialisation method Watch.

A more substantial example is provided in the demo database.


Option Buttons and Toggle Buttons

The methods described here work in exactly the same way with option buttons (sometimes called radio buttons). They are really just rounded check boxes, and the class module can be modified to expect and treat option buttons instead. However, it is not easy to use the same class for both types of controls. This is because of the “with events” declaration, which requires a fully qualified control type.

If you need the same functionality for an option button, make a copy of the class module and change “CheckBox” to “OptionButton” (in two places).

Toggle buttons can also be used to edit yes/no fields, and a class module can be created for those as well. Again, it is sufficient to change “CheckBox” to “ToggleButton”, although the exact metrics to determine if a valid click occurred might have to be adjusted. Toggles buttons do not look nice on a datasheet, but the code does work as intended if needed. Finally, they don't normally have an associated label, but if they do the label is handled correctly.

The Demo Database

The attached Access 2000 format database contains all the forms mentioned in the article and of course the class module.
IsClick.mdb
Table1 contains various representations of boolean values not stored as yes/no fields; Query1 converts or extracts boolean values from them (for display only); the form frmCheckboxes uses the class module claEventsCheckbox to implement editing of each calculated boolean query field, in both form view and datasheet view.

Different types of boolean values are treated, including triple state fields (with Nulls allowed), combined booleans, and meta-data booleans.

Conclusion

In years of Access coding, I have encountered this problem many times over. I have tried several solutions, some not even mentioned in the article, and most often ended up with the combo box solution described as an alternative.

For some reason, I never tackled the exercise “how to edit a read-only check box” as such. Perhaps because my first reaction was to mentally object (rather vigorously) to the decision not to propagate the click event, and to start daydreaming how deleting a single line in the Access source code would solve the problem. Really, the form's module instance can trap clicks on almost anything: locked text boxes, rectangles, section backgrounds, even the application background visible outside of the grid in datasheet view... One exception: a calculated check box.

This spring, I was faced with the same requirement one more time and, after the well-threaded mental circuit, I decided to write the hundred and some lines to work around the problem. The research and development made me think of some other ways to use calculated booleans, like for bit-field data (single bits of information packed into one integer value) and for all sorts of meta-data.


Anyway, I have a new item in my bag of tricks, and so do you if you ever need it.


Markus G Fischer
(°v°)

¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
This article is part of the EE-book unrestricted Access.