Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107

    Restricting User Access

    Hi,
    I am looking to restrict users to accessing certain forms, tables and reports.
    I'm not sure what the best way to go by doing this.

    Basically there will be two user logins (maybe a 3rd for the Admin).


    the 1st User logins will be for the Manager and Supervisor (same login and password). They will have full access to the Data etc.

    The 2nd Set of user logins are for the rest of the staff. They will all use the same login and password. They will only be able to access one button on the main menu which is a form. Restrictions on certain fields will also be on the Form.

    - I'm guessing I need to
    A) Create a form for the logins
    B) Hide and lock fields from them in the property sheet. And either disable all the buttons except for the one on the main form or Just Hide them. Or is it more technical than this?

    If there is alot of VBA coding, which i'm guessing there is especially for the Logins. What do I put in there? Do I need A workgroup to put my users in?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Common topic. Review this for starters http://forums.aspfree.com/microsoft-...7t-406737.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    Thanks June.
    I was browsing around yesterday looking for reference material etc and stumbled across a forum/thread which I found uselful etc. Noticed its the same thread you recommended as well in an earlier stage.

    http://www.mrexcel.com/forum/showthread.php?t=248191

    I've tried the code given and seems to work fine. however I want it to use the Main_Menu form (with buttons which Macro to a provided form/report etc.) that I created instead of the listbox provided. I'm guessing this is possible...though just not sure where to start.
    You able to help me?

    Thanks.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Maybe. I don't really understand "use the Main_Menu form (with buttons which Macro to a provided form/report etc.) that I created instead of the listbox provided".

    I haven't studied that mrexcel thread in depth.

    What listbox? What do you want to do with the buttons - manage their availability to users?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    I have a Main Menu with buttons on them which direct to certain forms, reports etc. Think its on the DB i sent you a couple of days ago which you were helping me with - Update queries etc.

    With the coding provided from the Mrexel thread - a listbox is made and brings ups the form/report that the user has availability to. Yes I would like to remove the listbox and manage the availability to the users via the buttons on my Main Menu form.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do you want to set buttons to not be available to some users? Or do you want the buttons to open forms with restrictions determined by who the user is? How many users? Will limitations be by user or by the group they work in (accounting, marketing) or whether they are supervisor? In other words, how is the table of users set up? How are privileges assigned? What criteria do you want to use to control the limitations?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    Right ok - There are two access levels. one with full access i.e Admin, Manager and Supervisor.

    The other level is for the rest of the staff with restrictions
    - set certain buttons to not be available (either hidden or disabled) and
    - the ones which are available to open forms: will be restricted to certain access. (some fields will be disabled/locked)

    The ribbons etc will be disabled as well

    Not sure if i need a read only access level if no user name/password is available.?!?

    At the moment I have a staff table with "logins" and "permissions"
    The logins contain the users window logins and
    the permissions is "Admin" and "Edit"

    Admin level - has full access as mentioned earlier. (the coding is a level 3)
    Edit level - has limited access (the coding is a level 2)
    else read only level (level 1)

    This is how the coding is set up for the permissions
    sPermit = GetPermission(Me.txtUser)
    End If
    Select Case sPermit
    Case "Edit"
    iAccess = 2
    Case "Admin"
    iAccess = 3
    Case Else
    iAccess = 1

    there will roughly be 10 users using this DB

    Here is the full coding in my Main_Menu form. The listbox is at the end. This is the one I'm looking to remove and change with new coding

    Private Sub Form_Load()
    Dim sPermit As String
    Dim iAccess As Integer
    Dim Ctl As Access.Control
    Me.txtUser = Environ("username")
    If IsNothing(Me.txtUser) Then
    sPermit = "ReadOnly"
    Else
    sPermit = GetPermission(Me.txtUser)
    End If
    Select Case sPermit
    Case "Edit"
    iAccess = 2
    Case "Admin"
    iAccess = 3
    Case Else
    iAccess = 1
    End Select
    Me.txtLevel = iAccess
    Me.lstMenu.Requery
    End Sub

    Private Function GetPermission(sUser As String)
    If (IsNothing(DLookup("Permissions", "tblStaff", "Login='" & Forms!Main_Menu!txtUser & "'"))) Then
    GetPermission = "ReadOnly"
    Else
    GetPermission = DLookup("Permissions", "tblStaff", "Login='" & Forms!Main_Menu!txtUser & "'")
    End If
    End Function

    Public Function IsNothing(ByVal varValueToTest) As Integer
    Dim intSuccess As Integer
    On Error GoTo IsNothing_Err
    IsNothing = True
    Select Case VarType(varValueToTest)
    Case 0 ' Empty
    GoTo IsNothing_Exit
    Case 1 ' Null
    GoTo IsNothing_Exit
    Case 2, 3, 4, 5, 6 ' Integer, Long, Single, Double, Currency
    If varValueToTest <> 0 Then IsNothing = False
    Case 7 ' Date / Time
    IsNothing = False
    Case 8 ' String
    If (Len(varValueToTest) <> 0 And varValueToTest <> " ") Then IsNothing = False
    End Select

    IsNothing_Exit:
    On Error GoTo 0
    Exit Function
    IsNothing_Err:
    IsNothing = True
    Resume IsNothing_Exit
    End Function
    Private Sub lstMenu_Click()
    Dim sForm As String, sType As String
    sForm = Me.lstMenu.Column(1)
    sType = Me.lstMenu.Column(2)
    Select Case sType
    Case "Form"
    DoCmd.OpenForm sForm
    Case "Report"
    DoCmd.OpenReport sForm, acViewPreview
    End Select
    End Sub

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is iAccess - a global variable or a textbox on MainMenu? Once you have the user permission code determined from login, where is that saved so that you can continue to reference it?

    As an example, Button1 is to open a form. So code maybe like:

    Select Case iAccess
    Case 1
    DoCmd.OpenForm "formname", , , , acFormReadOnly
    Case 2
    DoCmd.OpenForm "formname", , , , acFormEdit
    Case 3
    DoCmd.OpenForm "formname"
    End Select

    Then code in the Open event of form could manage what controls are available to user. Again, another Select Case.

    This could get very complicated. I have this sort of management only for one little form in my project. Limitations are put in place if I am not the user.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    iAccess is possibly a global variable. Defintely not a textbox or anything i created. The logins are saved in the tblstaff with their permissions next to their names.

    Login Permission
    i.e Mark.brown Edit

    If i understand this right:
    the code your presented

    Select Case iAccess
    Case 1
    DoCmd.OpenForm "formname", , , , acFormReadOnly
    Case 2
    DoCmd.OpenForm "formname", , , , acFormEdit
    Case 3
    DoCmd.OpenFrom "formname"
    Select End

    does it go in the on click build event of the button? or does it go after all the code in the Main_Menu. and if so..don't I need to reference my button somewhere?


    I assigned the code to a button.
    it's not liking the "Select End" bit

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I suggest it as code for the button Click event.


    Sorry, 'End Select'
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    got a compile error

    Case 3
    DoCmd.OpenFrom "ASE_Units_form"

    It highlights the openform and says method or data member not found.




    P.s
    I want the user group with restrictions to have access to 1 form, of which has restrictions on that. The rest of the buttons to other forms and reports are disabled.
    The other group has access to everything

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Probably because of another of my typos. It show OpenFrom instead of OpenForm
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    lol, I didn't even notice that :/
    my bad.

    I'll try again

    *Edit
    No nothing is happening. :/
    any other suggestions?

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Nothing happens? You have [Event Procedure] in the button Click event property? Step debug - is the procedure even executing?

    Show your complete code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    WayneSteenkamp is offline Competent Performer
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    107
    I originally had a macro event for the button.
    changed it to event procedure and put in the code you suggested.

    Private Sub ASE_Units_Click()
    Select Case iAccess
    Case 1
    DoCmd.OpenForm "ASE_Units_form", , , , acFormReadOnly
    Case 2
    DoCmd.OpenForm "ASE_Units_form", , , , acFormEdit
    Case 3
    DoCmd.OpenForm "ASE_Units_form"
    End Select
    End Sub

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 11-18-2013, 02:20 PM
  2. restricting access until logged in
    By gbmarlysis in forum Security
    Replies: 1
    Last Post: 02-17-2012, 06:43 PM
  3. Restricting Access Form Textbox to Text only.
    By Accession in forum Programming
    Replies: 4
    Last Post: 09-30-2011, 01:51 PM
  4. access restricting users
    By jmskms in forum Access
    Replies: 2
    Last Post: 03-05-2010, 08:39 AM
  5. Replies: 0
    Last Post: 03-26-2008, 08:46 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