Form Advice Please

[Golfinray]

I have built a form (a menu, really) for our construction managers. By
pushing a command button area A manager can access his 4 forms, same for area
B, C, D.
Is there a way that each manager can only see his files but the big boss can
see all of them. I would like to do this with a login for each manager and
for the big guy. My question is, can this be done right on the menu or in the
forms or does it have to be done through the security setup. Thanks!!!

 

[Klatuu]

First, if you have separate forms for each user and separate tables for each
user, you need to consider redesigning your system. You are creating a
maintenance nightmare and reducing the usability of your system.

All users should use the same forms. There should be only one copy of a
table. In that table, you should have a field that identifes the user. In
your menu form, you can capture who is logged in to the system and filter the
data they can see based on the user.

Here is a link to an API that you can use to determine who is logged on:

http://www.mvps.org/access/api/api0008.htm

 

[Golfinray]

I could easily redesign like you suggested. If I put my 16 tables into one
and use one form for all, how would I designate who gets what? I don't quite
understand that concept. How do you designate what fields of a table each
user gets? Then how do you set the form to output for that user. I'm not
quite getting the drift. If you could point me in the right direction to find
what I need? Thanks!!!!!

 

[Golfinray]

By the way, there is only one copy of each table and all tables have unique
information. Thanks!

 

[Klatuu]

When you say one copy of a table, let me be sure I understand. If you have a
table with identical structure (same fields and data types) for each user, I
mean you should combine all the users' tables into one with an additional
field that identifies the user to which the data belongs.

If you use the API I posted the link to, you can assign a module level
variable with the value returned by the API. Then use the OpenForm method to
filter the records for the user using the WhereCondition argument:

Docmd.OpenForm "MyForm", , , "[UserID] = '" & Me.strUserName & "'"