Write Conflict

[Anne]

Hi,
I am developing an adp database (access 2003) with a sql back end
(2000).

One of my form has a list of clients and a client subform.
When a client is selected in the list, it is displayed in the subform
as the first record.
To display the client as 1st record in the subform, I update a field
(displaySel) in the client table using:

CN.Execute "UPDATE tblClient SET DisplaySel=1 WHERE ClientID="&
me.ClientID

In the subform, the recordsource sort the client table on the
DisplaySel field Descending order.

If the user select a client, then update the client subform,
everything works fine. however if the user select a client in the
list, then select another client, I get the Write conflict error.

Any idea how I could remove this message?

Thank you in advance for your help.

Anne

 

[Sylvain Lafontaine]

Updating the database to control the display is quite probably a very bad
idea; for example, what will you do with multiple users? The write conflict
error is probably a side effect of the optimistic locking mecanism used by
Access in association with the update of the backend table. The best way
to know exactly what's going on here would be to use the SQL-Server Profiler
tool in order to see what commands Access is attempting to run on the server
but IMHO, this would be more a lost of time than anything else.

In your case, the best thing to do is probably to remove this update and use
another mecanism to filter the list of clients displayed in the subform;
like changing it's record source.

 

[Anne]

Thanks for your answer,
I know my solution is not ideal, however as far as I have tried you
cannot change the recordsource of a subform in a form. And even so, I
still need to display all records, I just want to have the selected
record displayed first in the subform.
I know what's happening, the record is updated using a connection
execute, so it is updated outside the form, if the user enter data in
the subform after the update is done, I don't have any message, it's
only if he plays with the list, selecting one client after the other.

If you have any idea how to display this record first without stamping
the record, I would really appreciate.

Thanks in advance

Anne

 

[Sylvain Lafontaine]

It's easy to change the re of a subform, just do something like:

« Me!NameOfTheSubformControl.Form.RecordSource = " your select query here
" »

where NameOfTheSubformControl is the name of the subform control. When you
change the RecordSource, Access performs automatically a requery. For
displaying all the records but with one particular in the first place, all
you have to do is to add a Case statement to your Order By clause.

 

[miley]

no you say that to her.com

 

[Anne]

Brilliant!
That's it, i have tried before and it never worked, as I was missing
the .form after the subform name.
I was using: Me!NameOfTheSubformControl.RecordSource

Any reason why we need to add the .form after?

Thank you very much for your help

Anne

 

[Robert Morley]

It's because without it, you're referring to the control that contains the
subform. By adding the .Form, you're referring to the form itself,
contained within that control. Did that make sense? Not sure how to
explain it better.



Rob

 

[Anne]

That makes perfect sense, I have played with it today and realised
that I was confusing the subform object with the form.
Thanks again