Multiple Selection Through Bitmasks

Let me begin by revealing that
I’m not an expert with bitmasks. However, this fact should bring a little
comfort to the uninitiated because it means that anyone can implement multiple
selections using a bitmask field and operators. Odds are, you’ve been using
them for years, but simply didn’t notice it. If you’ve ever set the properties
of a message box using Intellisense, then you’ve used bitmasks.

Notice the screen shot of the Microsoft VBA MsgBox function in action, adorned
with all its Intellisense regalia. When you type the first comma, a drop-down
list of options appears. If you check the help file, you’ll find the table of
options shown below, along with their constant values. If we select vbCritical,
the value used is 16, and the Critical Stop icon is added to the message box.

As you may be aware, there are more options than the ones I show below. In
addition the help file for the MsgBox() function provides these helpful
instructions:

The first group of values (0–5) describes the number and type of buttons
displayed in the dialog box; the second group (16, 32, 48, 64) describes the
icon style; the third group (0, 256, 512) determines which button is the
default; and the fourth group (0, 4096) determines the modality of the message
box. When adding numbers to create a final value for the buttons argument, use
only one number from each group.

That’s basically how a bitmask works … it’s singular value that is made up of
the addition of other distinct values. This example, however familiar, is not
what I consider to be a pure example because it comes with the instruction
disclaimer above. In a perfect world, the sum of two bitmask values can NEVER
equal another value in the list. Thus, pure binary values are usually employed.
See the example in the next section.

Bitmask Demo Example

While the idea behind bitmasks
can seem a little convoluted, the code is very simple. Download the sample application and you’ll
see what I mean. It solves the following problem:

    How do I save a number of Boolean attributes for an
employee?

More specifically, as shown in the form below, how do I save user selections
for the FIVE attributes shown here: Full Time, Hourly, Union, Management,
Amiable? These options are not mutually exclusive. An employee could possess
one or many, all or none of these attributes. An employee could be Hourly and
be in Management. They could belong to the Union and they might even be
Amiable. Or they could possess none of those attributes.

Traditionally, we are inclined to create a field of the table for each
checkbox. But what happens when you come up with another attribute, like
[Terminated]? Back to the table, right? Not necessarily. If you’re using a
bitmask, you simply add another attribute and code for it.

I’ve saved the bitmask options to a table named attribute but strictly
speaking this table of attributes isn’t necessary for this code, but in a SQL
Server application where we use bitmasks, a table of bitmask attributes is
maintained and used in queries to filter results. In the sample code below, we
simply show how to display and save edits. A simple Access query is included,
but no advanced techniques. Let’s consider the code.

Coding the Bitmask

Before we consider the demo code, a word about logical
operators. The And logical operator
is used to determine if a particular bit should be toggled. If the field value
contains the bit we’re looking for, it returns that bit’s value. If not, it
returns zero. It works something like this:

    1 AND 1 = 1
    1 AND 2 = 0
    1 AND 4 = 0
    2 AND 1 = 0
    2 AND 2 = 2
    2 AND 4 = 0
     ‘ Set the checkbox to a Boolean expression …
    Me!chk01 = (lngEmpAttributes And 1) = 1

When saving the selected options back to the database, the Or logical operator is used along with a
variable of type Long Integer. Notice that this doesn’t behave the same as
addition (1+1), which would yield a result of 2. As shown below, 1 Or 1 yields
1 while 1 Or 2 produces a sum of 3. This prevents an overload of operators if
the value is Or’ed more than once.

    1 Or 1 = 1
    1 Or 2 = 3
    1 Or 4 = 5
    2 Or 1 = 3
    2 Or 2 = 2
    2 Or 4 = 6
     ‘ If checked, add the bit to lngTotal
    If Me!chk01 = True Then lngTotal = lngTotal Or 1

Below is the full code listing, but other than what’s described above, it
contains only some code to toggle the font color of the labels to illustrate
the action in the user interface. This is a simple, elegant and extensible
solution for maintaining a variable number of attributes!

Option Compare Database
Option Explicit
Private Sub Form_Current()
On Error Resume Next
    Dim lngEmpAttributes As Long
    ‘ Grab the value for emp_attributes from the bound field
    ‘ If NULL, then use ZERO as a value (uninitialized)
    lngEmpAttributes = Nz(Me!txtEmp_attributes, 0)
    ‘ Set the True/False bit of each of our 5 checkboxes
    Me!chk01 = (lngEmpAttributes And 1) = 1
    Me!chk02 = (lngEmpAttributes And 2) = 2
    Me!chk04 = (lngEmpAttributes And 4) = 4
    Me!chk08 = (lngEmpAttributes And 8) = 8
    Me!chk16 = (lngEmpAttributes And 16) = 16
    ‘ Toggle the color of the labels for demo clarity
    Call UpdateBitmaskLabels
End Sub
Public Function UpdateEmployeeAttributes() As Boolean
On Error Resume Next
    Dim lngTotal As Long
    ‘ For each check box, add its value if checked
    ‘ (this could be handled with a For Each loop if
    ‘  the options were numerous, as with States)
    If Me!chk01 = True Then lngTotal = lngTotal Or 1
    If Me!chk02 = True Then lngTotal = lngTotal Or 2
    If Me!chk04 = True Then lngTotal = lngTotal Or 4
    If Me!chk08 = True Then lngTotal = lngTotal Or 8
    If Me!chk16 = True Then lngTotal = lngTotal Or 16
    ‘ Update the database field with the new total
    Me!txtEmp_attributes = lngTotal
    ‘ Toggle the color of the labels for demo clarity
    Call UpdateBitmaskLabels
End Function
Private Function UpdateBitmaskLabels() As Boolean
On Error Resume Next
    ‘ Reset all label text forecolor to black
    Me!lbl01.ForeColor = vbBlack
    Me!lbl02.ForeColor = vbBlack
    Me!lbl04.ForeColor = vbBlack
    Me!lbl08.ForeColor = vbBlack
    Me!lbl16.ForeColor = vbBlack
    ‘ Toggle forecolor to red where boxes are checked
    If Me!chk01 = True Then Me!lbl01.ForeColor = vbRed
    If Me!chk02 = True Then Me!lbl02.ForeColor = vbRed
    If Me!chk04 = True Then Me!lbl04.ForeColor = vbRed
    If Me!chk08 = True Then Me!lbl08.ForeColor = vbRed
    If Me!chk16 = True Then Me!lbl16.ForeColor = vbRed
End Function

The catch, if there is one, has to do with querying. There are some advanced
techniques that work in SQL Server, but Jet doesn’t support bitwise comparisons
directly. You can, however, create a VBA function to do the comparison and call
it from a query. That’s what was done to return these results. The function
used is displayed below.

Public Function EvalBit(ByVal varBitmask As Variant, lngCompare As Long) As String
On Error Resume Next
    Dim lngBitmask As Long
    If IsNumeric(varBitmask) Then
        lngBitmask = CLng(varBitmask)
    Else
        lngBitmask = 0
    End If
    If (lngBitmask And lngCompare) = lngCompare Then
        EvalBit = “Yes”
    Else
        EvalBit = “”
    End If
End Function

Bitwise Power of Two

Even though the Microsoft MsgBox() function seems to
contradict my assumption, well-formed bitmasks rely on the power of two. That’s
how I’ve always used them, and that’s how it’s described out at The Access Web, in this article:
   The
Access Web: Bits and Bitmasks

The Access MVPs do a better job than I could of explaining the binary aspect,
and they give a similar example to what I’ve outlined above. If you’re still
confused about Bitmasks, take a look at the article cited above. While it may
not be the perfect solution for every multi-select problem, it’s certainly
something to keep in your back pocket.

»


See All Articles by Columnist Danny J. Lesandrini