Solved

Form access based on AccessLevelID

Posted on 2013-10-24
7
628 Views
Last Modified: 2013-10-29
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
Comment
Question by:NNiicckk
7 Comments
 
LVL 12

Expert Comment

by:pdebaets
ID: 39598708
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
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39599915
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39603367
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:NNiicckk
ID: 39605894
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
 
LVL 84
ID: 39606061
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
 

Author Comment

by:NNiicckk
ID: 39606814
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39607085
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access 2016 Debugging 7 42
Cross Tab with two column values 7 33
Prevent use of Microsoft Office application 9 53
Field Size - Double?  Want to display 0 5 30
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now