allow insert but not delete

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PHisaw - 24 May 2007 19:40 GMT

Hi,

I'm hoping someone will help with permissions problem. I want to allow one
user in the "Read Only Users" to enter data to a form. I don't want them to
delete the data, just enter data to an existing record.

When I set permissions to the query backing the form to read & insert, it
doesn't allow any entry to any field. If I set permissions to read, insert
and update, it will let user insert and update, but also delete - which I
want to prevent.

Maybe I'm not understanding permissions as well as I thought.
If anyone has any ideas, I would appreciate hearing them.
Thanks,
Pam

Reply to this Message

Joan Wild - 24 May 2007 19:57 GMT

> I'm hoping someone will help with permissions problem. I want to allow one
> user in the "Read Only Users" to enter data to a form. I don't want them to
[quoted text clipped - 4 lines]
> and update, it will let user insert and update, but also delete - which I
> want to prevent.

Insert means add new records.
Update means to edit existing records.
Delete means to delete records.

I'm not certain, but do you mean you want them to be able to edit fields in existing records, but not allow them to clear out (delete) a field's contents? I don't think there's a way around this, because that's 'updating' the value to null, and you need to allow updates.

You could make the field required in the underlying table; that way they can't leave it as null. However, they can just fill it with garbage too.

Signature

Joan Wild
Microsoft Access MVP

Reply to this Message

PHisaw - 24 May 2007 20:19 GMT

Joan,
Thank you for the prompt reply. And yes, I want this particular user to
update data but have no way to accidentally delete it. Is there any way to
allow access to just one particular field on a form?
Pam

> > I'm hoping someone will help with permissions problem. I want to allow one
> > user in the "Read Only Users" to enter data to a form. I don't want them to
[quoted text clipped - 12 lines]
>
> You could make the field required in the underlying table; that way they can't leave it as null. However, they can just fill it with garbage too.

Reply to this Message

Joan Wild - 24 May 2007 21:19 GMT

Yes, you can set the locked property of a field when you open the form. Probably the best approach is to check if the current user is a member of a particular group, and then set the locked property accordingly. You'll find a function in the security FAQ you can use to determine if the user is a member of a group.
http://support.microsoft.com/?id=207793
In the form's open event...
If faq_IsUserInGroup("NameOfRestrictedGroup",CurrentUser) then
Me.txtNameOfControl.locked = True
Me.txtNameOfAnotherControl.Locked = true
etc
Else
Me.txtNameOfControl.locked = false
Me.txtNameOfAnotherControl.Locked = false
End If

You could use the visible property rather than locked, if you'd like the control to not even appear.

Signature

Joan Wild
Microsoft Access MVP

> Joan,
> Thank you for the prompt reply. And yes, I want this particular user to
[quoted text clipped - 18 lines]
>>
>> You could make the field required in the underlying table; that way they can't leave it as null. However, they can just fill it with garbage too.

Reply to this Message

PHisaw - 24 May 2007 22:06 GMT

Hi Joan,

That sounds great - but please bear with me and hopefully, my last question.
This particular user is sometimes a bit careless and is in the Read Only
User Group. Can I give him permission to this one form and then lock the
entire form except for one field he needs to update? Is there a way to lock
the entire form with code - there are a lot of fields on this form?
Thanks so much for your help!
Pam

> Yes, you can set the locked property of a field when you open the form. Probably the best approach is to check if the current user is a member of a particular group, and then set the locked property accordingly. You'll find a function in the security FAQ you can use to determine if the user is a member of a group.
> http://support.microsoft.com/?id=207793
[quoted text clipped - 32 lines]
> >>
> >> You could make the field required in the underlying table; that way they can't leave it as null. However, they can just fill it with garbage too.

Reply to this Message

Joan Wild - 24 May 2007 22:23 GMT

You'd need to loop through the controls on the form
If faq_IsUserInGroup("NameOfRestrictedGroup",CurrentUser) then
Dim ctl As Control
For Each ctl In Me.Controls
If Not ctl.Name = WhateverControlHeNeedsToEdit Then
ctl.Locked = True
end If
Next ctl
Else
'do nothing
End If

Signature

Joan Wild
Microsoft Access MVP

> Hi Joan,
>
[quoted text clipped - 42 lines]
>> >>
>> >> You could make the field required in the underlying table; that way they can't leave it as null. However, they can just fill it with garbage too.

Reply to this Message

PHisaw - 31 May 2007 21:39 GMT

Joan,

Thanks for your help. I gave permission to the query that backed the
subform and then used code as follows and it works. I haven't used "current
user" before, but I can see how it may be useful for other applications.
If CurrentUser = "name" then
Me.fieldname.locked = true
End if

Thanks again for your help
Pam

> You'd need to loop through the controls on the form
> If faq_IsUserInGroup("NameOfRestrictedGroup",CurrentUser) then
[quoted text clipped - 54 lines]
> >> >>
> >> >> You could make the field required in the underlying table; that way they can't leave it as null. However, they can just fill it with garbage too.

Reply to this Message