Checkbox crash

[Dave]

I'm having a problem when I click on a checkbox bound to
a 'bit' field in SQL server. I set the recordsource in
the onload event to a recordset. I can change the values
in other fields, (even a text field linked to the 'bit'
field where I type either 'True' or 'False'), but when I
click on a checkbox field bound to the 'bit' field,
Access will crash.

To reproduce the error, create a new ADP file and connect
it to the 'NorthwindCS' database. Create a form based on
the 'Product' table including the 'Discontinued' field
which is defined as type 'bit'. In the onload event enter
the following code:

Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM Products",
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set Me.Recordset = rs
Set rs = Nothing
End Sub

When you click on the CheckBox bound to
the 'Discontinued' field the system crashes, but not when
you have a text field where you can type True or False.

The same form will work OK if you set the record source
this way:
Me.RecordSource = "SELECT * FROM Products"

Has anyone else encountered this problem? Is this a known
issue? Is there another way to set the recordsource to a
recordset?

 

[Sylvain Lafontaine]

This is an old problem: in Access, True and False are defined as -1 and 0
while a bit field in SQL-Server can only hold the values 1 and 0. On
SQL-Server, True is also defined as 1 if I remember correctly.

I think that this error has been corrected in Access 2002/2003 but I'm not
sure. Bit fields can also be the cause of some other problems beetween
Access and SQL-Server; so I usually use tinyint instead.

S. L.

 

[Brian M. Sockey]

This very annoying bug was fixed in Access 2003. Besides the workarounds
you already figured out, the only other option is to make the checkbox
unbound and programmatically synchronize it's value with the underlying
field data.

Brian M. Sockey
www.farsightsolutions.com

 

[Niels Dekker (no reply address)]

Has this bug been fixed by Office XP Service Pack 3 as well?
(Currently we use Access 2002 SP2, including the bug.)

This very annoying bug was fixed in Access 2003. Besides the workarounds
you already figured out, the only other option is to make the checkbox
unbound and programmatically synchronize it's value with the underlying
field data.

Thanks in advance,

Niels Dekker
www.xs4all.nl/~nd/dekkerware