Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    3

    Unanswered: Show records based on User access

    Hi, I have set up a DB called "Task Manager" which has 2 linked tables -
    a. "Tasks" and b. "Actions". (linked one to many)

    I have a Log In form which logs Users into a parent form "Tasks" (Which can show Tasks through criteria specified in a search bar on the form).

    I only need 2 types of User Access. 0= regular user and 1= Supervision. I have a (Byte format) field in my "Tasks" table called "Security" The idea being that if this field is set to 1 then only Supervision level users (and the "Task Owner" of a specific record) can see All records set to 0 and 1 in the "security" records and if set to 0 then regular users can see only records set to 0 in the "security" field.

    Of course the obvious complication I hinted at is that in the "Task" table/form I have the field "TaskOwner". The "TaskOwner" could be user and may have a Task that they want to be viewed only between themselves and a supervisor despite they may not have Supervision access?

    I'm guessing I will have to have a field in the "Tasks" Form called "User" therefore providing a link between my Log in "Users" Table and the "Tasks" Form? But to be honest I'm lost on this one??

    Can anybody show me the code and links needed to set this up from my "Login" table/Form to the "Tasks" table/Form?

    Thanks,

    Lewo
    Last edited by Lewo; 08-20-12 at 10:11. Reason: Not detailed correct

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    You could filter the record set of the form when the user logs in. After you determine the level of the user add something like this:

    RecordSource = "select * from Tasks where Security = 0"

    Now the form will only show records that a regular use could see. If they were logging in as a supervisor you could change the 0 to a 1 and show everything.

    Keep in mind however that this is MS Access and what you are setting up is only a casual security. There are many ways to circumvent your efforts. Depending on your user base it could still be of value, just don't go into it believing that the database is completely secure!

    Also, if you are relying on testing for a 0 or 1, add a constraint to that field that ensures that a 0 or 1 will be entered.

    Steve

  3. #3
    Join Date
    Aug 2012
    Posts
    3

    That's Close

    Thanks for the reply Steve.

    Unfortunately it doesn't give access for a "Task owner" to view the record when that owner has regular user security.

  4. #4
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Yup, I ready right over that part, sorry!
    Of course the obvious complication I hinted at is that in the "Task" table/form I have the field "TaskOwner". The "TaskOwner" could be user and may have a Task that they want to be viewed only between themselves and a supervisor despite they may not have Supervision access?

    I'm guessing I will have to have a field in the "Tasks" Form called "User" therefore providing a link between my Log in "Users" Table and the "Tasks" Form? But to be honest I'm lost on this one??
    I'm a bit confused though. Paragraph one says you have a TaskOwner field in the Task table but paragraph two is asking if you should have one. To indicate a task owner you will need a field in the task table, and as you alluded to it should be linked with your user table.

    Then, you just update the sql to check both fields:
    Code:
    RecordSource = "select * from Tasks where Security = 0 or TaskOwner = 123"
    Of course, since you need to pull these values from your login form code you will have to do something more like this:

    Code:
    Dim userType As Integer
    Dim userId As Integer
    Dim sql As String
    
    'Set these two values during the login
    userType = 0
    userId = 123
    
    sql = "select * from Tasks where Security = " & userType & " or TaskOwner = " & userId
    RecordSource = sql
    Obviously, that needs to be cleaned up to fit your exact scenario but it should give you the idea

    Steve

  5. #5
    Join Date
    Aug 2012
    Posts
    3
    Thanks Steve, I'll give it a try. I did try initially just setting the record source to 0 which does work, But as soon as I put criteria in other fields such as "taskType" the security(1) records also returned.

    I'm away for a few days now but I'll let you know how it pans out.

    pete

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by Lewo View Post
    Thanks Steve, I'll give it a try. I did try initially just setting the record source to 0 which does work, But as soon as I put criteria in other fields such as "taskType" the security(1) records also returned.

    I'm away for a few days now but I'll let you know how it pans out.

    pete
    I've generally prefer to change the record source rather than play with adding criteria. I just find it easier to manage, or maybe that's just how I learned!

    Let me know how it works and if you get stuck further along

    Steve

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •