Design help - multiusers, 1 table, selected records per user

[Doug F.]

My challenge boils down to this: I have multiple-users going after 1 table,
each user has 1 or many of their own records which they can read and update;
they can only look at other users records; e.g. user1 can read/update records
flagged a or b or c while user2 can read/update records flagged d or e or f
but can only read records flagged a or b or c.

Currently there is no user login/password required. In another database I
have developed a modal form for userID & password input which is checked
against a lookup table, the userID is stored in a Global variable for later
access in an audit trail. Might work?

Any suggestions for the simplest solution. Thanks.

 

[Jeff C]

Create a query that defines the records for each user. Each query then
becomes the record source for each users form right?

now build a table with a field for the username and a field for the name of
the form assigned each user.

build a new startup form with txt boxes for the identifying user information
and in the OnOpen Event buid code that matches the value in the txtboxUser to
the user field in the table

then DoCmd. OpenForm [form field]

referencing the the value in your user table that names the form for the
particular User.

Then close the startup form.

In the Tools Startup setup name your Startup form to open with the program.

Once your user goes through your ID input, if referenced correctly in your
startup form their ID will fill the txtbox on the startup form and match up
with a value in your UserID Table, the correct form will open for them and
the startup form will close.

 

[Doug F.]

Hi Jeff C,
Every user can read/look at every record, they can only update their own
identified records. Also, having a form for each user is not doable as the
number of users is unknown and will grow, a change to this form would require
too many forms to be changed.
I'll have a table of userID and record type they can update eg A,B, and in
the forms BeforeUpdate Event , I'll prompt for userID & password, then access
the table to make sure they're trying to update a A,B, type record else kick
them out. I'll save the userID in a global variable so that I only need to
prompt them once per session.
Thanks,
Doug

--
Doug F.

Create a query that defines the records for each user. Each query then
becomes the record source for each users form right?

now build a table with a field for the username and a field for the name of
the form assigned each user.

build a new startup form with txt boxes for the identifying user information
and in the OnOpen Event buid code that matches the value in the txtboxUser to
the user field in the table

then DoCmd. OpenForm [form field]

referencing the the value in your user table that names the form for the
particular User.

Then close the startup form.

In the Tools Startup setup name your Startup form to open with the program.

Once your user goes through your ID input, if referenced correctly in your
startup form their ID will fill the txtbox on the startup form and match up
with a value in your UserID Table, the correct form will open for them and
the startup form will close.
--
Jeff C
Live Well .. Be Happy In All You Do

My challenge boils down to this: I have multiple-users going after 1 table,
each user has 1 or many of their own records which they can read and update;
they can only look at other users records; e.g. user1 can read/update records
flagged a or b or c while user2 can read/update records flagged d or e or f
but can only read records flagged a or b or c.

Currently there is no user login/password required. In another database I
have developed a modal form for userID & password input which is checked
against a lookup table, the userID is stored in a Global variable for later
access in an audit trail. Might work?

Any suggestions for the simplest solution. Thanks.

 

[John Nurick]

Hi Doug,

You can get the user name from the network with the code at
http://www.mvps.org/access/api/api0008.htm

For the rest, one approach is to put code in the form's Current event
procedure (which runs before the form displays the record) to do the
following:

1) Get the username and look up the record types they're allowed to see
or update
2) Compare these with the type of the current record
3) Set the form's AllowEdits property accordingly (or maybe lock and
unlock individual controls). You can also change the Visible property of
individual controls to hide some data.

To make this provide any actual security (as against just preventing
accidental changes) you'll need to deny users easy access to the tables
and queries, by hiding the database window and disabling the "press
Shift while opening" bypass key. But knowledgeable users will still be
able to edit any record in the table by linking to it from another
database.

Even if you take the next step and implement Access user-level security,
the Jet database engine used in most Access databases wasn't designed to
provide row-level access control. One can work round this to some
extent, but strong security requires a server database such as one of
the editions of SQL Sever (Access can still be used for the front end).

 

[Doug F.]

Thanks John.
Using the OnCurrent Event and AllowEdits Property is cleaner. Points taken
on security, the Database Window is hidden and we may leave it at that.
Doug