Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

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

0
Karen Schaefer
Asked:
Karen Schaefer
  • 8
  • 6
1 Solution
 
Jack LeachCommented:
>> 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,
0
 
Karen SchaeferAuthor Commented:
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
0
 
Jack LeachCommented:
 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.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Karen SchaeferAuthor Commented:
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
0
 
Jack LeachCommented:
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
0
 
Karen SchaeferAuthor Commented:
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

0
 
Karen SchaeferAuthor Commented:
still need help.
0
 
Jack LeachCommented:
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
0
 
Karen SchaeferAuthor Commented:
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
SampleScreenshot
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

0
 
Jack LeachCommented:
I'm fairly sure I understand what you're after, and I'm fairly sure that the Current event is what you need.  The Current event will fire every time the user navigates to a new record.  So, if you have a list with three records in it, and they click on the second one, the Current event will fire, check the status, and either lock or unlock the form for that record.  When they click a different record, the Current event will fire again, repeating the processes.  All of this happens far faster than the user will ever be able to start editing things in the record.

It is simply not possible to set different lock states for all three records at once, so we are forced to do so as the user goes into each individual record.  This is exactly what the Current even is supposed to be used for.  The Focus event is for something else entirely and should be completely disregarded for this task.

Were you able to try the short code I posted in my last reply?  I really do believe it will do what you need.  I'll make one minor adjustment based on the fact that RecordLock might be null:

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

Open in new window


Please try that and let me know if it works.
0
 
Karen SchaeferAuthor Commented:
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?
0
 
Karen SchaeferAuthor Commented:
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.
0
 
Karen SchaeferAuthor Commented:
thanks for your time and efforts this did the trick.
0
 
Jack LeachCommented:
Glad to hear you got it squared away.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now