Solved Display user who has a record open on continuous form (1 Viewer)

LGDGlen

Member
Local time
Today, 07:25
Joined
Jun 29, 2021
Messages
109
I am migrating users from a very large shared multi-user-access spreadsheet to an access database and they have started using the application i have created for beta testing.

One of the issues/requests is to have functionality that occurs with shared spreadsheets where each person accessing it is shown when they are editing a cell. What the users have requested is that if someone has opened a record to edit/update etc that their name be shown on the continuous form that shows all records so they know there is no need to go in and update.

the process is that there is a central email address that all the users see for updates and from the update emails they go to specific record on the continuous form, double click on it to open the form and make the updates. So what the users would like to see is if someone has opened the record that users name is displayed next to the record.

My thoughts on this would be as follows:

From the Continuous Form:
  • On Double Click event check you are opening a valid record (as in it has an ID and is not a new record)
  • If this is a valid record
    • find the record in the recordset
    • if the "User Editing Record" field is empty
      • update field with current user
      • open form with record
    • if the "User Editing Record" is not empty
      • message box popup saying record is being updated by "User"
  • if not valid do nothing
From the opened form:
  • When user closes form find record in the recordset
  • Set "User Editing Record" field to blank
my question is really, is this set of steps the best way of doing things or is there a better way. i don't want to lock "all records" if one is being edited and i'm not sure about how "edited records" setting for default record locking works and if thats the way i should be doing things - as in using the functionality built into access instead of writing VBA for it.

Happy to provide more information, pictures, table diagrams etc if necessary but as a mock up:
1631703362733.png

The last column is just a text box added to show where the info would be displayed as to who has the record open. When you double click on any field of a line it opens a form displaying the record the user double clicked on

thanks in advance
 

June7

AWF VIP
Local time
Yesterday, 22:25
Joined
Mar 9, 2014
Messages
3,670
What do you mean by 'shared spreadsheets' and how does this factor into editing table in Access?

AFAIK, the procedure you outlined would be the only way to accomplish this requirement. Be sure to null the field when user leaves record, probably in form BeforeUpdate event.
 

LGDGlen

Member
Local time
Today, 07:25
Joined
Jun 29, 2021
Messages
109
so the shared spreadsheet is what the company runs it business from at the moment. what i am doing is migrating from that to an access database

when you have multiple people editing a spreadsheet at the same time the user initials are displayed as a overlay with a colourful highlight of the field, the users have gotten used to seeing this to know who is doing what to the data

they would like - if possible - to have some sort of replication of this in the database so they know when a record is opened by a user
 

June7

AWF VIP
Local time
Yesterday, 22:25
Joined
Mar 9, 2014
Messages
3,670
I've never used SharePoint/shared spreadsheets so appreciate the explanation.

Your idea might be helpful but likely not foolproof. For each user that has the form open, the record update possibly won't show immediately. Their form will have to refresh. This occurs automatically according to frequency set by database.

If you are not already aware, multi-user db should be split with each user running their own copy of frontend installed on their computer.
 

Minty

AWF VIP
Local time
Today, 07:25
Joined
Jul 26, 2013
Messages
8,823
I suspect the need for this will disappear once they realise the database will chuck up a message if two people try and edit the same record at the same time, but silently just work otherwise.
 

LGDGlen

Member
Local time
Today, 07:25
Joined
Jun 29, 2021
Messages
109
@June7 the database has been split into FE and BE with each user having their own FE. I am currently looking at implementing a timer/refresh process anyway to cope with any additions/deletions
 

LGDGlen

Member
Local time
Today, 07:25
Joined
Jun 29, 2021
Messages
109
@Minty this is i am trying to understand because i have set the database to lock when editing but have tested by having the same record/form open and making changes on both computers but nothing happened.

the other issue is the opened form has sub forms where the user can add/amend data which doesn't make any changes to the main form therefore there is potential that 2 users will be looking a the same data and making changes to separate sub forms

unless that is something that is also coped with when setting locking to editing
 

Minty

AWF VIP
Local time
Today, 07:25
Joined
Jul 26, 2013
Messages
8,823
Generally speaking, Access is really quite good at keeping track of such things.
Looking at a record is different to editing it. Only once an edit is commenced will access start to check for other conflicting updates.
So 10 people could have the same records open in a subform, but until anyone actually edits something no one would notice.

Your Subform data is exactly that - it is data in another table.
If someone edits it then you try to (because your form hasn't refreshed or you haven't moved to another record) it will still tell you.
 

LGDGlen

Member
Local time
Today, 07:25
Joined
Jun 29, 2021
Messages
109
@arnelgp i had looked at that but the code that it refers to i couldn't find the download. i think i'll set the locking to edit and see what happens and if it becomes an issue maybe add the process i outlined in that a user name is added to the record
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:25
Joined
Feb 28, 2001
Messages
20,859
I think the problem is that Access CAN do things fast enough to pop up indicators that someone is editing a record - but it is a resource-eater to do that. I.e. if you update something in the BE, it is immediately visible to VBA operations and form navigation, but in the absence of an FE event, the FE display's operation is not inherently triggered by a BE update. To see the edit-flag on the form, you need something that does a timer-based check, and from the description involved here, it would not be a trivial check. That timer will eat CPU cycles like a kid in an untended candy store.

I might consider having a slot in each record, one which is normally empty, and when someone tries to edit that record, create an UPDATE query to put the user info in that slot. This is AIR code because I don't know your environment. You have to assume DIM statements for what I show here.

Code:
Set CDB = CurrentDB
CurrRec = <<the ID number of the record on which the user clicked>>
CurrUsr = <<the initials of the current user for this FE>>
SQLString = "UPDATE TheTable SET LockUsr = '" & CurrUsr & "' WHERE RecID = " & Cstr(CurrRec) & " AND Len( LockUsr ) = 0 ;"
CDB.Execute SQLString, dbFailOnError
NRec = CDB.RecordsAffected

After that, the current user has locked the record by putting his/her initials in the lock slot - or not. NRec should be either 1 (if you got it) or 0 (if the slot was occupied.) At this point, you know one of two things.

A. You are good to go OR
B. The lock slot for the record with an ID of CurrRec tells you who ELSE got the record.

Now, the trick is that if the person is going to update the slot OR LEAVE IT, you must update the LockUsr slot to release it. That could be done easily enough if you never allow a user to edit more than one such slot at a time. Obviously, if you are updating the record, update it with a blank Lock slot If the update will not occur, release it with

Code:
CDB.Execute "UPDATE TheTable SET LockUsr = '' WHERE LockUsr = '" & CurrUsr & "' ;"

IF you allow one user to edit more than one record, that becomes trickier. Now, the question is, HOW DOES IT WORK?

If the lock slot is empty on the identified record, the update will actually set the new value. BUT if the lock slot was NOT empty, it won't And doing it this way makes the operation monolithic. I.e. the test and the lock action are in the same SQL statement. This will be treated as an indivisible operation by the ACE engine because it is a single SQL statement.

If you tried to do this with an IF/DLOOKUP sequence followed by the update action, someone could sneak in between the two steps. But if you do this in a single statement and the test is part of the statement, nobody can sneak in.
 

LGDGlen

Member
Local time
Today, 07:25
Joined
Jun 29, 2021
Messages
109
@The_Doc_Man thank you for your in depth update, really helpful as have all above who have made suggestions, based on what you've suggested i'm going to put together a solution that works in the process we have but the SQL bit is REALLY helpful because the way i would have done it because i know very little about SQL would be recordset findfirst etc which i'm sure would be the WRONG way even if it worked
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:25
Joined
Feb 19, 2002
Messages
33,213
i think i'll set the locking to edit and see what happens
No. Do not do that. You will just interfere with the normal flow of things.

If the reason you are doing this is to assure the users that their updates are being saved, DON'T If you are trying to implement some type of task list, I've done that and can explain how to manage the list so that only one person will ever update a record at one time. Others can view it but not update it.
 

Users who are viewing this thread

Top Bottom