Results 1 to 8 of 8

Thread: User-level access to specific records

  1. #1
    gbonnaville is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    4

    User-level access to specific records

    Morning all! I am building a db in 2010 which will be used for HR evaluations. It currently has a user login which in the background designates that user's security level (i.e. admin, director, supervisor, etc). The login script works perfectly. The main menu works perfectly.

    My main issue at this point is developing code to make sure that the specific user logged in can only access their own records. For example, this database is for the fire department. The fire department has battalion chiefs and captains. Battalion chiefs work specific shifts and stations. If the day shift battalion chief logs into the db, he should have acces to ONLY the staff on his shift and his station. I do have shift and station fields within the employee information tbl so the information is available. I just have no clue where to put the code (under the OK button of the login screen?). What commands should I use?

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,425
    Filter form and report recordsource when they are opened. Need a field in data that criteria can be applied to.

    Need info in user table to determine user authority. Keep this info handy by setting textboxes on a form that remains open (such as a main menu) to these info during login procedure. Can set boxes not visible. Or use DLookup to grab the info whenever needed.

    Options to apply the criteria:

    1. set parameters in query that refer to the controls on MainMenu as input

    2. use WHERE CONDITION argument of DoCmd.OpenForm (or OpenReport)
    DoCmd.OpenForm "form name", , , "ShiftID=" & Forms!MainMenu.ShiftID & " AND StationID=" & Forms!MainMenu.StationID
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    gbonnaville is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    4
    Thanks for the options! I do have a security setting or access level within a table. For example, admin has complete access to tables, forms, reports, data. Director will have access to all staff members while supervisor or battalion chief will only have access to their staff. I even have user access which will only allow the ability to view information (most likely will not be used by the fire dept).

    So when someone logs in, the db permits the type of access that user is allowed. That portion of the application does work perfectly. I'm just struggling with limiting the records to the correct supervisor. Night shift battalion chiefs do not need access to day shift staff evalutations and vice versa.

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,425
    I don't know your data well enough to give specifics but No. 2 should offer some guidance. You need attributes of the logged-in user to control record filtering. If you know who the user is then you know their station and shift assignment and can filter by those criteria. If director/admins don't have station/shift assignment, then those attributes can be left null and no filter need be applied.

    Review this tutorial for approach No.1: http://datapigtechnologies.com/flash...tomfilter.html

    More from the same site: http://datapigtechnologies.com/AccessMain.htm
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    gbonnaville is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    4
    Thanks for the links! I've uploaded the db for your view to have a better understanding of what I am trying to relay. The first link you provided would not perform what I need for it to do.

    What would be awesome is.....

    user logs onto the db
    the main menu loads - when this loads, only the records that the current user needs access to would be available. The rest would be "hidden" from their view.

    For example, Roger is the chief at station 1 and needs to view all records.
    Sam is chief at station 2 on the day shift so he needs access only to station 2 day shift employees.
    Attached Files Attached Files

  6. #6
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,425
    That project doesn't show implementation of anything I have yet suggested.

    An example of what I am suggesting is the Employee Information button on MainMenu. The Click event code has a variable strCriteria that isn't being populated. Write code to construct a criteria string using the info about the user logged in. This will be a bit complicated but can be done. Or use the query parameter method demonstrated by the tutorial. In either case, first step is to decide how you will 'grab' the user info. Set values of hidden controls on MainMenu during the login procedure? Then the data will be available at any time as long as the MainMenu is always open.

    frmMainMenu button labels (except for Select Performance Review) click events have a HandleButtonClick function reference but the buttons have [Event Procedure]. Why the mix?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    gbonnaville is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    4
    Thanks for the heads up on the HandleButtonClick event. I deleted that function since I have the event procedures coded.

    I kind of understand what you are saying about criteria string. I can also see where placing a "query" button on the main menu might be advantageous as well. The manager would not be able to access any of the main menu functions (except EXIT) until they conduct a query for their employees. When they exit the application, a clear filter function will perform.

    This coding will be totally new for me since I have never done any query programming or VB programming. I'm basically learning as I go.

    Thanks for your help!

  8. #8
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,425
    You could even have the MainMenu form bound to the employees table and open it filtered to the employee who logged in. Simplest way to make all the info available. Don't even have to put the data in textboxes.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 11-18-2013, 12:20 PM
  2. Replies: 7
    Last Post: 02-22-2013, 02:57 PM
  3. Replies: 1
    Last Post: 08-13-2011, 03:44 AM
  4. Replies: 8
    Last Post: 06-30-2010, 09:57 PM
  5. user level security and splitting access db
    By tomClark in forum Security
    Replies: 3
    Last Post: 02-06-2010, 02:28 PM

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
  •  
Tech Forums: Microsoft Office Forums