Access crash when user update a CheckBox on a form 
Author Message
 Access crash when user update a CheckBox on a form

This isn't really a SQL Server issue -- it's an Access forms issue.
FWIW, you'd be better off requering the form using a WHERE clause
instead of using local filtering. the line of code you are using, "Set
Me.Recordset = Me.Recordset" is requerying the form anyway, so you
might as well do it cleanly and avoid the bugs you seem to be
encountering.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.***.com/

Quote:

>I have a big problem using Access Data Project.
>SW Version: Access XP, SQL Server SP2, Win 2000 Server SP2

>Short description:
>The problem happen when I have a form with a CheckBox linked to a bit column
>of a table/view.
>If the form is locally filtrered, every click on the checkbox make Access
>crash.

>Detailed description, (behavior reproduction):
>* create a table T , in that table create a 'bit' column B, and an identity
>column ID
>* insert some value in table T
>* create a view V that extract a set of data from table T (Select * from T)
>* create an ADP that access the DB containing T and V
>* create a form F based on the view V
>* set to the right value to the property UniqueTable (T) and ResyncCommand
>(Select * from T where ID=?) of the form F
>* on the form F you'll have a CheckBox control associated to the bit column
>B
>* create a button FILT that when clicked execute he following code (impose a
>local filter)
>    Me.Recordset.Filter ="ID>3"     'string that impose a filter
>    Set Me.Recordset =Me.Recordset  'update the screen

>Ok, now if you open the form F, you see all inserted value, and you can
>update the
>checkbox without problem; BUT after you impose the filter clicking the
>button FILT...
>every attempt to update/chancge/click-on a checkbox make Access INSTANTLY
>crash

>My analysis:
>* The problem only exist when I impose a local filter (but I need that
>filter on the client (it's a client-server App))
>* The problem persist even in the column B is an 'int' instead of a 'bit'
>* The problem persist even if on the checkbox control I impose the following
>event:

>        Private Sub checkbox _BeforeUpdate(Cancel As Integer)
>          Cancel = True
>        End Sub

>* The problem persist even if I try to change the checkbox value directly
>from the code
>* The problem persist even if I try to change directly the recordset column
>value from the code
>* The only event executed is on_MouseDown, every other event are not reached
>* Acces crash 'istantly' after the click on the checkbox, the following
>breakpoint will be never reached

>        Private Sub checkbox _BeforeUpdate(Cancel As Integer)
>      *  Cancel = True
>        End Sub

>* During the crash, nothing happen on sql server; no type of comunication
>between access and SQL Server (I checked this using SQL profiler)
>* Strange thing: even if I set checkbox.locked=true, checkbox ISN'T locked
>* I searched the net (msdn, technet, www...) for this problem, but I found
>nothing

>Please, help me!

>Thanks, Max



Sun, 12 Dec 2004 06:16:16 GMT
 Access crash when user update a CheckBox on a form



Quote:
> This isn't really a SQL Server issue -- it's an Access forms issue.
> FWIW, you'd be better off requering the form using a WHERE clause
> instead of using local filtering. the line of code you are using, "Set
> Me.Recordset = Me.Recordset" is requerying the form anyway, so you
> might as well do it cleanly and avoid the bugs you seem to be
> encountering.

The nice things is that   recordset.filter   is an ADO filter, and become
feltered locally, not on the server.
No requery request are made to the server.

I see that in ADP form I can use 3 filter:
Client form filter, Server form filter, Ado recordset filter.
The first 2 requery the server, the last work locally.

Thanks, Max



Sun, 12 Dec 2004 14:47:51 GMT
 Access crash when user update a CheckBox on a form

Quote:

>The nice things is that   recordset.filter   is an ADO filter, and become
>feltered locally, not on the server.
>No requery request are made to the server.

Whenever you reassign the form's data source  you are  making a
request to the server to requery the data based on your filtering
criteria.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446



Sun, 12 Dec 2004 23:05:53 GMT
 Access crash when user update a CheckBox on a form



Quote:

> >The nice things is that   recordset.filter   is an ADO filter, and become
> >feltered locally, not on the server.
> >No requery request are made to the server.

> Whenever you reassign the form's data source  you are  making a
> request to the server to requery the data based on your filtering
> criteria.

Try it yourself using SQL Server Profiler, no request are made to the server
when you set the  recordset.filter  properties.
recordset.filter is a "local" properies of ado recordset, it run locally, it
filter locally, and it's more limited that the standard WHERE on SQL server.

Max



Sun, 12 Dec 2004 23:50:42 GMT
 Access crash when user update a CheckBox on a form
You're right -- my bad. I wasn't reading your code carefully enough.
However, I still stand by my original recommendation to requery the
form with a WHERE clause. The problem with loading a large amount of
data into a form and then filtering as you go along it has two
problems (not counting Access bugs <g>): it places a load on the
network and the server fetching more records than you need, and (2)
the data tends to get stale. The only way this would be efficient is
if the data was static and you loaded it in a read-only recordset.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

Quote:




>> >The nice things is that   recordset.filter   is an ADO filter, and become
>> >feltered locally, not on the server.
>> >No requery request are made to the server.

>> Whenever you reassign the form's data source  you are  making a
>> request to the server to requery the data based on your filtering
>> criteria.

>Try it yourself using SQL Server Profiler, no request are made to the server
>when you set the  recordset.filter  properties.
>recordset.filter is a "local" properies of ado recordset, it run locally, it
>filter locally, and it's more limited that the standard WHERE on SQL server.

>Max



Mon, 13 Dec 2004 06:06:47 GMT
 Access crash when user update a CheckBox on a form


Quote:
> You're right -- my bad. I wasn't reading your code carefully enough.
> However, I still stand by my original recommendation to requery the
> form with a WHERE clause. The problem with loading a large amount of
> data into a form and then filtering as you go along it has two
> problems (not counting Access bugs <g>): it places a load on the
> network and the server fetching more records than you need, and (2)
> the data tends to get stale. The only way this would be efficient is
> if the data was static and you loaded it in a read-only recordset.

Tanks, Max


Mon, 13 Dec 2004 14:52:07 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Access crash when user update a CheckBox on a form

2. Access crash when user update a CheckBox on a form

3. Access crash when user update a CheckBox on a form

4. Access crash when user update a CheckBox on a form!

5. Access 2002 Crash when updating boolean on form

6. Me.Recordset, bound checkbox, Access 2002 crash

7. Form event crashing Access 2002

8. Crashing when 2nd user accesses the database

9. access 3033, database crash, no user administration installed!!

10. Updating data from forms emailed to several users

11. Checkbox on bound form sets value to -1 for True

12. EXAMPLE: DataGrid Checkbox based on modifying output of Data Form Wizard


 
Powered by phpBB® Forum Software