>> 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
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.