You may not have run into this little gem of a bug yet, so if you haven't, thank your lucky stars. Be sure to remember this so if you DO run into it, you don't have one of those I-want-to-tear-out-my-hair days.
The problem is that an Access ADP crashes in certain circumstances (which no one exactly knows) when trying to update a checkbox (or presumably other control) bound to a bit column in SQL Server. The following helpful message appears:
Microsoft is sorry all right. I've never seen such a sorry excuse for a major bug! Anyway, I'll explain the workaround I found--after probably a month of dancing around the problem on form after form.
The crash always happens 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:
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.
I hope this helps someone avoid supreme frustration... and I again call down a pox on Microsoft for this bug.
Supposedly, Access 2002 is the latest version to have this bug, but I saw some reports of people managing to get it in Access 2003 as well.
If I sound grumpy it's because I AM grumpy. Sorry about that! It would be nice to know if this ever actually helps anyone, sparing them from suffering the same grumpiness!
The problem is that an Access ADP crashes in certain circumstances (which no one exactly knows) when trying to update a checkbox (or presumably other control) bound to a bit column in SQL Server. The following helpful message appears:
Stupid Error Message said:
Microsoft is sorry all right. I've never seen such a sorry excuse for a major bug! Anyway, I'll explain the workaround I found--after probably a month of dancing around the problem on form after form.
The crash always happens 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 SubThis 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.
I hope this helps someone avoid supreme frustration... and I again call down a pox on Microsoft for this bug.
Supposedly, Access 2002 is the latest version to have this bug, but I saw some reports of people managing to get it in Access 2003 as well.
If I sound grumpy it's because I AM grumpy. Sorry about that! It would be nice to know if this ever actually helps anyone, sparing them from suffering the same grumpiness!