Restrict record view to admin and only user who entered record RRS feed

  • Question

  • I'm creating a database that requires Staff to enter information for clients who call. Staff will enter specific info on the clients. In order to use the database, the staff person will need to login thru a login form. I need  to have  the ID of the person logged into the database via the login form considered the current user.  Once I can do that I need to have the program display only records associated for the user as they login . . . meaning that when John Doe logs in he can only see/edit records assigned to him. He cannot view/edit records associated with Jane Doe.  How can I identify the user from my login form and restrict them to viewing/editing only the clients that they enter.

    Currently, I have  two levels of sign-in, Staff and Admin. I would also like to have Admin level users see all records.  

    I am an a advanced beginner level developer. Please consider that when responding, as I don't know much.

    Thanks, very much!

    Main Code for 2-level user Login:

    Private Sub OK_Click()
    Dim User As String
    Dim UserLevel As Integer
    Dim TempPass As String
    Dim ID As Integer
    Dim UserName As String
    Dim TempID As String
    Dim OwnerID As TempVars

    If IsNull(Me.txtUserName) Then
     MsgBox "Please enter UserName", vbInformation, "Username required"
    ElseIf IsNull(Me.txtPassword) Then
     MsgBox "Please enter Password", vbInformation, "Password required"
     If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPswd = '" & Me.txtPassword.Value & "'"))) Then
     MsgBox "Invalid Username or Password!"
     TempID = Me.txtUserName.Value
     UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
     UserLevel = DLookup("[UserType]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
     TempPass = DLookup("[UserPswd]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
     UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
     If (TempPass = "password") Then
     MsgBox "Please change Password", vbInformation, "New password required"
     DoCmd.OpenForm "frmUserinfo", , , "[UserLogin] = " & UserLogin
     'open different form according to user level
     If UserLevel = 1 Then ' for admin
     DoCmd.OpenForm "Main Menu"
     DoCmd.OpenForm "Tracking"
     End If

     End If
     End If
    End If

    End Sub
    Private Sub Form_Load()
    End Sub

    Private Sub txtPassword_Click()

    End Sub

    Monday, July 30, 2018 3:17 AM

All replies

  • Hmm, this should help (has sample download)...


    There also some additional links at the bottom of the page.  And you might want to take a lok at this as well...


    Gina Whipp<br/> Microsoft MVP 2010-2015 (Access)<br/> Access Tips: www.access-diva.com/tips.html<br/>

    Monday, July 30, 2018 5:56 AM
  • You might like to take a look at SecDemo.zip in my public databases folder at:


    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how 'row level security' can be implemented in a number of contexts.  I'd suggest you look at SecDemoSimple_PseudoLogIn.accdb in the first instance.  For the purposes of the demo it is possible to log in as any user.  In an operational database this would not be possible of course, and access would be controlled by a password in the usual way.

    By default each row is accessible only by the user who entered it, and any user designated as an administrator.  Other users can be given access to a row via the interface, but this could be omitted in your case.  User access to each row is governed by rows in the RowUsers table which models the many-to-many relationship type between the Users and MyTable table in the demo.  The last is analogous to your Clients table.

    Note the 'health warning' on opening the demo.  While you can do a number of things to make unauthorised access as difficult as possible, Access per se can never provide a thoroughly secure environment.

    Other files in the zip archive illustrate 'auto-login' by means of the current user's Windows user name, so once a user has logged into the system, they are automatically logged into the database as that user when opening the file.

    Ken Sheridan, Stafford, England

    • Edited by Ken Sheridan Monday, July 30, 2018 10:38 AM Hyperlink inserted.
    Monday, July 30, 2018 10:37 AM
  • Hello feemo,

    For the issue, show the data for two levels user, it seems to be resolved by opening different form, right?

    So the current issue should be how to show the records associated with specify staff, right?

    Here I will suggest you try to Open the form with args. And then in the Open Event of the opened form, you could try to get the args and use it to change the record source of the form.

    For instance

    DoCmd.OpenForm "Test", acNormal, , , , , UserName

    In the open event,.

    Private Sub Form_Open(Cancel As Integer)
    If Len(Me.OpenArgs) > 0 Then
    ARGS = Me.OpenArgs
    SQL = "SELECT * FROM TEST WHERE [AssignedUserName]='" & ARGS & "'"
    Me.RecordSource = SQL
    End If
    End Sub

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 31, 2018 5:31 AM