Restrict record view to admin and only user who entered record

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"
 Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
 MsgBox "Please enter Password", vbInformation, "Password required"
 Me.txtPassword.SetFocus
Else
 If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPswd = '" & Me.txtPassword.Value & "'"))) Then
 MsgBox "Invalid Username or Password!"
 Else
 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 & "'")
 DoCmd.Close
 If (TempPass = "password") Then
 MsgBox "Please change Password", vbInformation, "New password required"
 DoCmd.OpenForm "frmUserinfo", , , "[UserLogin] = " & UserLogin
 Else
 'open different form according to user level
 If UserLevel = 1 Then ' for admin
 DoCmd.OpenForm "Main Menu"
 Else
 DoCmd.OpenForm "Tracking"
 End If

 End If
 End If
End If

End Sub
Private Sub Form_Load()
Me.txtUserName.SetFocus
End Sub

Private Sub txtPassword_Click()

End Sub

Answer 1

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

https://www.access-diva.com/f7.html

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

https://www.access-diva.com/f10.html

---

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

Answer 2

You might like to take a look at SecDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

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

Answer 3

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,

Terry

---

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 [e-mail].