Write Conflict - 3197

[mcbass1]

I have a form linked to a table in a FE/BE database with multiple users. The problem I'm having is that multiple users will be accessing the same record at the same time, and the write conflict error shows up.

I have changed the form to update the controls from commands entered in an unbound field by the user, locking all of the controls. An after update event refreshes the form, updates the correct field, and saves the record. This is working well, and multiple users can update the same record at the same time. There will be times when two users try and update the same field of the same record at the exact same time, and this is where the write conflict shows itself. If two users update the field at the exact same time, I'd like to catch the error, pause a fraction of a second, refresh the form, and save the record.

Any suggestions on suppressing this error would be very much appreciated.

Thanks in advance,
Mike

 

[Guus2005]

You could try a different approach: let users view the record and lock it when they want to change it. In order to do so, write the recordnumber in the LOCKS table (which you have to create) and check the LOCKS table when you want to edit a record. This way only one user can edit the record.

HTH

 

[mcbass1]

Thanks Guus, I actually have that in place right now. I added two fields to the table, one check box called "locked" and one text box for the operator id. If the box is checked when a user tries to pull up a record, a msgbox tells them who it's locked by and pulls up the record read only. If it isn't checked, it checks the box and puts the user's operator id in the text field.

For the most part, this is working, but I do have days where the check box stays checked even when the person is no longer looking at the record, which prevents another user from updating the record.

I was hoping to get rid of the locks and just have everything work from the unbound command line text box. The record would be dirty for so much less time.

 

[Guus2005]

Your approach works fine but consumes a lot of space. If you create a LOCKS table it holds only the recordid's that where locked. Searching would be a lot faster.

Use the form_unload event to do last minute stuff. If you have a Menu form, put the following statement in that event:

Private sub frmMenu_Unload()
    dim strSql as string
  
    strSql = "Delete from Locks where UserID = " & getUserID
    currentdb.execute strsql
 
end sub

(or something like that.)

Even logging out or closing Access triggers the unload event.

Only a crash leaves traces.

Enjoy!

 

[mcbass1]

Thanks guus, I'll give it a shot.