Results 1 to 3 of 3
  1. #1
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202

    vba to open to specific record based on user's security

    Good afternoon, I have a form that I want to limit the ability of adding a new record based on a user's security level. If the UserSecurity level is >2, then I do not want them to add new records. If < 2 then they can add new records. The problem I am having is the following. There is a form connected to tblEmployees (Editable). There is a list on that form that is using a junction table so that the list will only show the employees assigned to the supervisor. There is a many to many relationship. Currently, they can click on the list and it will open to that specific employee. Works great. This list is based on a qry and I cannot use the qry as the record source for my form as it will not allow me to add new records (understandably). I cannot set the record to open to new as I don't want all supervisors to add an employee to the db. I have set up UserSecurity to limit the access specific records and editing abilities. I am not sure how to either (1) open to a new record if UserSecurity is <3 and allow adding new records and/or (2) open to either only their employees, or to a blank they cannot input and click on the list to move to a specific employee. I even tried to have a dummy employee that is blank for them to open to with the following vba (know it is a work around, but lost for ideas), which does not work.



    Private Sub Form_Load()
    If Me.txtUserSecurity > 2 Then
    DoCmd.GoToRecord , , [EmployeeID] = 143 'dummy employee
    End If
    End Sub

    Any Ideas. I am reaching for the stars!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure I follow everything but if you store the users security level in a global variable, in the form open event you can use code

    me.allowadditions=gblvarSecurityLevel<2

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Have done something similar to this. While I created a user object and assigned several properties including usrLevel, emlAddress, etc., a simpler method as suggested may suffice. For every form involved, the open event retrieved the user level value and a Select Case block decided what to do: open read only, lock or disable certain controls, open as Data Entry/Allow Additions, etc. Admittedly, it gets to be a lot of code if many forms are involved, but it can provide a lot of possibilities. Not sure if in your case you want Allow Additions or Data Entry or both but in different situations. To see the different options along with a host of other form properties, see
    https://msdn.microsoft.com/en-us/lib.../jj249441.aspx
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Open to a specific record
    By jenkag875 in forum Forms
    Replies: 5
    Last Post: 10-18-2016, 07:16 AM
  2. Replies: 1
    Last Post: 02-06-2016, 05:30 AM
  3. Replies: 4
    Last Post: 04-20-2013, 10:12 AM
  4. Replies: 1
    Last Post: 05-31-2012, 01:01 PM
  5. Open Specific Record
    By halfaguava in forum Forms
    Replies: 1
    Last Post: 06-09-2011, 04:08 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
  •  
Other Forums: Microsoft Office Forums