Hi,

I have this form that includes a subform. I had set both form and subform to lock only edited records. When I am editing the form, the other user is able to edit the subform of the same record. However, I want to make it in a way that both form and subform is lock when that particular record i being edited by a user. How to I do so? Any suggestions?

THANKS

There is nothing built into Access that will do that for you.
You would have to write your own locking and unlocking code to do this. This could be fairly complex for a mainform/subform object.

You could add a 'Locked' field to the main form table, and initially set the Allowedits property of both main form and subform to No (preventing editing).
PLace an 'Edit' button on the main form which the user has to click if they wish to edit the main form or any subform record.
In the button click event procedure you test the Locked field.
If it is set then you have to display a message saying the record is locked by another user.
If it is not set then you update the record to set the locked field and then set the Allowedits property of the main form and subform to Yes (Allow edits)
In the afterupdate event procedure of both the main form and the subform, you set the Allowedits property back to No and update the main form table again to clear the Locked field.

The Locked field could be a simple yes/No or it could contain username/datetime (see below) or whatever you want, so that you can display meaningful messages to users who try to edit a locked record.

Clearly, for this to be succesful the user must not be able to update the same records in any other way - only via this form.

You have to consider what to do if the user clicks the edit button but doesn't edit anything before moving to the next record. You might need to use the main form current event to test the form's Allowedits property value - if it is set to Yes then that would imply that the value has not been reset in the previous record and you would need to sort out the Lockedits property settings and the Locked field setting (see next paragraph)

You will need to run a housekeeping job on a regular basis to unset 'locks' that that have been left in place somehow. THis is one situation where using a Username as the Locked field value would help, as you could run this housekeeping job at any time to clear any locks placed by the current user .

You will still need a 'clear all locks' process though - which you might run once a day when no-one is using the application.

Aw, this is so complicated. So there's no way I can do by just editing the properties? I need VBA codes? I am lousy at codings

Thank you very much Peter

>> When I am editing the form, the other user is able to edit the subform of the same record. However, I want to make it in a way that both form and subform is lock when that particular record i being edited by a user. How to I do so? <<

I just want to be clear as to what you want.... you have a Form/SubForm. With that I will assume that you have Table1 bound to the main Form and Table2 bound to the form used as the Source Object of the SubForm control.

When User1 begins an edit on the main Form (a record in Table1), you wish to have all the related records in Table2 (displayed on the SubForm control) locked as well. Is that correct?

If so, peter is correct in that, with out VBA, the best you can do is set the Record Locks property on both the main Form object and the SubForm's Source Object Form to "Edited Record" (aka: pessimistic locking). This will prevent editing to any individual record from Table1 or Table2.

If you use VBA, you can lock the sub-form records with a metadata field (ie: a Yes/No field the enables your code to determine whether or not to open a particular record), but when you do that, you will may be preventing the user with the main Forms record open from editing the subform records. Also, I beleive you can use Transactions to emulate what you are trying to do.

Another aspect to consider is the behavior you wish to have present when a sub-form record gets edited. Should all the "sibling" records get locked, as well as the parent record?

As you can see, locking can grow into a big task if you wish to deviate from standard locking behavior of a database in general, so --- with that, I must ask as to why this requirement is needed? There are other ways to prevent other users from SEEING the main form record when it is being edited, and thus visibility of the subform records would not be possible by a second user, which effectively prevents edits of subform records. So that leads me to my next question ... what scenario exists that make you want what you describe?



>> I want to make it in a way that both form and subform is lock when that particular record i being edited by a user <<

I want to clarify some verbage -- when you are editing the record displayed in the main Form --- the data displayed in the subform is from a different table, so they are different records

Thanks for the reply.

- When User1 begins an edit on the main Form (a record in Table1), you wish to have all the related records in Table2 (displayed on the SubForm control) locked as well. Is that correct? -

Yes this is correct. The table2 is a continous form. Its like a account, there are a lot of transactions. So these transactions details are being capture in Table2. Hence, when I want to edit the account or any transactions in that account, I want all the records related to this account to be locked.

I dont really understand this context: "you will may be preventing the user with the main Forms record open from editing the subform records"

Also, when I use peter's method, will users be able to edit different records concurrently? What I understand is, if a user is editing a record, as soon as he locks it, other users are unable to use the same form to edit other records.

...will users be able to edit different records concurrently? ...

Yes, provided you set up the multi-user app correctly by splitting the database into front-end and back-end and giving each user their own copy of the front-end.

This project doesnt allow me to split the database. Which what I think it's a bad choice, but that's the final decision made. So I dont think I am able to lock the fields through AllowEdits = false.

Anyway, when I was testing the system without any locks of records including the Global Settings of Access was (Exclusive and No Locks). However, I do recieve a Write Conflict pop out when I was trying to overwrite the record. Was wondering if this is an automatic pop out that the MS Access provides when it discover there is user conflict? Or rather is .ldb created at any senarios or only when you lock your records?

Aside from the main issue of this thread, if you don't split the database and you have multiple users you will potentially spend the rest of your life fixing corruption problems.

In terms of this Q, your requirement requires 'isolation' of a set of records for a specific user. Whatever solution you adopt is going to require user-specific tables or settings. I don't see how you will achieve that without splitting the file.

>> Yes this is correct. The table2 is a continous form. Its like a account, there are a lot of transactions. So these transactions details are being capture in Table2. Hence, when I want to edit the account or any transactions in that account, I want all the records related to this account to be locked. <<

My question would be ... Why? It seems that someone should be able to edit transaction#1 whilst someone else is editing transaction#3 of the same parent record --- that is the point of a relational database (IMO).

>> I dont really understand this context: "you will may be preventing the user with the main Forms record open from editing the subform records" <<

What I mean is that if you create a yes/no field in the child table (lets call it IsLocked for easy discussion) for the records in Table2 that toggles (via a VBA procedure) based upon the record lock status of the parent record in Table1, you may end up having cases in which you render your transaction records un-editable (by way of a test of the IsLocked field prior to editing) by the user who locked the record in Table1 -- which is not what you want.

>> Also, when I use peter's method, will users be able to edit different records concurrently? <<

User1 can edit Record1 from Table1 and User2 can edit Record2 from Table1, but User1 can not edit Record2, while User2 has Record2 opened for editing (and vice versa).

>> What I understand is, if a user is editing a record, as soon as he locks it, other users are unable to use the same form to edit other records. <<
...
>> So I dont think I am able to lock the fields through AllowEdits = false. <<

That is untrue, expanding on the above statements concerning editability, if User1 is editing Record1 using Form1 in an instance of a db application, Form1 can be used by User2 to edit Record2 in another instance of the db application. The AllowEdits property is per instance, so if the db (and subsequently the form) is opened on 5 machines, then Form properties that are modified in one instance do not effect (or cascade) to the other instances. With that said, this is often how corruption begins because invariably someone will try to save the modified property settings to the saved Form Object -- so you may have 5 people trying to save 5 different sets of properties to one Form object at the same time. With out splitting and distributing the Front End your risk of corruption increases significantly. At an absolute minimum, you should split the db into a Front End (Forms, Queries, Reports, and code) and a Back End (data only) and keep both files in a common network share folder. By doing this, your data (Back End) is at a low risk for corruption while your Front End is at a higher risk, but you can replace the Front End file with a master copy at any time with out effecting your data. In either scenario (distributed FE or FE & BE in same folder), you should utilize the MDE/ACCDE file format for your Front End. If you are not split, then I would still utilize the MDE/ACCDE format in order to reduce the risk of corruption that can be associated with Object modification.

>> Anyway, when I was testing the system without any locks of records including the Global Settings of Access was (Exclusive and No Locks). However, I do recieve a Write Conflict pop out when I was trying to overwrite the record. Was wondering if this is an automatic pop out that the MS Access provides when it discover there is user conflict? Or rather is .ldb created at any senarios or only when you lock your records? <<

You seem to be using the term "lock" in two different contexts. The first one was in the scope of prevention of edits through a form; with the above you are refering to database level record locks. There is a difference in those concepts.

Using the Form, you can lock the seto of CONTROLS via the AllowEdits property or individual Controls with the .Locked property of each control. By controling the ability to modify values displayed in a Forms set of Controls you have not prevented the records from being edited through an alternate mechanism, or another user on another instance of the Form, you have simply prevented A user from modifying the data through the form THEY working with -- remember the .Locked and .AllowEdits properties do not cascade to all instances of the form created and being used by other users.

With database level record locking, the records are locked regardless of the mechanism in place that modify records. Database level locking is managed by the database engine and effects ALL users of the data, regardless of the instance of the database/form is being used. You can control database level locking via the Record Locks property of a Form -- but -- I would not recommend modifying that property in each instance of the Form. Set it during design time to what you want (I recommend "Edited Record") then leave it be. Also, you have brought up the global setting of the Default Record Locking strategy -- That setting controls the value that is used for the Record Locks property of a NEWLY created Form and the strategy of locking that a Table or Query Object takes on when you view the object in Datasheet view.

With the two differing scopes, I will use the term "prevention of edits" and "record locks" (or something similar) to distinguish between the two concepts.

Now lets look at two differing record locking strategies the database engine handles. The first being "Pessimistic Locking" (Edited Record on the Record Locks property of a Form Object), the record being edited is locked immediately upon becoming opened for edit. So other users can view the locked record, but they can not open it for editing. "Optimistic Locking" (No Locks on the Record Locks property of a Form Object), the record is locked right before the changes are committed to the database then unlocked immediately after the commit (save) is successful. So ... a time line of each strategy looks something like this (may be slightly different on a way deep technical level -- but the jist is about the same):

Pessimistic:
edit started > record is locked > record is opened > user edits fields > commit started > changes commited to db > lock released.

Pros: no one else can edit the record.
Cons: locks can be "expensive" to manage for the db engine, plus they can be troublesome if left open for too long.

Optimistic:
edit started > record is opened > user edits fields > commit started > record is locked > changes commited to db > lock released.

Pros: Short lock time; "Cheap"; Keeps lock count low, thus improving performance and scalability.
Cons: two (or more) users can edit the same record at the same time -- the first one to save wins, and the second one is prompted before the save is complete regarding the data concurrency conflict (that is the dialog box you saw!).

----------

Using a "home grown" method to emmulate record locking, thus enabling the prevention of edits can intensive thing to manage if implemented as I have described. For example, if you begin editing the parent record, you would likely use the OnDirty event to execute an Action query and update ALL children records (your transaction records) to be "locked" (SET IsLocked = True). So if you have 100 transaction records associated to the parent record, you drastically increase your databases work load just to edit one field of the parent record because you now have 200 updates (100 to set IsLocked to True when editing begins and 100 to set it to False). If your db is a multi-user system, which you indicate that it is, your performance will take a huge hit. For example, lets take two users, each editing 1 parent and each parent record has 100 transactions --- so, you would be modifying the IsLocked field on 200 records, twice! 400 record modifications -- just to modify a field or two in the parent record. Now --- lets take 4 users --- you can see how that can escalate! I personally would not recommend this type of implementation to prevent edits of children records! ... I do have a recommendation (pausing for a moment to create anticipation and excitement ----- )

-----

Now, we are to my recommendation. With your goal, which seems to be to prevent edits of related records through record locking.

So ... to do this lets set up a extra field on the table that is bound to the main form. I will call this table tblEmployees. The "extra field" is named DateStamp. The DateStamp field is defined to have a default value of Now() and its value will be subsequently managed by the application. In other words, the field is not shown to the user -- if it is, the control used to display the value should have its Locked property set to True in order to prevent edits of the value.

Next we'll have to add some code to the main Form Object (which I will call "frmEmployees") as well as the Form Object used as the Source Object of the SubForm control (aka: the sub-form), which I will call "frmEmployee_Orders". The majority of the code will be on the sub-form with the code fired on the forms AfterUpdate, OnDirty, and OnUndo events. Also, it is important to set the Record Locks property of each Form object to "Edited Record"

The OnDirty event code will attempt to modify (but NOT save) the DateStamp field of the main forms (aka: parent) record. If the code can modify the field, the main form record has a record lock automatically placed upon it by the database engine, thus preventing any other user from anywhere modifying that main form record. In addition, a record lock is automatically placed on the child record being edited, thus preventing edits from any other user. If the OnDirty code cannot modify (or rather have the db engine apply the record lock to) the parent record, the OnDirty event of the sub-form is CANCELLED, thus preventing the editing of the child record. Even though a record lock does not exist on the child record, any user attempting to modify a child record on a record locked parent, will be unable to do so through the form/sub-form with this code in the OnDirty event.

The OnUndo event code will undo the changed DateStamp field of the parent record when the child record changes are undone, thus releasing the record lock of the parent record as the child record's lock is released do to the undo.

The AfterUpdate event code will save (commit) the new value of DateStamp on the parent record, thus releasing the record lock of the parent record as the child record's lock is released do to the save.

----

By using this technique (which is actually similar in nature to peter's original suggestion -- i just pushed as much a I could to the dbengine level), you capitalize upon the dbengine's inherent locking capability as well as a form's ability to prevent edits based on record level properties. Also, you minimize database "expense" while creating the user experience you want.

In order to examine this concept more closely, I have attached an A2000 mdb (created in A2007) for you do examine. I hope it helps you out.

>> In terms of this Q, your requirement requires 'isolation' of a set of records for a specific user. Whatever solution you adopt is going to require user-specific tables or settings. I don't see how you will achieve that without splitting the file. <<

Hello Peter,

Check out my sample and you will see how it can be acheived without splitting and without user-specific tables ... granted, I am with you -- the db should be split, as I mentioned in my brief post

Hi iijane,

You have received so many good advice already so I will just welcome you to UtterAccess.

Thank you very much!! However, my form is actually a amendment form which the record source is a query. =/ does it works the same way? because it seems that I am having some bug.

It depends on the query .. if its updatable then the technique I proposed should work fine. Any chance of posting a sample db that emulates your scenario?

Oh. No sample! This is an company file so I cant show it! But i read online, they have this recordset thing. Locking through recordset? And recordset is define as query calling out a tables? I am so confused now.

I would not want the actually company data ... I was just hoping you can build a database that closely represents what you have (ie: only the pertinant Table objects, Query objects, and Form object --- but the tables would have bogus data in them.

----

You can use recordsets and such, but as I inidcated earlier, if the Query bound to the main form is editable, then you can use the technique I proposed -- you would just need to add a DateStamp field to the table of the Query object that contains the linking field to the sub form data.

Have you looked at my sample?

Yes i did look at your sample and try to absorb and put it into my file. However it has a bug (run tiem error 2448). It seems like they do not recognize the "datestamp" field.

>> Yes i did look at your sample and try to absorb and put it into my file. However it has a bug (run tiem error 2448). It seems like they do not recognize the "datestamp" field. <<


Who is "they" who ever "they" is, is likely correct in that the field is not in your table. The is why in my long post there is a paragraph that says:

"So ... to do this lets set up a extra field on the table that is bound to the main form. I will call this table tblEmployees. The "extra field" is named DateStamp. The DateStamp field is defined to have a default value of Now() and its value will be subsequently managed by the application. In other words, the field is not shown to the user -- if it is, the control used to display the value should have its Locked property set to True in order to prevent edits of the value. "

So in your case of the query as the RecordSource, you will have to add the field to the main table of the Query object, then make sure the DateStamp field is returned by the Query Object.

Oh i did add a field. Okay i'll check again tomorrow and try thank you very much! I'll see what bug was it and let you know!

The bug is: Method or data member not found.

The form is taken from the query. Hence I did drag datestamp into the field of the queries. However it still state not found. and i've created the datestamp field into the master table.

Is the master table part of the query that feeds the main form?

-----

This will best be solved by a sample db. Please construct a similar form, query, and child tables as to what is in your actual database --- we don't need your proprietary data, nor your proprietary application --- all we need is something similar that matches this scenario.

If you can't do that, then I recommend that you study the sample I posted very carefully as it will work with a Query as your RecordSource for the main form. Its just a matter of getting all the pieces in the right spots.

Sorry datAdrenaline, I am super busy with my modification, hence i did not reply on thread! Anyway I already solved the problem and it can be done already! THANKS A MILLION!!!

Glad you got it sorted out! ... Good luck with your project!