[AccessD] checkboxes aren't functioning

I usually don't do this -- but I forgot who asked the original question. I
don't think there's anything in here to solve the original problem, but
thought some of you might find it helpful. There are two articles (no
figures unfortunately, and the copyrights are intact at the bottom of each).
If anyone has a subscription, I'll be glad to send the links so you can get
figures and all. The first article isn't exactly relevant, but more an
extension of the second article, which is specifically about the Tri-state
property. I should've copied them in reverse order -- sorry about that. 

Susan H. 

Tweak combobox settings to appropriately display and store Boolean values
by Susan Sales Harkins and Gustav Brock
Application(s): Microsoft Access 97/2000/2002/2003
Operating System(s): Microsoft Windows
Access lets you define a field as a Yes/No data type, which denotes a
Boolean value. That means the field stores only two values: -1 and 0. The
checkbox control is the natural and logical choice to interact with this
data type. In fact, the Form wizards automatically use a check box to
present the Yes/No data type in a form. However, there may be times when you
want to use a different control, such as a combobox control. Unfortunately,
other controls aren't as inherently compatible with Boolean values and might
need a bit of tweaking to handle them appropriately. In this article, we'll
show you how to trick both a bound and an unbound combo box into displaying
Boolean values.
No place for maybes
In the Boolean world, there's no maybe-there's only -1 and 0. The Boolean
data type, named Yes/No in Access, represents a two-position state of
activity: True/False, On/Off, Yes/No, and so on. Regardless of the format
you use to display a Yes/No field's values to the user, the underlying
values will always be -1 or 0, where -1 represents the active position (Yes)
and 0 represents the inactive position (No).
As mentioned before, the checkbox control is a natural choice for displaying
Boolean values in a bound control. Like the data type, the check box has two
positions: selected and deselected. Using a control such as a combobox
requires a little work because the combobox control isn't restricted to just
two values. The combobox bound to a Boolean field is the easiest to adapt,
so that's where we'll start. Then, we'll look at a special problem that
crops up when using an unbound combobox with Boolean values.
Related article
For another way to visually present Boolean values, see the De-cember 2003
article "Using the Triple State property to express a Null value in Boolean
controls." You can view the article online at
http://www.elementkjournals.com%2fpremier%2fjournal_archive.asp?vwJournalID=
IMA#200312.
Examining the problems with combobox controls bound to Yes/No fields
The first problem that appears when you bind a combobox control to a Yes/No
field is that the con-trol displays the Boolean values, -1 and 0, instead of
Yes and No. In addition, although the bound combo box displays the
underlying values, the list compo-nent is empty, as shown in Figure A. The
control is actually performing properly by displaying the ac-tual values and
not any representative text, since no representative text has been supplied
yet.
Figure A: A combo box bound to a Yes/No field displays the numeric Boolean
values by default. 

Making bound combobox controls Boolean-friendly
Fortunately, it's easy to get a combo box to appropriately display a Boolean
value. To demonstrate, create a table that includes some Yes/No fields,
similar to the ones shown in Figure A. Add a few records and then create a
new form that's bound to the table. Instead of dragging the Yes/No fields
from the field list, add combobox controls to the form and bind the controls
to the fields by setting the Control Source property appropriately.
Once you've finished setting up the form, switch to Form view and experiment
with the combobox controls. You'll find that they exhibit the behavior
described in the previous section. Now, switch back to Design view and we'll
address the issues.
First, set the control's Format property to Yes/No. The modified control
interprets the underlying value and displays the appropriate text value: Yes
or No. However, the list is still empty.
To create an appropriate list, change the control's Row Source Type property
to Value List. Then, enter a Row Source setting of
Yes;No
Figure B shows the impact that just these few changes have on the control.
The combo box now displays the representative text value in the text box,
and it displays appropriate values as list items.
Figure B: Use the control's Format property to determine the text values and
the Row Source property to set the list values. 

Note that you don't have to choose an item from the control's list. If you
type -1 in the combo box and then press [Enter], the control displays Yes;
type 0 in the combo box and press [Enter] and the control displays No. Keep
in mind that the control's text box accepts any value, not just the values
-1 and 0. Any numeric value other than 0, including negative values, returns
a true result (Yes, True, and On). If you must restrict data input to just
-1 and 0, add a validation rule or use VBA. An unbound combobox behaves in
the same way.
Handling Boolean values in an unbound combobox control
An unbound combobox that displays just Boolean values is a bit unusual, but
certainly acceptable. You can use the properties reviewed in the previous
section to get the unbound control to behave the same way as far as
displaying values is concerned. Just remember, you must use the Format
property to determine the displayed value. If you don't set the Format
property, the control displays the actual input value. Unlike the bound
control, the unbound control has no underlying data type to force a
compatible Boolean entry.
The main difference of concern is the control's value. If you open the table
bound to your sample form, you'll find that the control updated the bound
fields as you'd expect-the Yes/No field stores only -1 and 0. The Yes and No
strings in the combo box are just for show. The bound combobox will always
equal a true or false value (-1 or 0). An unbound control isn't as
predictable.
For this example, create a form and add two unbound combobox controls. Set
the Format property as before and rename the first control cbo1. Then, open
the VBE and add the following event procedure:
Private Sub Cbo1_LostFocus()
 Debug.Print cbo1.Value
End Sub
Return to the form in Form view and enter the value -1 in the first combobox
and move focus to the second control. The text box component returns the
value Yes, as you expect. Return to the VBE and you'll see the value -1 in
the Immediate window (the result of the event procedure).
Return to the form and enter the value True in the first combobox. The text
box component again correctly interprets the value as Yes, but the control
actually equals the value True, as shown in Figure C. Return to the form and
enter any numeric value, and then check the Immediate window. You'll see
that the control itself always equals the input value and not the Boolean
values -1 or 0. Remember, the bound example accepted values other than -1
and 0, but the underlying Boolean data type forced the control to interpret
the value as either -1 or 0. The unbound control won't force a Boolean
value. The Format property forces the displayed value, but it doesn't
convert the input value.
Figure C: The unbound control equals the input value, not a Boolean value. 

Forcing valid Boolean values in an unbound combobox control
There's an easy solution to the unbound problem. Force the control to equal
either -1 or 0 by changing the Row Source setting to
-1;Yes;0;No
Then, change the Row Source Type property to Value List. Finally, change the
Column Count property to 2 and change the Column Widths property to 0". As a
result, the unbound control continues to display Yes and No in the list and
it equals the appropriate Boolean value. However, the control itself is a
bit limited. The control accepts only the values Yes and No. This limitation
could be good or bad depending on your needs.
After modifying the properties, enter the value Yes in the combo box and
then check the Immediate window. This time the value in the Immediate window
is -1; enter No and the value is 0.
Formatting choices besides Yes/No
Both of our examples use the Yes/No format set at the control level. The
True/False or On/Off formats behave similarly, in keeping with the
underlying Boolean values. Just remember that -1 equals Yes, True, and On; 0
equals No, False, and Off. Additional considerations may be needed if your
application serves an international audience, as described in the
accompanying article "Automatically localize Boolean values in control
lists."
(https://www.elementkjournals.com%2fpremier%2fjournal_archive.asp?vwJournalI
D=IMA#20051)
Expanding your control choice options
Although it's probably easiest to use a check box to represent Boolean
values, there can be times when another control is better suited to an entry
form or interface. As we've shown you, a combobox control can be a viable
alternative-with just a bit of coercion.
Gustav Brock is a consultant who has been working with Microsoft Access
since version 1.0. Gustav is a developer at Cactus Data ApS, an independent
software house specializing in accounting and time billing systems and
custom database applications. You can contact Gustav at
gustav at cactus.dk.Back to top Questions? Email the editor. 
If you're having problems viewing this article please email us. 
Please be aware of our copyright policy. C 2005 Element K Journals, a
division of Element K Press LLC ("Element K"). The content published on this
site ("Content") is the property of Element K or its affiliates or third
party licensors and is protected by copyright law in the U.S. and elsewhere.



Using the Triple State property to express a Null value in Boolean controls
by Susan Sales Harkins
Application(s): Microsoft Access 97/2000/2002
Operating System(s): <none>
There are several native Access controls that are typically used to imply
one setting of a dual state. The check box, toggle button, and even the
option button are all usually used to display or offer a choice between two
settings: yes and no, true and false, on and off, and so on. Occasionally,
you'll find that two states aren't enough-sometimes a Null value can be just
as critical as true and false. In this article, we'll show you how to flip
that on/off switch a third way to also accommodate Null values.
Showing three states with one control
Before directly working with any controls, we'll start with a quick rundown
of the key behaviors associated with controls that are used to indicate true
or false states and discuss how to influence the way in which Null values
are handled. Then, we'll examine the default control behavior firsthand
using a form and some unbound controls. Finally, we'll show you how to
accommodate Null values, which is especially important to know when your
controls are bound to a data source.
About Boolean type controls
Access has a robust set of interface controls and knowing all the little
behaviors and special properties is a lot of work. For instance, there are a
few controls, referred to as Boolean controls because they accept or display
only Boolean values-true and false. Internally, Access actually stores the
values -1 and 0, respectively, but the purpose is the same. That purpose is
to act somewhat like a switch between two states, such as on and off, yes
and no, and true and false.
The matter can be confusing once you throw the Null value into the mix
because the control must somehow handle three values. All three of Access'
Boolean controls, the check box, the toggle button, and the option button
(known to some as a radio button), can display two states. Fortunately, all
three controls can also handle a third state if you simply set the Triple
State property to Yes.
The Triple State property
With the Triple State property set to No (the default state), each of the
three controls will interpret a Null value as a False value. Although a Null
value isn't the same as a False value, the arrangement works well, most of
the time. However, when your need to know that Null really means Null, the
default state just won't do. Setting the control's Triple State property to
Yes allows the control to interpret a Null value in addition to the
traditional Boolean values of True and False.
Examining an unbound example
Working with an unbound form is the best way to illustrate how these
controls work. To do so, create a new form and add one of each of the three
Boolean type controls: a check box, a toggle button, and an option button
(don't worry about including the latter within an option group). Name the
controls chk, tgl, and opt, respectively. Then, insert a label control
containing a few blank spaces to the right of each Boolean control. Name the
label controls lblChk, lblTgl, and lblOpt, accordingly. You'll use these
labels to display the numeric value of each control as you cycle through
True and False (and later the Null) states.
Next, open the form's module and add the event procedures in Listing A.
Close the VBE and save the form as frmUnbound. Now, switch to Form view.
When you switch to Form view for the first time, the check box and option
button will be dimmed (or grayed out).
Listing A: Control Click event procedures 
Private Sub chk_Click()
lblChk.Caption = chk.Value
End Sub

Private Sub tgl_Click()
lblTgl.Caption = tgl.Value
End Sub

Private Sub opt_Click()
lblOpt.Caption = opt.Value
End Sub
By default, the controls' Triple State properties are set to No. As such,
the controls display only two states-True and False. Select the check box,
click the toggle button, and select the option button. This selected or
clicked state is shown in the form on the left in Figure A. Now, deselect
the check box, unclick the toggle button, and deselect the option button to
display the second state, as represented in the form on the right.
Figure A: By default, the Boolean type controls display only two states. 

The visual state is a clue to the control's value. As you can see in each
control's corresponding label, a clicked or selected control represents a
True value (-1) and an unclicked or deselected state represents a False
value (0). A third set of clicks cycles the controls to the True state
again.
Working with a bound example
Now, let's see how they handle a Null value. At this point, it's important
to note that the underlying data type must accept at least three values: -1,
0, and Null. To set up the example, close the form and create a new table.
Add three Numeric fields to the table named chk, tgl, and opt. Finally, save
the table as tblBound and close it.
Next, open your form in Design view and save it as frmBound to create a
copy. Then, set the form's Record Source property to tblBound. Set each
control's Control Source property to its corresponding field in tblBound.
Save the form and then switch to Form view. At first, the controls appear
empty because their bound fields contain no values. At this point, cycling
through the controls' states will have the same effect as you saw
earlier-the controls display just the two states of True and False.
Now, set all of the controls to a False state and switch the form to Design
view. Next, change each of the Boolean type controls' Triple State
properties to Yes. Then, replace the previous event procedures with the ones
shown in Listing B. If you don't use the new procedures, the old ones will
return an error when the control equals Null.
Listing B: Code to reflect three states 
Private Sub chk_Click()
  If IsNull(chk) Then
    lblChk.Caption = "Null"
  Else
    lblChk.Caption = chk.Value
  End If
End Sub

Private Sub opt_Click()
  If IsNull(opt) Then
    lblOpt.Caption = "Null"
  Else
    lblOpt.Caption = opt.Value
  End If
End Sub

Private Sub tgl_Click()
  If IsNull(tgl) Then
    lblTgl.Caption = "Null"
  Else
    lblTgl.Caption = tgl.Value
  End If
End Sub
Close the VBE, save the form, and switch to Form view. Click on each control
to cycle past the False state and you'll see that they display the newly
enabled Null state, as shown in Figure B.
Figure B: These controls accept and display a Null value. 

Using VBA to set the TripleState property
Setting a control's Triple State property programmatically is simple, just
identify the control and set the TripleState property to True or False as
follows:
control.TripleState = True | False
where True sets the property to Yes, forcing the control to accept and
display Null values. A False setting, or No, is the default setting, which
displays just the True and False states.
A quick trip to the state of either, or, and Null
The Null value is a mystery to most and many avoid it because they simply
don't know how to handle it. However, knowing it's out and about is the
safest way to deal with Null values. When you need to know the difference
between True, False, and Null, use a Boolean type control and set its Triple
State property to Yes. As a result, the control will accommodate the Null
value and visually alert you to its presence.Back to top Questions? Email
the editor. 
If you're having problems viewing this article please email us. 
Please be aware of our copyright policy. C 2005 Element K Journals, a
division of Element K Press LLC ("Element K"). The content published on this
site ("Content") is the property of Element K or its affiliates or third
party licensors and is protected by copyright law in the U.S. and elsewhere.



IIRC it shows tick if -1 and no tick (ie False) if anything else, or
vice-versa.  I don't think it'd cause this, though like you I haven't
tested.

And Susan's right. If the value is Null it shows a grey tick box but allows
you to tick/untick.