How can I code a checkbox to use "Y" and "N"?

[Maury Markowitz]

I have a database that holds a boolean-like value as a "1" or an
"0" (or null, which is "0") in an int field. When I bind this to a
checkbox in the display, changing it to the1 state gives me an error
that the value is invalid. It appears that checkboxes can only use -1
and 0 (and null). Oddly, the checkbox displays the 1's and 0's
properly - 1's come out as "on" and 0's as "off".

Is there a way to do this? I tried hooking event handlers into Before
and AfterUpdate, onClick and even MouseDown. Most of these fired after
the error was popped up, so they didn't help. MouseDown did work, but
when I changed the value in the code (Me!theCheck = -1) it didn't seem
to actually change anything.

Maury

 

[Klatuu]

Why not just use a Boolean field? Displaying the value is not an issue, but
updating it will be.

For example if your control source were
[YesOrNo]
you could then use
=[YesOrNo] * -1
Which would change the 1 to -1 and not change a 0, so it would display
correctly, but it would then not be an updatable control.

Another solution (without changing the data type) would be to use an option
group control. You can use check boxes in option groups, but it would take
two instead of 1.

 

[Maury Markowitz]

Why not just use a Boolean field?  Displaying the value is not an issue, but
updating it will be.

That's the problem, this is a user control that means "is active" and
is used to interactively turn lines on or off in other reports.

Another solution (without changing the data type) would be to use an option
group control. You can use check boxes in option groups, but it would take
two instead of 1.

So you mean to use two check boxes, one as a "1" and another as a "0"?
Hmmmm. Doable, but ugly.

Maury

 

[Klatuu]

Yes, doable but ugly.

 

[Dale_Fye via AccessMonster.com]

Or you could make the checkbox unbound (as long as the form is not a
continuous form). Then, in the forms current event, you would do something
like:

Private Sub Form_Current

me.chk_Whatever = NZ([FieldName], 0) * -1

End Sub

And then, in the checkboxes AfterUpdate event, you would set the value of the
field, something like:

Private Sub chk_Whatever_AfterUpdate

me.[FieldName] = me.chk_Whatever * -1

End Sub

If it is a continuous form, making a change in an unbound control will set
the value of that control in all of the records (although it would not
actually change the value of the associated field except on the record which
has the focus).

HTH
Dale