Locking a Record with Warning

[MattBaldry]

Is there an easy way to mark a record as locked so if a second user tries to open it they get a warning it is already opened by someone?

Sage Accounts does this and I would like to implement it in some way.

I am thinking I have a field (or fields) in the table that when the record is open, if puts the username in and if someone tries to open it and there is a name in that field it gives them a message saying that the record is open by that user.

Has anyone tried this before, is there a better way?

~Matt

 

[vba_php]

Is there an easy way to mark a record as locked so if a second user tries to open it they get a warning it is already opened by someone?

if I'm not mistaken, when you implement "record level locking" the default behavior is for access to throw it's own error message to the person that is 2nd in their attempt to open the same record when the 1st person already has it open. I believe there's also "table level locking" where access behaves the same way, but with regard to an entire table object being locked by the open form instead of a single record. Why create a custom message if access does it anyway?

I am thinking I have a field (or fields) in the table that when the record is open, if puts the username in and if someone tries to open it and there is a name in that field it gives them a message saying that the record is open by that user.

I think your thinking mite be a bit off on this one Matt. You can't lock records based on the fact that *one* field is being edited. The record as a whole is either locked or it isn't. You might see a corrective post come through from someone else if I'm wrong here.

 

[MattBaldry]

if I'm not mistaken, when you implement "record level locking" the default behavior is for access to throw it's own error message to the person that is 2nd in their attempt to open the same record when the 1st person already has it open. I believe there's also "table level locking" where access behaves the same way, but with regard to an entire table object being locked by the open form instead of a single record. Why create a custom message if access does it anyway?I think your thinking mite be a bit off on this one Matt. You can't lock records based on the fact that *one* field is being edited. The record as a whole is either locked or it isn't. You might see a corrective post come through from someone else if I'm wrong here.

Thanks Adam. What I was hoping was something like the below.

Tom is editing purchase order 12345
Harry tries to open purchase order 12345 and gets told Tom has it open, so the form then closes

Sage Accounts has this feature and it is one of the few things I like from Sage.

As such, I don't need to actually lock the data or record, I just need to stop someone else from even opening that record if someone is working on it.

I thought that if I had a field that was updated to a name when the record is opened, and then that field is emptied when they close the record down, then it would give me what I need.

~Matt

 

[vba_php]

What I was hoping was something like the below.

Tom is editing purchase order 12345
Harry tries to open purchase order 12345 and gets told Tom has it open, so the form then closes

If I'm not mistaken, access already does this. it throws a message to Harry when he attempts to open the form and rejects him from even seeing the form object in an open state. Haven't you tested this?

As such, I don't need to actually lock the data or record, I just need to stop someone else from even opening that record if someone is working on it.

that is exactly what record-level locking is. It's a built-in feature of the application my friend.

I thought that if I had a field that was updated to a name when the record is opened, and then that field is emptied when they close the record down, then it would give me what I need.

well based on everything I just said you don't need to do this, but if you really want to, there's an endless amount of ways it can be done. One easy way is to create a table called "cur_user" or something, then when a form is opened, throw the username into a field and then when person 2 tries to open the same form, point to the field and check for a piece of content. if TRUE, deny access, if FALSE, allow form to be opened.

Moreover, at the record-level locking stage instead of the table-level locking stage that I just described, you can throw an error message to a user after they click on the "next record" button if they are attempting to go to a record that is opened by another user. To detect if the record is "locked", all you have to do it throw a name into the table like I described for "table level locking" when users navigate from one record to another. REPLACE [recordID_being_edited] field with new value when user moves from one rec to the next. I assume you're using buttons for rec navigation? If you're letting users use the nav buttons that are provided in forms by default by access, that's a bad thing and unsecure to boot.

 

[MattBaldry]

If I'm not mistaken, access already does this. it throws a message to Harry when he attempts to open the form and rejects him from even seeing the form object in an open state. Haven't you tested this?that is exactly what record-level locking is. It's a built-in feature of the application my friend.well based on everything I just said you don't need to do this, but if you really want to, there's an endless amount of ways it can be done. One easy way is to create a table called "cur_user" or something, then when a form is opened, throw the username into a field and then when person 2 tries to open the same form, point to the field and check for a piece of content. if TRUE, deny access, if FALSE, allow form to be opened.

Moreover, at the record-level locking stage instead of the table-level locking stage that I just described, you can throw an error message to a user after they click on the "next record" button if they are attempting to go to a record that is opened by another user. To detect if the record is "locked", all you have to do it throw a name into the table like I described for "table level locking" when users navigate from one record to another. REPLACE [recordID_being_edited] field with new value when user moves from one rec to the next. I assume you're using buttons for rec navigation? If you're letting users use the nav buttons that are provided in forms by default by access, that's a bad thing and unsecure to boot.

At the moment access doesn't me a message if someone tries to open the same record. If I have a PO open on my machine, someone else can open it, but if they try to type if doesn't allow them to.

With regards to the next/previous buttons, I don't actually use these. I have a form, frmPurchaseOrderList. On here is a listbox with all the Purchase Orders. Double click to open the one you want, and it opens only that data in the form. When you are finished, you close it, but you cannot move through the records from the form frmPurchaseOrder. If you want to see another order you just double click the list again to open the one you want to see.

The issue I have found was this. Person A had a form open displaying some data but were editing the data in the subform datasheet. A second person opened the same record and changed the main form data. I believe this was possible as that record was open but not in the process of being edited, the subform data was and therefore the record in tblPurchaseOrderItem was locked, but the record in tblPurchaseOrder was not locked.

I hope I am explaining this correctly. Please let me know if it doesn't make sense.

~Matt

 

[vba_php]

hmmmm...

well I have to admit, I'm more of a web developer and server type guy now than an Access guy, so you might need help from someone else on this one, but let me try:

see image for where you lock records and options to do so. maybe you already know that. are you issuing a *popup* form with a higher z-index to the user when the double click a PO in the listbox? as in, that form stacked on top of the one where the listbox remains? If you are, the properties in the attached image should work fine. sounds like you are. the bottom level form is "frmPurchaseOrderList" and the top level form is "frmPurchaseOrder"?

The issue I have found was this. Person A had a form open displaying some data but were editing the data in the subform datasheet. A second person opened the same record and changed the main form data. I believe this was possible as that record was open but not in the process of being edited, the subform data was and therefore the record in tblPurchaseOrderItem was locked, but the record in tblPurchaseOrder was not locked.

that might simply be an issue of you choosing the wrong property setting that I have illustrated in the image. If it's not, then you might have to ask someone else here about that one because I develop in a way that stacks forms on top of forms just like Larry Ellison does with Oracle. That forces users to enter and manipulate data in one way and one way only. Remember, I work for some pretty strange people that are capable of all kinds of weird occurances when operating software.

 

[isladogs]

One possible strategy you could consider if you really want to do this

Add a Boolean field to the table and call it Tag or similar. Default value =False
When a user opens the form to a particular record, run an update statement to set Tag = True for that record. When the form is closed, reset Tag = False.
If another user tries to open the form to that record whilst Tag= True e.g. On a button click, show your message and don't open the form.
If you allow users access to the navigation pane you would instead need to add code to the load event of the form which would show the message and close the form immediately.

Any use?

 

[Uncle Gizmo]

Might be wise to add some way of identifying who applied the flag, just in case MS Access closes down unexpectedly and leaves the flag in place.

So now when a user opens the form it could check to see if there were any flags in place for that particular user and reset them to false.

In fact, you could use an integer instead of true/false so you could use the integer to identify the user. So you'd still only need one field.

I have been watching this thread with interest because I quite expected Colin (Isladog) to come back with a solution using some sort of hidden table!

I'm a little disappointed by Colins response... (Tongue in cheek!)

Sent from Newbury UK

 

[isladogs]

I have been watching this thread with interest because I quite expected Colin (Isladog) to come back with a solution using some sort of hidden table!

I'm a little disappointed by Colins response... (Tongue in cheek!)

Well now you mention it, there is the little used deep hidden system table MSysComplexTypeUnsignedByte …

 

[Deleted member 147267]

When the form is closed, reset Tag = False.

As well as the Current event if navigation is involved.

 

[Pat Hartman]

I do this for a task list in my app. The form opens to show the ToDo list. But to prevent people from working on the same problem, I update the item when the first person selects it. I add both the UserID and the SelectDT to the record. When a user selects a record from the ToDo list, the edit form opens with edits locked if someone (other than the current user) already is working on the item. The heading shows who has the record and when they opened it.
The ToDo list does show Who and the Date but for any given individual, the form might not have refreshed so you can't always tell by the list if the record is locked.

When the edit form was closed normally. I ran an update query to reset the lock fields because I didn't want to dirty the record again.

That leads us to how to handled locked records. A record can stay locked for a variety of reasons so here are some things the app does to clean this up.
1. If the same person opens the locked record, I update the date to refresh it.
2. When the app opens, it checks the ToDo list for items left open by THIS person and brings him directly to the list so he can clean up the locked records.
3. There is a button on the edit form that with a supervisor password will allow the locked fields to be reset to nulls.

There may be other tweaks but I did this at least 5 years ago so I might have forgotten something

 

[isladogs]

As well as the Current event if navigation is involved.

The OP made it clear in post #5 that users cannot navigate through the records. It was that point that led me to suggest using the Boolean Tag field method.
That approach would be much harder to manage if navigation was allowed

 

[vba_php]

I think Matt might be getting overwhelmed at this point guys. But we can all agree that he is not short on options.

 

[isladogs]

Adam
As I said in a recent thread, allow Matt to speak for himself.

 

[MattBaldry]

Thank you all for your input. After reading them all, I think I am going to try a table that stores the user id, form opened and date/time it was open and then closed. To avoid records staying locked if there is a crash, or they somehow manage to close the form without using the close button, I will have a button that will search for forms that have been open for more than 2 hours and then update them to open.

I don't think anyone I work with would need to keep any record open for that long, so that should fine to re-open the locked record.

I have a new database I am building for someone so they will be the first to try this for me.

I will update this if/when I have a working solution.

~Matt

 

[Pat Hartman]

You really don't need a second table. Add the two fields (user and date) to the record you want to lock.

 

[GK in the UK]

I've implemented the method described in post #11 but I must be missing some detail as it's flawed in my application.

I have a main form with sub form for transactions, 1-many.
Main form record has fields thUserInUse and thDtInUse.

Validation is in BeforeUpdate of the main form.

The user could make edits to one or more fields on the main form and edit a line which will fail validation.

Before saving the main form, user can add or edit a record in the sub form which will also fail validation. To enforce validation, I have to dirty a main form field so that BeforeUpdate executes. So if the transaction line is edited, I say (for main form record) thDate = thDate, and this causes an attempted save with validation.

So on attempted save we have fields in the main form which may or may not be invalid, and a record in the sub form which *will* fail validation.

On an attempted save, validation pops up message to inform the user of the invalid fields, which will include an invalid transaction line in the sub form.

The issue I have is that the main form record has been dirtied and I can no longer double-click the line to open it. Access just keeps popping up the validation fail message, presumably because it's trying to do a navigation save when I go from main form to click the sub form line.

The only way I can make this work is to Undo the main form on the failure of the save, in the Error Handler. Then, I can navigate to the offending detail line to fix it. But, I have undone all the fields that the user edited on the main form.

I tried setting thDate.Undo and undoing the 'InUse' fields that have been set, in the Error Handler, but it doesn't 'clean' the record. It didn't appear to do anything.

Is there a way I can somehow inhibit the navigation save so I can return to the invalid transaction line ?


edit: Why don't I validate the detail line in it's own pop-up form? The line in isolation may be fine. This is a double-entry accounting system. If the user attempts to save the form, the net total of the lines *must* be zero. So I *must* enforce either the deletion, or edit, of the existing line, or await another line to be added which will balance things up.

 

[GK in the UK]

I think I can solve this by putting the 'balance' validation in the form Unload event and cancelling it if the records don't net down to zero