write conflict

[Nora M.]

I have a bound form and am calling a stored procedure
which will set or clear the inactive date field based on
the active flag field in the table. I am getting a write
conflict warning and am wondering if there is a way to
default the choice save record or suppress the warning. I
tried the DoCmd.SetWarning False before calling the
procedure but that did not work. Any ideas/suggestions
would be appreciated.

 

[Karen Vaudreuil]

Maybe you could try putting Echo Off; empty the form's recordsource with
<your select statement> WHERE 1=0, so it returns no records; execute the sp;
put the recordsource back and Echo to ON. This way, there will be no
records open while you make the update.

Karen

 

[Sylvain Lafontaine]

Usually, stored procedure are for retrieving or storing/updating data when
you're done with your local values. If you can modifie your date field with
VBA code then you will get rid of your conflict warning. This is a side
effect of the optimistic locking scheme used by Access.

This warning come when Access try to save the current record in the form and
discover that the underlying record on the database have been modified by
someone else then the form itselft (in this case, your stored procedure).

There are of course many solutions besides using VBA code or unbound forms.
One possibly is first saving the record, then make the appropriate
modification via your stored procedure and finally making a refresh or a
requery. A refresh is probably better if you want to stay a the same place,
otherwise you need to use bookmark or a find with the proper key.

S. L.

 

[Nora M.]

Thanks for the suggestion. I had originally tried that
but when using VBA to toggle the date to Null (as for the
case that they accidently set active to false), then
Access sets a Null date to 12/30/1899. I will try the
refresh.

 

[Sylvain Lafontaine]

I'm not sure about your code but usually Access will not replace a Null
value with a 0 value. Make sure that you have allowed Null value for the
date field on the SQL-Server table and that you have not something in your
code or your select statement that will replace a Null value with 0. If
Access see the value of 0 for a date, it will replace it with 12/30/1899,
which is the value of CDate (0).

If you transfert a value of 0 as a date to the SQL-Server and you use a
variant instead of enclosing it into a character string, it will add one day
to it so that 12/30/1899 will become 12/31/1899 on the SQL-Server. So, you
must take care when evaluating such expression on the SQL-Server.

S. L.