Karen Schaefer
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:
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
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
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
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")
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
Next, a main form has a subform Control, which itself contains the reference to the actual Form object. Thus:
Set frm = Me.sub1.Form
(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.
ASKER
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
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
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
ASKER
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:
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
ASKER
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:
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
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
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
ASKER
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
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.
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
ASKER
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.
My mistake.
thanks for all your efforts.
ASKER
thanks for your time and efforts this did the trick.
Glad to hear you got it squared away.
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,