[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Form access based on AccessLevelID

Posted on 2013-10-24
7
Medium Priority
?
695 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 39

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 85
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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

650 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