Solved

lImit Edit of records based on Logon (GetuserName)

Posted on 2014-02-03
14
458 Views
Last Modified: 2014-02-04
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
Comment
Question by:Karen Schaefer
  • 8
  • 6
14 Comments
 
LVL 4

Expert Comment

by:Jack Leach
Comment Utility
>> 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
 

Author Comment

by:Karen Schaefer
Comment Utility
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
 
LVL 4

Expert Comment

by:Jack Leach
Comment Utility
 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
 

Author Comment

by:Karen Schaefer
Comment Utility
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
 
LVL 4

Expert Comment

by:Jack Leach
Comment Utility
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
 

Author Comment

by:Karen Schaefer
Comment Utility
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
 

Author Comment

by:Karen Schaefer
Comment Utility
still need help.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 4

Expert Comment

by:Jack Leach
Comment Utility
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
 

Author Comment

by:Karen Schaefer
Comment Utility
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
 
LVL 4

Accepted Solution

by:
Jack Leach earned 500 total points
Comment Utility
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
 

Author Comment

by:Karen Schaefer
Comment Utility
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
 

Author Comment

by:Karen Schaefer
Comment Utility
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
 

Author Closing Comment

by:Karen Schaefer
Comment Utility
thanks for your time and efforts this did the trick.
0
 
LVL 4

Expert Comment

by:Jack Leach
Comment Utility
Glad to hear you got it squared away.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now