Solved

lImit Edit of records based on Logon (GetuserName)

Posted on 2014-02-03
14
462 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
ID: 39831918
>> 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
ID: 39832924
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
ID: 39833061
 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
ID: 39833218
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
ID: 39833234
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
ID: 39833334
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
ID: 39833613
still need help.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 4

Expert Comment

by:Jack Leach
ID: 39833644
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
ID: 39833677
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
ID: 39833758
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
ID: 39833830
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
ID: 39833890
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
ID: 39833891
thanks for your time and efforts this did the trick.
0
 
LVL 4

Expert Comment

by:Jack Leach
ID: 39834048
Glad to hear you got it squared away.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

920 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

16 Experts available now in Live!

Get 1:1 Help Now