Applying "After Update" code to datasheet view?

[stickhog]

I have an issue with my split database in a multi-user environment that I was able to solve if users use forms for entry. But that doesn't work if they enter data in the datasheet view. Here's the problem:

I have a multi-value combo box that once edited in any record, locks out all users from also selecting values in that field in any other record. This does not appear to be due to page-level locking. If I use pessimistic locking which with the various memo fields in my DB will force page-level locking, I get records locked about +/-10 records away from the one being edited. I've changed to optimistic locking in the forms which removed that problem. But even if I've edited the multi-value combo box in record #1, I can't edit that field in record #1300, until the change to record #1 is saved. None of the other fields exhibit this behavior with optimistic locking.

I solved this in Form view thanks to ItsMe's suggestion by forcing an immediate save once a user edits the multi-value field, which frees up the lock immediately: https://www.accessforums.net/access/...tml#post244408



But that doesn't help if a user decides to edit the multi-value field in Datasheet view. It'll stay locked out until the record is saved. Is there a way to either apply the Me.Dirty script to datasheet view or prevent somebody from editing this field in the datasheet? It's important that users can still view, sort, and filter in the datasheet view. I just need to prevent them from locking out the entire multi-value field for all users in all records. I'm an Access newbie so please use small words.

Thanks.

Access 2010 14.0.7128.5000 (32-bit)
Windows 7

[June7]

Professional developers abhor MVFs and advise not to use them. The behavior you describe is a new one to me and another reason not to use MVF.

Why use datasheet view? A continuous form can be arranged to look like datasheet.

[ssanfu]

But even if I've edited the multi-value combo box in record #1, I can't edit that field in record #1300, until the change to record #1 is saved. None of the other fields exhibit this behavior with optimistic locking.

From http://office.microsoft.com/en-us/ac...001233722.aspx

The idea behind multivalued fields is to make it easy to support those instances where you want to select and store more than one choice, without having to create a more advanced database design. Multivalued fields are also important for integration with Windows SharePoint Services because SharePoint lists also support multivalued fields.

You might wonder why Office Access 2007 allows you to store more than one value in a field, when most relational database management systems forbid this. The answer is that the database engine in Office Access 2007 doesn't actually store the values in a single field. Even though what you see and work with appears to be a single field, the values are actually stored independently and managed in hidden, system tables. The Access database engine handles this for you, automatically separating the data and bringing it back together again to surface the values in one field.
Technically speaking, an Access multivalued field models a many-to-many relationship. For instance, consider an events table where you assign responsibility for each event to one or more of your employees. Suppose you create a multivalued field named "AssignedTo" for assigning events to employees. The relationship between events and employees is many-to-many. That is, you can have many employees assigned to any one event, and you can also have any one employee assigned to many events.

Access locks the field until the record is saved because of the junction (M-M) relationship.

Also see:
Multivalued datatypes considered harmful
http://www.theregister.co.uk/2006/07...access/?page=1