• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 701
  • Last Modified:

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
0
NNiicckk
Asked:
NNiicckk
2 Solutions
 
pdebaetsCommented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What sort of troubles do you have now? You've provided the code and such, but you did not state the problem you have.

I have better luck with Select Case statements than If -ElseIF structures:

Dim MyID AS Integer
MyId = DLookup("[AccessLevelID]", "tblUser", "[UserID] = " & Forms!frmLogin!cboUser)

Select Case MyID
  Case 1
      Msgbox "Manager Access"
  Case 2
      MsgBox "Statement Access"
  Case 3
      etc etc
End Select
0
 
PatHartmanCommented:
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.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
NNiicckkAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
NNiicckkAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Just add to the If statement:

If Forms("frmLogin").txUserLevel = 1 Then
  Me.Recon.Enabled = False
  Me.HomeManagers.Enabled = False
ElseIf Forms("frmLogin").txUserLevel = 2 Then
  Me.Recon.Enabled = True
  Me.HomeManagers.Enabled = False
'/ and so forth
End If

Or use a Select Case statement instead of an If - Then structure:

Select Cast Forms("frmLogin").txUserLevel
  Case 1
    Me.Recon.Enable = True
    Me.HomeManagers.Enabled = True
  Case 2
    Me.Recon.Enable = False
    Me.HomeManagers.Enabled = True
  Case 3
    Me.Recon.Enable = False
    Me.HomeManagers.Enabled = False
  Case Else
End Select
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now