Link to home
Start Free TrialLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

lImit Edit of records based on Logon (GetuserName)

Need a little help. - Need to limit users capability to edit a locked record based on username/group.

Note; the form is a main/subform- it is the subform (datasheet view) I need to restrict the Locked records.
I am currently setting the lock based on a field (checkbox).

If the User = "User" then they should not be able to edit any records where checkbox = -1, but should be allowed to add new record. but allow the user to edit any records where the checkbox <> -1.

Shoudl this be on Current of the Subform or on focus of active record?

The record becomes locked once the user prints the report.

this is what I have so far:

Private Sub Form_Current()

    Dim frm As Form
    frm = forms.Form.sub1
    
    If gUser = "User" And Me.RecordLock = -1 Then
        With frm
           .AllowAdditions = True
           .AllowDeletions = False
           .AllowEdits = False
        End With
    Else
        With frm
            .AllowAdditions = True
            .AllowDeletions = True
            .AllowEdits = True
        End With
    End If
    gInvID = Nz(Me.IDInvoice.Value)

End Sub

Open in new window

Avatar of Jack Leach
Jack Leach

>> Shoudl this be on Current of the Subform or on focus of active record? <<

Current.  Focus is a GUI state, Current is a Form logic event.  Thus, I prefer only to use the Focus event when I need some particular GUI-related thing to happen not in relation to when the form is in a certain state, but in relation to where the user's mouse is (which is pretty much never).

Also, you'll want to do this in the AfterUpdate event of the form as well, I'm guessing.

Essentially, you want to set a form state (editable/not-editable).  I'd create a dedicated procedure for that and then call it from any event you like (Current, AfterUpdate, etc)

Cheers,
Avatar of Karen Schaefer

ASKER

thanks for the input, however, need clarification.

I want the user depending on Login to be able to either edit any record or just be able to add a new record if the record is locked.

I am having an issue with my code regarding the frm value please check my syntax
 frm = forms.Form.sub1

Open in new window


For objects, a "Set" is required to set a variable.

Forms is the name of the open forms collection, which is followed by an index or form name to denote the actual form you want:

Set frm = Forms("MyForm")

Open in new window


However, if the code is inside the form you want to reference, just use the "Me" keyword, which references the current instance of the form:

Set frm = Me.sub1

Open in new window


Next, a main form has a subform Control, which itself contains the reference to the actual Form object.  Thus:

Set frm = Me.sub1.Form

Open in new window


(sub1 being the name of the control, and .Form referencing the form which is contained in that control).

Sorry for not seeing that the first time I looked at it - hopefully that'll get you squared away.
thanks, however, I now realize this is not doing the trick.  I want to look at both the user status and then each record where me.recordlock = -1, then lock the records, however not lock any records where false.

the above code changes the status of the subform and not each record.  Is there a way to lock the records where the field recordlock = false?


K
To get record-specific information in the subform records, you'll have to use the subform's Current event and examine the current record details.  From within that event, you can set the AllowEdits to the subform to true or false, or, if you wanted, you could loop through the controls and do it on a control-by-control basis (required if you need certain unbound controls to accept input).

If I'm understanding correctly, you're trying to impose subform record-level granularity on your edit status, and that should be done in the subform's code, not the parent form's code.

I don't know enough of the larger picture of how you're handling custom user security to advise of there's a better way than that.

hth
Still having issues.  Note I have tried the code on current of the subform, and now on the Gotfocus of a field.  Still no luck, please help

Does not like the set frm statement:

Public Sub VendorName_GotFocus()
Dim frm As AccessObject
Dim Ctrl As Control
Dim nStatus As String
Set frm = Forms!frmCodingSlip.Sub1.Form


gInvID = Nz(Me.IDInvoice.Value)
nStatus = DLookup("[RecordLock]", "TblInvoice", "[idinvoice] =" _
     & gInvID)
'Dim frm As Form


    If gUser = "User" And nStatus = -1 Then
        For Each Ctrl In Me.Controls
           If Ctrl.ControlType = acSubform Then
              With Ctrl
                 Me.AllowEdits = False
                 Me.AllowDeletions = False
                 Me.AllowAdditions = True
              End With
           End If
        Next Ctrl
    Else
           Me.AllowAdditions = True
           Me.AllowDeletions = True
           Me.AllowEdits = True
    End If

End Sub

Open in new window

still need help.
You have a fair amount of stuff you don't need in your code.  Please go to the Current event of the Subform and try this:

 Private Sub Form_Current()
  If gUser = "user" And Me.RecordLock = -1 Then
    Me.Allow Edits = False
  Else
    Me.Allow Edits = True
  End If
End Sub

Open in new window


Please also disregard the Focus event - it has no use to us under these circumstances.  Remember that Me refers to the current form (the subform, where we are now coding), and setting a form variable is not required.

Also please note that I will be traveling shortly and unable to reply immediately.

Hope this helps,
-jack
No, sorry I wasn't clear I want to lock any record only where the field recordlock = -1
and the User = "Users", however if the recordlock is null or not checked then I want to allow the user to edit that particular record only.

Hence the check for user type and recordlock.  That is why I am confused about the best placement of the code and also allow the adding of a new record, editing the new record until the recordlock is set.

Also, if the user = Admin the allow editing and adding of all records

I have tried the code on the subform gotfocus, also on the afterupdate
User generated image
As you can see above there is 3 records,

1 & 2, needs to still be editable
3 - needs to be locked.

hope this explains things better.

Private Sub Form_AfterUpdate()
    Form_GotFocus
End Sub

Open in new window


Private Sub Form_GotFocus()
Dim Ctrl As Control
Dim nStatus As String

    gInvID = Nz(Me.IDInvoice.Value)
    nStatus = Nz(DLookup("[RecordLock]", "TblInvoice", "[idinvoice] =" _
         & gInvID))

    If gUser = "User" And nStatus = -1 Then
        For Each Ctrl In Me.Controls
              With Ctrl
                 Me.AllowEdits = False
                 Me.AllowDeletions = False
                 Me.AllowAdditions = True
              End With
        Next Ctrl
    ElseIf gUser = "User" And Nz(nStatus) Or nStatus = 0 Then
           For Each Ctrl In Me.Controls
              With Ctrl
                 Me.AllowEdits = False
                 Me.AllowDeletions = False
                 Me.AllowAdditions = True
              End With
        Next Ctrl
    ElseIf gUser = "Admin" Then
        Me.AllowAdditions = True
        Me.AllowDeletions = True
        Me.AllowEdits = True
    End If


End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jack Leach
Jack Leach

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I tried your suggestion, and it works the first time, however, When I select a new Contract Name the user is allow to edit all records on the subform again.

Note the 1st time the user access the system the User must make a selection of a dropdown on a different form.

But the next time the user wishing to move to another Contract Name they have the option to select a new Contract name from the drop down on the MainForm that the subform is on.

what am I missing?
Nevermind, disregard last post, Dah? I forgot to set the Recordlock values on most of the records, since it is newly added feature the data has not be updated.

My mistake.

thanks for all your efforts.
thanks for your time and efforts this did the trick.
Glad to hear you got it squared away.