Question 2 users in same record, with read-only (1 Viewer)

[jpl0219]

Ok, so I am not very knowledgeable about access, and its intricacies, but we have an issue that is very frustrating. I wasn't 100% sure how to title it.

We have a database that is set up so that if a user is in a record, the record is supposed to open a read only copy for the 2nd person that clicks into a record.

This week, we had a situation where 2 people were able to access a record at the exact same time, and have it open as editable. So both users were able to change information in the same record and save it. This broke the notes portion of the database that is a large field that allows multiple notes saved in it. Our access guy was able to fix the broken field, and we recovered the data.

However, the issue still persists that if 2 people click into the same record within a few seconds of each other, they are both able to edit the record.

Is there anything that can be done in this situation? Or are we to hope that 2 people don't keep clicking into the same record?

Any help is welcomed.

 

[Uncle Gizmo]

Are you using the recommended front-end back-end arrangement for multiple users of your MS Access Database?

 

[jpl0219]

Are you using the recommended front-end back-end arrangement for multiple users of your MS Access Database?

Yes, we are.

 

[Uncle Gizmo]

It would be helpful to see the VBA code that operates this record locking.

 

[jpl0219]

It would be helpful to see the VBA code that operates this record locking.

I wouldn't even know where to find it. I haven't set it up, and I don't know much about coding.

I'm mainly wondering if this is a coding issue, or something that access just does that can't be fixed.

From your response it sounds like it might be a coding issue?

 

[AliyuKatsina]

If the records are accessed via a form, then to prevent two users editting the same record at the same time you do the following:

1. create a Yes/No field in the underlying table (Let's call it 'Select')
2. On the form where the records are displayed and edited, create a command button (Let's call it 'Select' too). Create an OnClick event for this button to check (Make True) The 'Select' field on the table.
3. Create an Oncurrent event on the form tat check the value of the 'Select' field on the table. If 'Select' is true it means another user has marked that particular record for editing, so the control holding the record can be disabled.
4. Create another command button (Let's call it 'Edit'). Create an OnClick event for this button to update the table and uncheck (make false) the 'Select' field.

Once implemented then two users cannot edit the same record.

 

[static]

https://support.office.com/en-gb/article/RecordLocks-Property-6ca29bbb-8824-4671-8087-97fe0568019a

 

[gemma-the-husky]

Ok, so I am not very knowledgeable about access, and its intricacies, but we have an issue that is very frustrating. I wasn't 100% sure how to title it.

We have a database that is set up so that if a user is in a record, the record is supposed to open a read only copy for the 2nd person that clicks into a record.


This week, we had a situation where 2 people were able to access a record at the exact same time, and have it open as editable. So both users were able to change information in the same record and save it. This broke the notes portion of the database that is a large field that allows multiple notes saved in it. Our access guy was able to fix the broken field, and we recovered the data.

However, the issue still persists that if 2 people click into the same record within a few seconds of each other, they are both able to edit the record.

Is there anything that can be done in this situation? Or are we to hope that 2 people don't keep clicking into the same record?

Any help is welcomed.

this sounds like a design issue, a training issue, or a general misunderstanding to me, and it shouldn't happen in the way you describe without users being made aware.

Bits in Bold
first bit in bold - having multiple users using the same record simultaneously should not be an issue anyway, because that's what access is supposed to do - allow multiple users.

second bit in bold. So both users would not be able to change information and save it, without being notified there was an issue. If both did change the record, then one user would get a message saying the record had been changed, and asking them to take action.


I am generally reluctant to use lock flags to prevent users reading records, as they aren't really necessary.

What if a user locks a record, then goes to lunch, or leaves his screen? What if a programming error fails to remove a software lock? If you do decide you want to add locks, then it isn't a simple process, and your app needs changing to accommodate the locks. eg You need to programme your app diferently in order to recover when users' locks interfere with one other processes.

 

[Galaxiom]

Totally agree with Dave. Access manages clashes automatically if used properly.

My guess is that the developer has used an ill-considered design with unbound forms and saves records with SQL commands. This technique is sometimes used when a Save button is mandated.

 

[Galaxiom]

the notes portion of the database that is a large field that allows multiple notes saved in it.

Also a poor design choice.

These notes would be better as separate records in a related table.