Locking data on a record 1

[EuanPirie]

I have been asked if I can lock data in a database. This needs to occur on a record when a status is changed to &quot;completed&quot; on a field in the record. When this happens, no more changes can be made to that record. I believe that it is possible to lock record, however my understanding is this is for multi-user record locking. Any ideas?<br>
<br>
Thanks for the Cancel button, works a treat now. So simple....<br>
<br>
Euan.

 

[phparadis]

I am not sure exactly what you mean....but if you are interested in displaying the data to a user on a bound form and prevent them from editing that record where the field &quot;Completed&quot; is true (assuming a logical field) you could use the Form_Current event to change the form property &quot;recordset type = snapshot&quot;. The following is taken from the MSAccess help on Recordsettype Properties<br>
In the following example, only if the user ID is ADMIN can records be updated. This code sample sets the RecordsetType property to Snapshot if the public variable gstrUserID value is not ADMIN.<br>
<br>
Sub Form_Open(Cancel As Integer)<br>
Const conSnapshot = 2<br>
If gstrUserID &lt;&gt; &quot;ADMIN&quot; Then<br>
Forms!Employees.RecordsetType = conSnapshot<br>
End If<br>
End Sub<br>
However when I tried to compile I got a variable not defined on &quot;conSnapshot&quot;. Unfortunately, I don't have time at this point to continue investigating. But it seems it can be done. Using Form_current will trigger this procedure every time the form is opened and each time the record is changed (assuming it is a bound form). You can reset the recordset type property to dynaset if &quot;completed&quot; = false so the editing is available<br>
Hope this helps and that I interpreted your objective correctly

 

[phparadis]

Oops........I had some time and I tried to implement my strategy on a working form and have had no luck.<br>
Sorry about that<br>
The variable is defined in the example code. I missed it. <br>
When I placed that line of code in the OnCurrent event I generated a loop, stopped, and I was still able to edit.<br>
I'll try and find the problem and if I can find a solution I will post it.<br>
Again, my apologies

 

[EuanPirie]

You have indeed interpreted me correctly. I'll have a play as well. Thanks a lot for your help! If you manage to crack it, post it up.<br>
<br>
Thanks again!

 

[elizabeth]

Haven't tried this, but if your form shows a single record at a time, you should be able to set &quot;Allow Edits&quot; and &quot;Allow Deletes&quot; based on the value in Completed in the form's OnCurrent event.

 

[phparadis]

I had a different solution and was about to post when I saw Elizabeth's solution. <br>
I added AllowAdditions = false<br>
I checked out her idea and it works great. Thanks Elizabeth (even though it wasn't my problem)

 

[EuanPirie]

Thanks folks!

 

[elizabeth]

Happy I could help