Question Login to see own records but admin to see all records

[josephbupe]

Hi,

I have a user login code am using for users to login and only access their records in a subform according to their user ID and access level (user or admin). On the main form I have a user name field, the password field and two other fields: one to hold userID and the other to hold access level.

I also want if the person logged in has "Admin" access level the subform should return records from all users. For now what is happening is that even when I assign myself with "Admin" access level, the subform is not returning all records from other users, but my own records only. The original demo where I found the code worked well but I cannot find it and I am totally lost on this.

I hope someone here will no what I am driving at and subsequently help me.

Here is the code I have so far:

Private Sub cmdLogin_Click()
Me.Hold_User_ID = Nz(DLookup("User_ID", "T_Users", "Username='" & Me.UserName & "' and pword='" & Me.PWD & "'"), -1)
' check to see if we have a good login
If Me.Hold_User_ID = -1 Then
    MsgBox "Invalid username or password."
Else
    ' load the users access level into the holder form field
    Me.hold_level = DLookup("access_level", "T_Users", "Username='" & Me.UserName & "' and pword='" & Me.PWD & "'")

    Forms.F_FileCases!F_Cases.Visible = True
    Me.cmdNewCase.Visible = True
    Me.Frame2.Visible = True

End If

' need to requery the subform
Me!F_Cases.Requery

' note that inorder to refer to the F_ToDos_view form directly using Form_
' the forms Has Module property must be set to Yes
If Me.hold_level.Value = "Admin" Then
    Me.cmdUsers.Visible = True
    Else
        Me.cmdUsers.Visible = False
End If
End Sub

 

[pr2-eugin]

In the On Open event, Change the RecordSource of the Form based on the user.

 

[josephbupe]

In the On Open event, Change the RecordSource of the Form based on the user.

Thanx for your response.

But, I am still not sure about changing the record source of the subform open event. Are you saying avoiding the criteria? If so, how would that work?

 

[pr2-eugin]

Simple and easy method. Create two Queries, one Query like..

SELECT theTable.FieldsYouWant FROM theTable
WHERE someIDColumn = Forms!yourMainFormName!theIDControlName;

Save this query by giving a name - "qryUserSpecificRecords". Similarly create another Query now without the WHERE part.

SELECT theTable.FieldsYouWant FROM theTable;

Save this query by giving a name - "qryAdminRecords".

Then Go to the design view of the Form you are trying to restrict. From the list of available events, choose On Open. Code the following..

Private Sub Form_OnOpen(Cancel As Integer)
    If Forms!YourLoginFormName!hold_level = "Admin" Then
        Me.RecordSource = "qryAdminRecords"
    Else
        Me.RecordSource = "qryUserSpecificRecords"
    End If
End Sub

PS: The above code thinks that the Login form is not closed.

 

[josephbupe]

Hi Paul,

The form I am restricting is a subform. Not sure I can use the open event.

See attached copy of the mdb file.

Thanx.

 

[pr2-eugin]

More information as in what is the name of the Login form, what is the name of the Main Form and SubForm is required.

 

[josephbupe]

Sorry about that.

Please, try this one. Open F_FileCases.

ADMIN:
Username: joseph
Password: joseph

USER:
Username: kennedy
Password: kennedy

Thanx.

 

[pr2-eugin]

Try this..

Private Sub cmdLogin_Click()

    Me.Hold_User_ID = Nz(DLookup("User_ID", "T_Users", "Username='" & Me.UserName & "' and pword='" & Me.PWD & "'"), -1)
    ' check to see if we have a good login
    If Me.Hold_User_ID = -1 Then
        MsgBox "Invalid username or password."
        Exit Sub
    Else
        ' load the users access level into the holder form field
        Me.hold_level = DLookup("access_level", "T_Users", "Username='" & Me.UserName & "' and pword='" & Me.PWD & "'")
    
        ' note that inorder to refer to the F_ToDos_view form directly using Form_
        ' the forms Has Module property must be set to Yes
        If Me.hold_level.Value = "Admin" Then
            Me!F_Cases.Form.RecordSource = "Q_AdminCases"
            Me.cmdUsers.Visible = True
        Else
            Me!F_Cases.Form.RecordSource = "Q_cases"
            Me.cmdUsers.Visible = False
        End If
        
        ' need to requery the subform
        Me!F_Cases.Form.Requery
        Me!F_Cases!User_ID.Requery
        
        Me!F_Cases!cboUserID.Requery

        Me!F_Cases.Visible = True
        Me.cmdNewCase.Visible = True
        Me.Frame2.Visible = True
    End If
End Sub

 

[josephbupe]

That worked fine. Thank you Paul.

 

[pr2-eugin]

No worries ! Good Luck !