how to Restrict so only authors of record can modify record

T

TxBlueEyes

Hi All you wonderful Gurus,
I have a database being used by about 30 users... they all have an MDE Front
End on their PC, and the data is in a BE on our server.... However, I need to
know how I can make sure that only the user who entered the record is able to
make any modifications, right now they are on the honor system, which is not
a very sound security.... Any Suggestions.... I know about Passwords/Login
and Workgroups, however, I don't know how to secure at the user/record
level... thanks TxBlueEyes
 
A

Allen Browne

Access does not provide record-level security, so you need to roll your own.

Here's an example that records the user's name (from Windows), and locks out
changes from other users, even at the field-level:
Field-level Permissions in Microsoft Access
at:
http://allenbrowne.com/ser-55.html
 
B

BruceM

With user-level security in place you could do something like this. When a
new record is started, add the CurrentUser to a field (StartedBy). You
could use the Before Insert event for this. Then in the form's Current
event:

If Me.StartedBy = CurrentUser Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If

I have to admit I have not used this exact approach. In the project where I
locked things down except for the CurrentUser there are several parts of a
record, each on its own tab and each filled out by a different person, so I
loop through the controls for each tab. In retrospect I would have done
better to have each section's data in a separate table, but that will have
to wait for the next serious reworking of the project.
Anyhow, I expect the suggested approach will work. You may want to allow
for a Full Permissions group to edit the record:

If Me.StartedBy = CurrentUser Or _
UserGroup("Full Permissions") Then
Me.AllowEdits = True
Else
Me.AllowEdits = False
End If
 
B

BruceM

Some time elapsed between my starting my initial reply and finishing it. I
should have checked to see if there was a response before I posted. Allen's
sample database is clearly more flexible and versatile than my approach.
 
T

TxBlueEyes

Hi there Allen,

First, You have really helped me and many other Access guru want-ta-bes....
I was wondering in the code you have created, how can I do it simply at the
record level, not the field level, so I don't have select every field on the
record.... The Record contains, over 20 fields, including memo fields.... so
I just wanted only the author of the record itsellf be the one to see and
edit the record.... Maybe I missed something in your code, but it seemed to
have be set at the field level, not the record level.... Meanwhile, I will
keep working with the code and make some more tests to see if I can figure
that out myself. Thanks again, for all your assistance...Tex
 
A

Allen Browne

Use Form_BeforeUpdate to record the Windows user name of the user who
created the record.

Use Form_Current to lock all the fields if it's not the same user who
created the record.

Note that LockControls() locks all the fields by default. You don't need to
specify them all, i.e. you only need to list the exceptions (controls that
you don't want locked) if there are any.

If there are no unbound controls on the form that the user needs to edit,
and none for the exception list, and no subforms, instead of calling
LockControls() you could just set the form's AllowEdits (and AllowDeletions)
in Form_Current.
 
T

TxBlueEyes

Hi Allen,

Kewl Beans.... I have tested on my own,,,,, the real test will be in the
office on Monday, I will have a couple test the revisions to the new Form,
and see if it works... I will let you know how I did.... Allen you should
offer some online classes, I know I'd pay to attend... Tex
 
Top