Field Level Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nery - 17 Nov 2004 15:14 GMT

I currently have a database with multiple users and I have set up workgroups
for security - they need to log-on. The users are divided in groups: PD, PUR,
PPL, PRM, HR & FIN. I have a field in the main table that lists this group
code. I want the user who logs on only to have access to their group's
records, for example,

User 1 - has access to code PD
User 2 - has access to code FIN

I have MS Access 2000 on NT. Can anyone tell me how to set this up?

Signature

Nery
Mack Trucks

Reply to this Message

Rick B - 17 Nov 2004 15:35 GMT

A user can be a member of more than one group. As a matter of fact, all of
them are, since they are in the "users" group.

There are functions out there to find out if someone is a member of a
particular group, but I don't know if it would be possible to grap "the"
group to which they belong and use that in a query to find records, since
they don't belong to only one group.

Hopefully someone will have some ideas here, but I would guess you would
need a table where all the USERIDs and their "department" are stored, then
use that information to find records for their department.

Rick B

> I currently have a database with multiple users and I have set up workgroups
> for security - they need to log-on. The users are divided in groups: PD, PUR,
[quoted text clipped - 6 lines]
>
> I have MS Access 2000 on NT. Can anyone tell me how to set this up?

Reply to this Message

Nery - 17 Nov 2004 15:45 GMT

Thank you very much, that gives me an idea. But, I'm wondering if there is a
way to put in somewhere in Access some type of criteria like the following:

If User 1 opens the database, they only have access to those records that
have 'PD' in the field [group]. or If User 2 opens the database, they only
have access to those records that have "TC1816" in [DeptCode]

> A user can be a member of more than one group. As a matter of fact, all of
> them are, since they are in the "users" group.
[quoted text clipped - 23 lines]
> >
> > I have MS Access 2000 on NT. Can anyone tell me how to set this up?

Reply to this Message

Rick B - 17 Nov 2004 15:54 GMT

Yes. That is what I recommended. Add a table where you store the user and
the department. In your query, filter so only records for their department
show up.

Rick B

> Thank you very much, that gives me an idea. But, I'm wondering if there is a
> way to put in somewhere in Access some type of criteria like the following:
[quoted text clipped - 30 lines]
> > >
> > > I have MS Access 2000 on NT. Can anyone tell me how to set this up?

Reply to this Message

Nery - 17 Nov 2004 16:18 GMT

thank you. But, I have a concern. I've already created the queries with
macros, etc. for this database; and I will have about 40 users; Will I have
to create 40 different queries for these users? Do I use the function
CurrentUser()? You'll have to excuse me; but I am new to security.

> Yes. That is what I recommended. Add a table where you store the user and
> the department. In your query, filter so only records for their department
[quoted text clipped - 45 lines]
> > > >
> > > > I have MS Access 2000 on NT. Can anyone tell me how to set this up?

Reply to this Message

Rick B - 17 Nov 2004 16:35 GMT

No, one query will work.

I am not sure how to do exactly what you ask here. I know that if you had a
table and only wanted to see records added by a particular user, you'd just
pull the field from the table which contained the user who created that
record. Then, in the criteria, you'd put =currentuser.

Somehow, you will have to pull the "department" for the current user and use
that in the criteria for your query.

Again, I'm not sure exactly how to do this. Hopefully someone will post a
suggestion.

If you pull all the records in your query, then you could apply a filter in
your form. In your form's OnOpen code, you could use a lookup to go out and
grab the current user's department from the table that we built. You could
then apply a filter using that freshly obtained department. This is
probably how I'd do it. All you would need to do is figure out how to grab
the user's department (should be pretty easy) and then use that in a filter
for the form.

Hope that gets you going.

Rick B

> thank you. But, I have a concern. I've already created the queries with
> macros, etc. for this database; and I will have about 40 users; Will I have
[quoted text clipped - 50 lines]
> > > > >
> > > > > I have MS Access 2000 on NT. Can anyone tell me how to set this up?

Reply to this Message

Rick B - 17 Nov 2004 17:09 GMT

Check this out...

Here's a link to a previous discussion on this subject. It includes the code
for a 'IsInGroup' function that will return True if the specified user is in
the specified group ...

http://www.google.com/groups?threadm=0ce301c3fb12%24461d6320%24a101280a%40phx.gbl

Signature

Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

>I have a form that I want to secure to the FIELD LEVEL. My thoughts were
>that I could check the currentuser() membership and if it does not meet a
[quoted text clipped - 6 lines]
>
> thanks

> No, one query will work.
>
[quoted text clipped - 88 lines]
> > > > > > I have MS Access 2000 on NT. Can anyone tell me how to set this
> up?

Reply to this Message