Record Locking using a secondary form to edit long text field

[Pox]

I have a small sales office and have written an Access 2016 muliti-user database for the staff to view client data and track sales and contacts. We often work on the same client simultaneously and this often causes locking conflicts. I am struggling to work this out. I am not a professional programmer but have a pretty good grasp of VBA and like it. Our main form is bound to our main database. It has lots of controls and tabs to view client information and run reports. It is bound so it allows users to edit data. Users can click to open a second form that allows editing of our ClientNotes field, which is a long text field. Most of the conflicts occur when using this second form to edit ClientNotes. I have tried to use Recordlocks to lock or unlock these forms, and have tried it different ways but nothing has worked very well. At this time the ClientNotes field is a part of the main Client database, but I could put it in a related database I guess. I seek advice on how to set this up. Thanks for any help.

[pbaldy]

Try this before opening the second form:

If Me.Dirty Then Me.Dirty = False

That will save the record on the main form so the second form can edit it. That won't help with multiple users trying to edit the same record.

[Pox]

OK
That sounds good
How do I set the forms' Recordlocks?

[Micron]

At this time the ClientNotes field is a part of the main Client database

Hopefully you don't plan on one database per table?
Because of that statement, I'm not understanding if the conflict is because the notes and the rest of the fields are coming from the same table or query, or some other reason. I suspect they are, and you are using a sort of modal form to edit notes. This might explain why you had no success with record locks. If you have no locks, you will get warnings from Access that are sure to confuse the users. If the notes are part of the same record source, then would you not want Access to prevent editing of the notes portion of the record? I would think so, one reason to follow*.

Possibly separate notes into their own table and put them in a subform of the main form, assuming you want to see the notes while looking at the main record? If you don't need to see the notes related to the main record, probably even better. You should be able to edit the notes while another user has the same record open if the notes are not part of the source that populates the other fields.

* However, the notes would not be refreshed for a concurrent user viewing the main record when those notes are changed by someone else unless something about the process could ensure their notes view is requeried before providing the means to edit them. Of course, if I'm editing the notes and you attempt to do so at the same time, you're back to square one.