Record lock

[usman948]

Hi..

Sorry that after long period i came to form for help and hope that I will get the same.

I have made database of billing with many important fields. I want to lock the entire record so that others cant edit that record. I have added field name edit lock with yes/no., when i check the edit lock field entire record is to be locked and give message that "your record is locked and cannot edit/delet. and when I will un-check the field it should ask for password and than allow for edit/delet the record.

Kindly please help me with some vba code. Thanks in advance

 

[Mihail]

Not sure what you intended to do but from your explanation the AllowDeletions property of the form should do the trick.
So create a button for record deletion.
When this is pressed ask for password.
If password is OK set AllowDeletions to True, delete the record then restore the AllowDeletions to False.

 

[usman948]

Thanks for reply.

My main form having a list view of works which is linked to sub-form in datasheet view having many fields/rows with details and billing and also one yes/no check box field (edit lock), when i checked yes, entire record/row should be locked so that nobody can edit any field of record and when I unchecked the same it should ask for password and allow for edit of this record.

This require vba code for which I have no knowledge.
I truly appreciate any help from you guys and thanks again for your help in advance.

 

[gemma-the-husky]

include this line in your forms current event

me.allowedits = me.newrecord

this will lock any existing record. If you find that this is over-restrictive, or not sophisticated enough, then you need to find a way of releasing the lock

maybe a button with this code

me.allowedits = true

 

[usman948]

Thanks guys..

I have added this code to subform..

Private Sub Form_Current()

If Me.Editlock = True Then
Me.Form.AllowDeletions = False
Me.Form.AllowEdits = False

Else
Me.Form.AllowAdditions = True
Me.Form.AllowDeletions = True
Me.Form.AllowEdits = True

End If
End Sub

This will locks the record which i wanted, I also want to add inbox asking whether this record to be locked, if yes than lock, if no undo.. Pl help where to add and how?..

In fact i had added message box before Else, but when first record was locked in subform and whenever i navigate and click the parent record in listbox it will pop-up the message un-necessary.

Ok.. lets first solve the locking issue and than we will think of unlocking..

 

[Mihail]

More condensed, your code can be replaced with:

Me.Form.AllowDeletions = Not Me.Editlock
Me.Form.AllowEdits = Not Me.Editlock

 

[usman948]

Thanks Mihial for help..

I have final complete code here and it works well for record locking..

Private Sub Form_Current()
Me.Form.AllowEdits = Not Me.Editlock
Me.Form.AllowDeletions = Not Me.Editlock
End Sub
-------------------------------------------------
Private Sub Editlock_BeforeUpdate(Cancel As Integer)
If Editlock = True Then
If vbNo = MsgBox("Selected Record will be locked... Are you sure you want to LOCK?", vbYesNo, "Lock Alert") Then
Cancel = True
Editlock.Undo
End If
End If
End Sub
------------------
For un-locking I have made record-edit-form which will open by clicking button on main menu with password, this form sourced via a query with criteria of edited-lock only records. Here edit-lock field can be unchecked for un-lock the required record.

This is slightly lengthy, actually wanted to have direct un-check facility with password confirmation on the same form. Pl help me in this regard..

Thanks again to all..

 

[Mihail]

As far as I can think, is not possible to unlock only one record in a form that display more records (a continuos form). I repeat: as far as I know.
On the other hand I'm sure that it is possible to force Access to remain in a certain record or, if you navigate to a new record, the Lock propertie will be automatically applied.
But this approach involve a lot of VBA = few hours for an experimented (as me )))) ) programmer.

On the other other hand , I think that a simplest way is to test from the beginning (at login phase) if the user is (or not) able to make changed in records.

One more point:
Is not necessary to test a boolean value against "True".
In the real life you say: IF I have money then I buy an yacht.
You don't say: If I have money is true then I buy an yacht.

As well, when you use this in a program, you don't say
If Editlock = True Then .....

You simple say:
If Editlock Then ......

or, in order to negate this:
If Not Editlock Then.....

 

[usman948]

Thanks Mihail for help and tips in VBA programming,
Actually I have very little/No knowledge of program like VBA... Just learning and do most of the work by trial and error method..
I do agree that .. there must be a solution for un-locking with lot of VBA programming as u said but for me it will be very tough to make out...

This database may operate few peoples and after completion job/record they will lock and only admin can unlock it to edit if required.. For this I think unlock form I made earlier will be fine..

Thanks again for all help..

 

[gemma-the-husky]

I don't quite follow the discussion you are having.

this "lock/unlock" you are talking about is completely artificial.

if you open the table, you can change the data at will - but you are not working through tables, you work through forms.

So - the only record you can ever "edit" is the current record - so what you need is some code in the current event to determine whether the current record is locked or not - but the lock you are placing is just your business logic - it's not an actual permanent lock.

it doesn't matter whether your form is a single form, a continuous form or a datasheet - you are moving from row to row, and each row fires a distinct "current event" that you can use to control that row,

 

[jdraw]

Here is a link to a post on "locks" that may be relevant, or at least useful as a reference.

 

[usman948]

Thanks to all for help.. my DB works as I needed with suggestion from you guys,
My parent form have list view which is related to subform (in datasheetview) and current record will be locked when checked Editlock field (yes/no) and cannot be unlocked again when after record change.. ok it works fine but here I have one small glitch, that suppose when i have locked the first record in subform (first record of linked table which will show up first in subform) and than goes to parent list and selected another record and locked that too and comes again to first record and finds that the record can be unchecked easily. I am unable to sort out the issue.. This happens only with first record (of table). Pl suggest what to do. Thanks in advance..