asked on

Form access based on AccessLevelID

I am trying to limit form access based on AccessLevelID.
1=Manager, 2=Statement,3=Recon,6=Statement and Recon

If they have 1 access I want them to be able to open "frmManagers"
If they have 2 access, I want them to be able to open "frmStatements"
If they have 3 access, I want them to be able to open "frmReconciliationStatements"
If they have 6 access I want them to open the "frmStatements" and "frmReconciliationStatements"

This is what I have now:

Private Sub cmdAdmin_Click()
If DLookup("[AccessLevelID]", "tblUser", "[UserID] = " & Forms!frmLogin!cboUser) = 1 Then
MsgBox "You have Manager access!", vbOKOnly
Else
MsgBox "You do not have Manager access!", vbOKOnly
End If
End Sub

Private Sub cmdDev_Click()
If DLookup("[AccessLevelID]", "tblUser", "[UserID] = " & Forms!frmLogin!cboUser) = 2 Then
MsgBox "You have Statement access!", vbOKOnly
Else
MsgBox "You do not have Statement access!", vbOKOnly
End If

End Sub

Private Sub cmdEditor_Click()
If DLookup("[AccessLevelID]", "tblUser", "[UserID] = " & Forms!frmLogin!cboUser) = 3 Then
MsgBox "You have Recon access!", vbOKOnly
Else
MsgBox "You do not have Recon access!", vbOKOnly
End If

End Sub

Private Sub cmdReader_Click()
If DLookup("[AccessLevelID]", "tblUser", "[UserID] = " & Forms!frmLogin!cboUser) =6 Then
MsgBox "You have Statement and Recon access!", vbOKOnly
Else
MsgBox "You do not have Statement and Recon access!", vbOKOnly
End If

ElseIf Then
MsgBox "Access Granted!", vbInformation
DoCmd.Close
DoCmd.OpenForm "frmReconciliationStatements"
End Sub

pdebaets

Here's one way to do it. In your form frmManagers OnOpen event procedure put this code:

If DLookup("[AccessLevelID]", "tblUser", "[UserID] = " & Forms!frmLogin!cboUser) = 1  then
Else
    msgbox "You do not have authority to open this form"
    cancel = true
end if

Open in new window

If you are opening the form with a Docmd.OpenForm, then the calling code will need to handle a 2501 error which occurs when the form open is cancelled.

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PatHartman

If you are using the switchboard form and the switchboard items table, you could add a new column to the table for level and change the query the form uses to select items where the level field is >= the value in the log in form. So have the users log in using a form. Then instead of closing that form, just hide it when you open the switchboard.

The only thing quirky about this is that given the way the switchboard form works, the menu may have gaps. If a user can go to 2, 3, and 7; the lines for 1, 4,5,6,8 will be blank.

NNiicckk

ASKER

Sorry for the crude example.... I cannot get the security level correct. Nick

John Doe password 1234

AccessLevelID 1 is for HomeManagers
AccessLevelID 2 is for HomeStatements
AccessLevelID 3 is for frmReconciliationStatements
Example.accdb

Scott McDaniel (EE MVE )

I've made a few changes, and have uploaded them here. Here's what I did:

1) Added a textbox named "txUserLevel" to frmLogin
2) Changed the code in the "Validate" button to NOT close frmLogin, but instead to simply Hide it.
3) In the Open event of the "Home" form, added code to disable the "Recon" button if Forms("frmLogin").txUserLevel = 1

You must somehow be able to to get to the "user level" value, and a good way to do that is with the hidden form method shown in the uploaded db. Instead of closing the login form, I hide it, and I can then refer to the new txUserLevel control there. So from any form in the database, I can refer back to frmLogin to get my current user's "level id".

Obviously you'd want to hide that textbox ... and you'd want to change the code in the Home form's Open event to show/hide the controls based on the user level.
Example.accdb

NNiicckk

ASKER

Thanks, that seemed to work. What if I wanted the txUserLevel to only open 1 link and have the other 2 blocked out? How do I add to the If statement so that Me.Recon.Enabled = False and Me.HomeManagers.Enabled = False if they should only get into the HomeStatements form? Thanks

Private Sub Form_Open(Cancel As Integer)
If Forms("frmLogin").txUserLevel = 1 Then
Me.Recon.Enabled = False
End If

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial