? about checkbox in Access adp & SQL "bit" data type

[EManning]

Using A2K adp and SQL 2000. I've got a SQL table with a field called
"Attended" which is a bit data type. In my adp frontend, I've got this
field displayed on a continuous form (actually a subform) as a checkbox.
Some weirdness happens when checking/unchecking this box....sometimes the
subform will disappear. I have to close the form and reopen it to get the
form to redisplay.

I'm wondering if the "bit" datatype is not the correct datatype to use and
it's conflicting with the checkbox on the form. There are other controls on
the form (all textboxes) and they update OK.

Just curious if anyone else has had this problem and what you did to solve
it.

Thanks.

 

[Sylvain Lafontaine]

They seem to be some problems with the Bit datatype when the option "Allows
NULL" is set, giving you something like a tri-state instead of a bi-state
field. If this is your case, set it off and set the default value to 0 to
make sure.

You can also replace the Bit datatype with a Tiny integer, but then there
are the possibility that you will have 0/-1 instead of 0/1 for your
values; -1 being the equivalent for True on many systems. Not a big deal
however.

S. L.

 

[Brian M. Sockey]

There are also some bugs related to using checkboxes in ADP forms. Most of
the issues occur when code behind the form uses the form's recordset
property.

Last time I checked, these problems were fixed in version 2003. Otherwise,
the main workarounds are to use a different control or to programmatically
manipulate the checkbox control.

Brian M. Sockey
www.farsightsolutions.com
www.televantagenorthwest.com

 

[EManning]

Not sure what you mean by "...programmatically manipulate the checkbox
control."? Could you elaborate please?

I'd really like to keep the checkbox if at all possible. It would be the
simplest for the user to use. However, I'd thought of using a combo box
where the user would have to choose "yes" or "no".

Thanks...

 

[Brian M. Sockey]

What I meant by programmatically manipulating the checkbox control was to
leave the checkbox control unbound and then change its value in code to
synchronize with the form's underlying record as records change.

However, this should only be needed if you have some other code behind the
form that is hitting one of the bugs I referenced. If the form still
exhibits the behavior without running any code then you've got a different
problem, perhaps what Sylvain mentioned previously.

--Brian