Results 1 to 3 of 3

Limit user editing to their own assigned cases?

  1. #1
    Vladivive is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2019
    Posts
    1

    Limit user editing to their own assigned cases?

    Hey Guys, new to this forum and to access. Really wish id realised the value of databases in school. But know, I had to pursue something useless!


    This is probably similar to mlrucci's question on permissions. I have a database setup and working. So I've moved onto security features. A spreadsheet is currently being used (which is awfully made) so I took it up myself to make a more useful tool that can be secure.

    The gist of the DB-Teams containing workers who have clients assigned to them. Various detail outlined within different tables. So I would like for all records to be viewable by anyone with a login for the db. However, information can only be edited by the individual they are assigned to (and those with admin status). I dont want everyone to be able to mess around with the data. I' rather that everyone be accountable for their own upkeep. eventually ill want to track changes but for now, this is the goal

    So user levels, I think, should be: Read only, admin, then whatever you call limiting records to the allocated individual.

    Im assuming VBA is needed for this. I somehow managed to get VBA to work for the login. But have no idea how to use it in general. Any help would be really appreciated guys.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,441
    The approach would be dependent on what type of form you use.

    Each record would have to be associated with a user. You could have a field (e.g.CoordinatorID) for those records. This would be the PK ID of the user and that would link to their permissions level in the user table. If a continuous or datasheet form, load it with that user's records only because to lock records individually may be impractical at best and impossible at worst. Provide a way to swap from one to the other option and make the recordset read only for when it's not their stuff.

    If the form is single record view, you can lock it or all the controls based on whether or not it is their stuff by using the form Current event.

    For admin types:
    The code for handling the above would need 2 pathways - as above if not an admin, or skip all the controlling code if an admin, thus not locking the form or its controls.

    I would enter the levels and associate a numerical value, increment by say 5. Thus lowly user is 1, next is 6, next 11 or something similar. That way, you can govern permissions using operators so that (e.g.) anyone 11 or under gets the permissions of the 1st 2 levels plus their own. You can also compartmentalize by using (>=1 and <= 6) OR (>=16 ) if you get my drift.

    Gotta run. Hope that helps a bit.

    Did you consider what this will entail if user A is covering for user B who is on vacation?
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.

  3. #3
    alansidman's Avatar
    alansidman is offline Life @ 7100'
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,436
    Look at this link and see if any of it will work for you.

    https://www.mrexcel.com/board/thread...system.248191/

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

Similar Threads

  1. Need Help Editing Drop Down on User Form
    By Spacle in forum Forms
    Replies: 5
    Last Post: 11-05-2018, 07:08 PM
  2. Replies: 3
    Last Post: 06-23-2017, 07:03 AM
  3. Replies: 13
    Last Post: 11-17-2013, 03:33 PM
  4. Limit user to one selection
    By burrina in forum Forms
    Replies: 12
    Last Post: 01-12-2013, 03:22 AM
  5. Filter by Records Assigned To User
    By skankingpigeon in forum Access
    Replies: 2
    Last Post: 07-23-2012, 03:35 PM

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