How to link and display task based on user login

[xaxier]

Hi guys,

I am new to access, pardon me if it sound a bit silly or ridiculous . Anyway, i have created a login form with a user database table. I have also created some task assign to each user on the another table called work_database. My question is:

1) let said user A login, how do i enable user A to only have access and update his task alone based on work_database? In other word, user A will not have access and will not be able to view or update other users task.

 

[Subcancel]

I don't know how your tables are setup but here you go.

You can add a field to you table work_database that tell which user is assign to that task by using your user table PK and then setting up a relationship between the two fields.

 

[xaxier]

Hi Subcancel

Thanks a lot for the advice/guidance.
I had setup relationship between the table work_database and user_database but it is not showing what i wanted

I wish to setup the form to this format, once user A login ( click button "login') , he will be directed to a form whereby it will display his task and work to be done based on work_database. Any idea on how this can be achieve? I had already created the login form with the login button but when i login using User B,user C or user D credential, it only show me user A info which is the first line in my user_database.

Thanks in advance.

 

[Subcancel]

First of I would make general query to pull all the field you need plus the FK one. Then Build a form to display it how you want it look and use the your new query as your control source. This form would include all task for everyone btw.

Now that you have a general form and query you can now filter that based on who is logging in.

To do this you will need to do a bit of coding behind your button on your login screen.

You are going to want to pull the PK or ID for the person who is logging in; an easy way to so do this by combo box or list box I think.

Now for you button code behind.

Private Sub Command_Click()

DoCmd.OpenForm "<FormNameThatWillOpen>", , , "ID=" & Me.[ID], acFormEdit

End Sub

"ID=" & Me.[ID] is a Where condition, This will allow you to filter by user (I used ID it might be something different for you)

acFormEdit will allow the user to add/edit/ on the form you can replace with acFormReadOnly if you only want them to view it.

After that the form will open based on what user is logged on and only show there tasks.

This is pretty general.. but I hope it helps

-Sub

 

[DCrake]

One thing you have to bear in mind is staff turnover. What happens if record 1 belongs to user A and user A leaves? No one will be able to view it so you will need some sort of override or a wizard to re assign records to other users.

 

[The_Doc_Man]

I approach this slightly differently. I use role-based rights where the user is in a table along with roles and a few check-boxes. I use the Environ("Username") call to see who the O/S thinks this person really is. Then I look up required info in a table based on the login ID as reported by Windows. Make a subroutine to do the call to read the person's information record. Pick out the parts of interest.

Note: In my case, it will work best if the users' tasks are all form-based AND those users do not see the database window. That's a different topic, "Hide Database Window" - which you can search in both Access Help and this forum to see how it is done.

The ideal place to put this check on a form-by-form basis is to put this subroutine in the Form_OnOpen routine for every form. Get the role or, if you use it this way, the yes/no flags. (I use role but I know other views can exist.) If the person has an acceptable role, you just continue. If not, you force a DoCmd.Close on the form. Maybe, if you were feeling nice, you could implement a message box to say "You are not allowed here" (with a vbOkOnly selection for the message box style) - THEN you dump them unceremoniously.

 

[DCrake]

DOC
Tha's exactly how I do it (RBAC). At login the users permissions are passed to global variables, such blnCanDelete = False, false being the value taken from the permissions table as to whether this user can delete jobs. So when the user opens the jobs form the event reads the blnCanDelete value such as

Me.CmdDelete.Enabled = blnCanDelete

if for some reason blnCanDelete has not been set then it will pessamistically return False, which is erring on the side of caution.

 

[xaxier]

Wow, I did not expect to get so many replies. Thanks a lot for the help offered. When come to programming, I am really hopeless. Anyway, on Doc and Dcrake suggestions..I am not really sure how to proceed, it sound interesting but i am basically clueless..

On the other note, I tried Subcancel suggestion but run into problem with the coding. It give out "Runtime error 2467, the expression you entered refers to an object that is closed or doesn't exist."

Is something wrong with my value assignment?

 

[Subcancel]

My stuff was just an example.. your going to have to replace the "ID= " & Me.[ID] to the field and table you want to match up based on your form and tables.

 

[The_Doc_Man]

The trick involves knowing when events fire. When you do a Form Open, you have many events but only one can be canceled. For form opening, you have

Open(Cancel) --> Load --> Resize --> Activate --> Current

If you want to stop someone from opening a form based on role, the only place that makes sense is to put VBA code in Form_Open code to open your recordset of users. Use a Dynaset so you can do a "FindFirst" method on it to look up that user. If you get .NOMATCH=TRUE, you have no such user (assuming that you have access to the USERNAME environmental variable.) Once you have that information, you can make all sorts of rational decisions about what a person could do.

I'm pretty sure you cannot do ANY of this without VBA. Therefore, xaxier, before you go very far with this, be sure you are comfortable with VBA and the event structure. Unfortunately, as I am on a U.S. Government site, I cannot post code without almost literally an act of Congress, so I cannot give you samples.

 

[pkstormy]

Perhaps this example will be helpful: http://www.dbforums.com/6332819-post68.html

I found it's much easier to automatically grab the user's windows loginID via a simple function which can then be utilized anywhere throughout the program - ie. getuser(). You can then simply write code to match the getuser() against a 'user' type table with LoginID's to 'branch' to whatever form or make fields/buttons appear/not appear.

For example:
If getuser() = "pkstormy" then me.cmdWriteData.visible = true else me.cmdWriteData.visible = false

or write a function/code to return the 'securitylevel' of the loginID in the user table (ex: =dlookup("[SecurityLevel]","UsersTable","[LoginID] = '" & getuser() & "'")

And your users will be much happier without needing to 'login' to the database.

You can then also create an 'EnteredBy' field in your data tables and simply set the default value of this field on a form to =getuser() and always know who entered that record. It saves a lot of 'work-around' type coding.