microsoft access vba coding in form to determine if a user has a record open for editing
Posted on 2010-11-10
Right now I have a form with a custom built navigation button system that a user uses to navigate thru records and make changes to the record where needed. Each record is identified uniquely by an AutoNumber called "Service Report No". As the user navigates thru the records, all the controls are disabled. Only if the user enters the Service Report No in a textbox called "txtSearch" that matches the Service Report No., then all the controls on the form are enabled for the user to edit the data. What I want to do is, if VBA possible, to check to see if the Service Report No record is already open by another user after another user updates the txtSearch value to edit the record. If the record is open by another user, then the other user(s) is/are prompted with a message box stating that the record cannot be edited and the user(s) acknowledge the message and then docmd go to the first record. Under Tools and Options and the Advanced Tab for my database, I have the "Default record locking" set to "Edited record" and the database opens using record level locking. However, at times, when the user is warned about a write conflict, the user selects "Save Record" instead of "Discard changes" and ends up overwriting the original user's edited values for that record. Somehow I would like to capture the cursor position within the underlying table (called Service Database that the form is tied to) such that if another user tried to get to that same cursor position, that user is prohibited from going to that record when updating the txtSearch value. How can this be accomplished using VBA?