Full Version: Write protect a record
UtterAccess Forums > Microsoft® Access > Access Forms
chris7575
I would like all users to be able to access and add/update records but need to have a user that will approve the record. Once the record has been approved users can only view the approved record. Ideal scenario would be a check box that only certain users (e.g. admin) can tick and once this has been ticked the record is locked to all or low level users.
Could this be fairly straight forward as I am quite new to access.
Thanks
Jack Cowley
Welcome to Utter Access Forums!
You can add the checkbox field to your table. In the On Current event of the form that displays the records from your table use code similar to this to 'lock' records that have the checkbox ticked:
If Me.NameOfCheckboxControl Then
Me.AllowEdits = False
Me.AllowDeletions = False
End If
Change object names as necessary.
hth,
Jack
chris7575
Thanks for that, is it possible to limit the tick box to defined users?
Jack Cowley
If UserType = 1 Then
Me.NameOfCheckboxControl.Visble = False
Else
Me.NameOfCheckboxControl.Visble = True
If Me.NameOfCheckboxControl Then
Me.AllowEdits = False
Me.AllowDeletions = False
End If
End if
sers with a UserType of 1 will not see the checkboxes... If you want the check box to still change the AllowEdits, etc. then modify the code above.
hth,
Jack
chris7575
Thanks thats a great help.
Jack Cowley
You are welcome and good luck!
ack
chris7575
I can add a checkbox to a form but how do you add one to the table?
chris7575
Think have added to the table ok but cannto find the on current event in the event box. Also once locked is there anyway ofunlocking?
Thanks
k9huey
I do something like this with my forms. I have given all users different security levels and I use nested if statement similar to the one Jack give you above. I put my code in the OnClick event of a button. It checks the persons security level and then either locks or unlock the data.
en
k9huey
I do something like this with my forms. I have given all users different security levels and I use nested if statement similar to the one Jack give you above. I put my code in the OnClick event of a button. It checks the persons security level and then either locks or unlock the data.
en
chris7575
Thanks, I have managed to get the lock to work but get a run time error 94 (Null) when I open a new record.
In your example can an administrator update the record once it has been locked?
chris7575
Jameson, could you post the exact code you use to perform this?
Thanks
k9huey
Here is the code that I use on a button to lock the form. I have a hidden form that stores the security level after a user logs in. That is the "Forms!frmhiddenSL!txtHiddenSL "
ome background on the part of the code. This button is on a service report form that a user will fill out and then must be approved by a supervisor and then sent to a customer. Once it is Sent to a Customer then it locks the editing.
CODE
  Private Sub cmdLock_Click()
    'Click event to mark a service report as sent to customer. Then lock the form
    'for editing. But if all ready marked as sent to customer then unmark it and
    'unlock the form
    If (Forms!frmhiddenSL!txtHiddenSL >= 2) Then   '2 is Data Entry Security Level
        If Me.SentCustomer = True Then
            Me.SentCustomer.Value = False
            Me.cmdApproved.Enabled = True
            Me.cmdNotDone.Enabled = True
            Me.Approved.Enabled = True
            Me.AllowEdits = True
            Me.AllowDeletions = True
            Me.AllowAdditions = True
            Me.Refresh  'Had to refresh form for the Allow___ to take effect
        Else
            If Me.Approved.Value = False Then
                MsgBox "This service report must be Approved" _
                & vbCr & "before it can be sent to the customer", vbOKOnly, "Notice"
            Else
                Me.SentCustomer.Value = True
                Me.cmdApproved.Enabled = False
                Me.cmdNotDone.Enabled = False
                Me.Approved.Enabled = False
                Me.AllowEdits = False
                Me.AllowDeletions = False
                Me.AllowAdditions = False
                Me.Refresh   'Had to refresh form for the Allow___ to take effect
            End If
        End If
    Else
        MsgBox "You do not have security clearance to mark this as Sent to Customer!", vbOKOnly, "Notice"
    End If
End Sub

HTH
Ken
chris7575
Thanks, I really need to get my head round the visual basic side of things as this looks very complicated. Have you got one button for approved and one for sent to customer?
k9huey
I actually have three buttons. One for the service guys to mark the service report as DONE. One for the Supervisor to mark as APPROVED and then one for SENT TO CUSTOMER. You probably could make one button do all three but I had room on my form for three so I left it that way.
en
chris7575
I think I could do with two buttons, one that lets a level 2 lock the form and one that will let a level 1 unlock it. Should this be fairly straightforward?
k9huey
I would think so. Make sure you ask them if they are sure they are ready to lock it before actually locking it. That way they have a second to think about if they are actually ready to lock it.
en
chris7575
Finally got round to incorperating this in to my database but I just have one other question. How are user groups 1 and 2 defined for security purposes or do you add the user group name (e.g. operations and line managers) in place of 1 or 2?
Thanks
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.