Solved

Form access based on AccessLevelID

Posted on 2013-10-24
7
614 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

707 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

18 Experts available now in Live!

Get 1:1 Help Now