sel is boolean. Yes, users select the records by flagging sel to true. This is done with a button that launches VBA code that executes a query (using docmd.RunSQL).
If so, multiple users may be 'selecting' the same records which is causing the issue.
Due to the workflow it's is very unlikely two users would be selecting the same record at the same time. I would expect a issue that may raise once in a blue moon, not multiple times in a day.
If selection does not happened at the same time then should not raise any issues, right?
I will clarify the workflow as it may be helpful.
A) An order is inserted > a record is created in tblOrders. sel default is false
B) An order is selected > tblOrders.sel is set to true using a docmd.RunSQL
C) All selected orders are deselected > tblOrders.sel=false using
Code:
docmd.RunSQL("UPDATE tblOrders SET tblOrders.Sel = False WHERE tblOrders.Sel = True")
both B and C sometimes fail because of concurrency violation. But the record is editable if the table is opened.
Now, there are several solutions to avoid the problem. But I would like to know the reason of the difference in behaviour. Does the locking system apply ro queries and not direct editing sometimes? under which conditions? Seems offly odd to me.