Using checkboxes on non-booleans?

[Guest]

I have a data field in my database that toggles between two values, null and
27. Yes, 27. It's a long story.

I'd like to attach this to a checkbox. When I do, the checkbox properly
displays a check when the value is 27. I assume it really means "not null".

The problem is that when I toggle the control it seems to ignore the macro
I've written, overriding the value I push in (null or 27) and changing it to
0 or -1. I've tried doing this in both the before and afterUpdate, but no
luck.

IIRC there's a way to stop the rest of the event from firing so that my
change will stick and the checkbox's own event handler won't continue. Do you
think this will do the trick? If so, how do I do this?

Maury

 

[Guest]

Maury:

Insert the checkbox as Unbound. Then use the After Update event to modify
your data field.

If the form is in Continuous mode, then you will need to bind the checkbox.
In that case you may want to look at simply adding a new column, for the
checkbox, and again use the After Update event to modify your data field.

The checkbox really only operates as Null, 0, or -1. So you should think of
it in those terms, when working with it.

HTH.

Sharkbyte

 

[Guest]

Insert the checkbox as Unbound. Then use the After Update event to modify
your data field.

Well I thought of that too, but then I have to do something in the OnOpen
for every one of the forms that uses it. There's a lot.

Maury

 

[Guest]

Maury:

Then why not add a boolean column, to the table. Run an update query and
set 27's = -1 and Nulls = 0 (I believe it cuts down user confusion if you
can keep the checkbox to 2 states).

Once you've done that, add and bind the checkbox, to your form. Then you
may be able to build a macro to do what you want. (I work more in code, and
am not all that fluent with the macro system.)

Sharkbyte

 

[Dirk Goldgar]

In

I have a data field in my database that toggles between two values,
null and
27. Yes, 27. It's a long story.

I'd like to attach this to a checkbox. When I do, the checkbox
properly displays a check when the value is 27. I assume it really
means "not null".

The problem is that when I toggle the control it seems to ignore the
macro I've written, overriding the value I push in (null or 27) and
changing it to 0 or -1. I've tried doing this in both the before and
afterUpdate, but no luck.

IIRC there's a way to stop the rest of the event from firing so that
my change will stick and the checkbox's own event handler won't
continue. Do you think this will do the trick? If so, how do I do
this?

It's a strange and probably wrong-headed thing to do <g>, but you can
probably do it using the control's AfterUpdate event, like this:

Private Sub chkWeirdIdea_AfterUpdate()

If Me.chkWeirdIdea = 0 Then
Me.chkWeirdIdea = Null
ElseIf Me.chkWeirdIdea = -1 Then
Me.chkWeirdIdea = 27
End If

End Sub

That seems to work for me in a test form, though you said you tried the
AfterUpdate event yourself.

 

[Guest]

It's a strange and probably wrong-headed thing to do <g>

Both, most likely. The right interface is a combobox, but in this odd case
that would be more confusing than a checkbox.

That seems to work for me in a test form, though you said you tried the
AfterUpdate event yourself.

Yeah, still no luck. I think maybe something is coming along later when the
form is being torn down and reading it out as -1 regardless of the underlying
value.

Ohhhh, wait, is it maybe because the form is dirty after I set it to 27, and
when I click a button it re-reads the value and sets it to -1?

Maury

 

[Allen Browne]

Maury, the check box is only capable of handling the values 0, -1, and Null.

If you need to store 27, then a checkbox is *not* the right control to use.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Dirk Goldgar]

In

Both, most likely. The right interface is a combobox, but in this odd
case that would be more confusing than a checkbox.


Yeah, still no luck. I think maybe something is coming along later
when the form is being torn down and reading it out as -1 regardless
of the underlying value.

Ohhhh, wait, is it maybe because the form is dirty after I set it to
27, and when I click a button it re-reads the value and sets it to -1?

When you click a button? I suppose that would depend on what that
button does.

As I said, it works fine for me, and stores the value of 27 or Null in
the record accordingly. However, I'm testing in an .mdb, and as I
recall you work mainly with .adp files. I don't know if there may be
something different in an .adp.

The other, and probably obvious, point is to make sure that the field is
defined as a number field in the table -- long or short integer, single,
double, byte, or decimal -- not as a boolean or bit field. If you
define it as a boolean field, any non-zero value is always going to be
converted to -1. In an ADP, I guess that would be a bit field and would
actually hold a 1 or a 0, but I think Access will still display it as
a -1 or 0 (though I've never worked with ADPs much, so I could be
wrong).