Checkbox crash

  • Thread starter Thread starter Dave
  • Start date Start date
D

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?
 
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.
 
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
 
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.
Click to expand...

Thanks in advance,

Niels Dekker
www.xs4all.nl/~nd/dekkerware
 
Possible workaround I discovered

Regarding checkboxes or other controls bound to a SQL Server bit column in an Access ADP causing an immediate crash of Access when clicking on them:

In my testing, the crash always happened after the form's BeforeUpdate event, but before the control's BeforeUpdate event. If two Form_BeforeUpdate events happen, it will occur after either of them (such as if you cancel the first one).

(Access for some reason sometimes (always?) fires two Form_BeforeUpdate events. And it also (sometimes?) does some weird things with autonumber primary key fields in the meantime. A coworker tells me that during the first one, records have sequential primary key values starting with 1, and during the second one, they again have their correct server-side values.)

Surprisingly, cancelling just these events seems to allow the application to not only avoid crashing but also to work normally as a user would expect. Since the control's GotFocus event fires before the form's BeforeUpdate event, we can use it to tell us when to cancel those updates:

Code: 
Private gfPreventUpdate as Boolean

Private Sub Checkbox1_GotFocus
   gfPreventUpdate = True
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Cancel = gfPreventUpdate
End Sub

Private Sub Checkbox1_BeforeUpdate(Cancel As Integer)
   gfPreventUpdate = False
End Sub

Private Sub Checkbox1_LostFocus() ' in case user tabs to and away from control without updating it
   gfPreventUpdate = False
End Sub

This surprisingly small amount of code seems to do the trick. If you are already doing something in the control's Click or AfterUpdate events, you can optionally set the flag back to false there also instead of in the BeforeUpdate event.

There may be other events where the flag needs to be set back to false which I haven't discovered yet.

One additional thing I found is that if I made the record dirty by editing another field first, this could also prevent the crash from occurring by itself.

ESquared
 
Back
Top