Write Conflict Error - sql table/Access form

[chrisvt]

Apologies in advance for what is probably a simple question. I'm getting up to speed with our organization's database programming after the departure of our database administrator.

We have an existing form in Access that we have been using to populate an existing sql table. I need to add a new Yes/No field to the table and a new checkbox to the form in order to populate the field. Adding the field and checkbox was straightforward and I made sure that the default value of the field was 0.

Unfortunately, whenever I select a record, check the new box and try to close the form, I get the "Write Conflict, This record has been changed by another user...." error.

I've searched the forums and there appears to be some AfterUpdate code that I can use, but I'm not really sure where this should be added. Using the code, I've created Event Procedures for both the checkbox and for the form itself, but neither seem to eliminate the error.

I have a feeling that I'm missing an obvious step.....Any ideas?

Thanks.

edited to add: the form actually contains multiple subforms, which probably explains WHY this is happening.

 

[RuralGuy]

The SubForms are a clue. Make sure *all* of your forms are bound to queries rather than directly to tables.

 

[chrisvt]

Thanks RG. I just checked the subforms and some are linked directly to tables. Should I setup Select Queries for the Record Source for those subforms?

 

[RuralGuy]

Should I setup Select Queries for the Record Source for those subforms?

You bet. Probably the source of your problem.

 

[chrisvt]

Ok, so I went in and there were 7 subforms. Four were already setup with Select Queries and the other three were linked directly to tables. For the three that were linked to the tables, I invoked the Query builder and setup a Select Query. Because each subform was sourcing data from only one table, I choose the * and didn't add each individual field.

I saved the select query and then saved all the forms and subforms but I'm still getting the Write Conflict error. Is now the time to add some AfterUpdate code?

Sorry for the basic questions, I'm assumed these duties and am learning as I go.

 

[RuralGuy]

It is hard to say what is going on without knowing more about your db. How many forms/subforms that are open have your table in the RecordSource query?

 

[Kiwiman]

Howzit

I had a similar problem months ago. By adding a new field, with a data type of timestamp, all our write conflicts went away. Don't worry about updating this field SQL does it for you.

This link will give you further information - not the link I was looking for, but could get you on your way.

http://www.sqlusa.com/articles2005/rowversion/

 

[chrisvt]

It is hard to say what is going on without knowing more about your db. How many forms/subforms that are open have your table in the RecordSource query?

Two of the seven. I also have another, separate form that also links to the same table and was giving me the same Write Conflict error. I was able to eliminate all the errors by going into sql and deleting the field that I had added yesterday but of course now I'm back to square one.

Thanks Kiwiman-I'll check out that link.

 

[Kiwiman]

Howzit

You may already have a field that has this data type - so check first. I think SQL server can only have the one field in a table with this data type.

 

[Banana]

Just to point out one more possibility-

If you have anything in your code behind for any of those form/subforms that for instance execute SQL upon the same table your forms are bound to, it can potentially create write conflict because SQL Server will perceive your code executing SQL as a separate connection from the connection that your form uses to get the same data and thus lock you out. It has no way of telling that two connections are actually from the same users.

In such cases, you should ensure that all of your code are funneled via the form (e.g. edit its controls or recordset) instead of executing a separate SQL statement.

Hope that helps.