Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-08-2016, 10:03 AM   #1
Orange56
Newly Registered User
 
Join Date: Jul 2016
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Orange56 is on a distinguished road
Login Screen Message Box Showing When a User Has Permissions

I am currently building a database in which one of the requirements is to pull the user's Windows login name and set permissions for the users (based upon the user's permissions will open their corresponding form). Essentially, this will alleviate the users having to login into both Windows and MS Access through a login screen. I was able to successfully grab the users Windows Login name and permissions within a back end table. The problem that I am having is that when a user does have permissions to the database the message box stating "You do not have access to this database" continues to show. Also, I am not sure if I am correctly using rs.FindFirst "UserName = '" & EmployeeType_ID & "'". As the UserName is the users Windows login name and EmployeeType_ID is a text field which the admin would either place a 1-4 to set the user permission (UserName and EmployeeType_ID are within a table).

Any help would be greatly appreciated!

Code:
Private Sub Form_Load()
    
    Debug.Print Environ("UserName")
    Debug.Print Environ$("ComputerName")
    
    Dim strVar As String
    Dim i As Long
    For i = 1 To 255
        strVar = Environ$(i)
        If LenB(strVar) = 0& Then Exit For
        Debug.Print strVar
    Next

Dim rs As Recordset
    
    Set rs = CurrentDb.OpenRecordset("tblUser", dbOpenSnapshot, dbReadOnly)
    
    rs.FindFirst "UserName = '" & EmployeeType_ID & "'"
    
    If rs.NoMatch = True Then
        MsgBox "You do not have access to this database.", vbInformation, "Access"
        Access.Quit
    End If
    
    If rs!EmployeeType_ID = 4 Then
    
        Dim prop As Property
        On Error GoTo SetProperty
        Set prop = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False)
        
        CurrentDb.Properties.Append prop

SetProperty:
        If MsgBox("Would you like to turn on the bypass key?", vbYesNo, "Allow Bypass") = vbYes Then
            CurrentDb.Properties("AllowBypassKey") = True
        Else
            CurrentDb.Properties("AllowBypassKey") = False
        End If
        
            DoCmd.OpenForm "frmManager"
            DoCmd.Close acForm, "frmLogin", acSaveNo
    End If

    If rs!EmployeeType_ID = 3 Then
        DoCmd.OpenForm "frmAssisstant_Manager"
        DoCmd.Close acForm, "frmLogin", acSaveNo
    End If
    
    If rs!EmployeeType_ID = 2 Then
        DoCmd.OpenForm "frmLead"
        DoCmd.Close acForm, "frmLogin", acSaveNo
    End If
    
    If rs!EmployeeType_ID = 1 Then
        DoCmd.OpenForm "frmGeneral_User"
        DoCmd.Close acForm, "frmLogin", acSaveNo
    End If
End Sub

Orange56 is offline   Reply With Quote
Old 12-08-2016, 10:13 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,296
Thanks: 13
Thanked 4,112 Times in 4,044 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Login Screen Message Box Showing When a User Has Permissions

I think you want:

rs.FindFirst "UserName = '" & Environ("UserName") & "'"

I personally would open the recordset filtered rather than use FindFirst, but a user table is probably small enough to make it irrelevant.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Orange56 (12-08-2016)
Old 12-08-2016, 10:23 AM   #3
Orange56
Newly Registered User
 
Join Date: Jul 2016
Posts: 5
Thanks: 3
Thanked 0 Times in 0 Posts
Orange56 is on a distinguished road
Re: Login Screen Message Box Showing When a User Has Permissions

Quote:
Originally Posted by pbaldy View Post
I think you want:

rs.FindFirst "UserName = '" & Environ("UserName") & "'"

I personally would open the recordset filtered rather than use FindFirst, but a user table is probably small enough to make it irrelevant.
Thank you, this fixed my problem. Is there a benefit of using the recordset filter? If so how would I implement it?

Orange56 is offline   Reply With Quote
Old 12-08-2016, 10:36 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,296
Thanks: 13
Thanked 4,112 Times in 4,044 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Login Screen Message Box Showing When a User Has Permissions

With your code, you're pulling the entire table over the network, then finding the record you need. The better method is to use SQL that only pulls the desired record over the network. Like I said, a user table is probably so small that it won't make a difference, but it's a good habit to be in. It would look like:

strSql = "SELECT * FROM tblUser WHERE UserName = '" & Environ("UserName") & "'"

Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot, dbReadOnly)

And your test to see if it found the person changes to:

If rs.EOF Then

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Reply

Tags
vba , vba access 2016

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Creating User Login Screen Joe B. Forms 25 07-11-2012 03:21 PM
[SOLVED] User Security Levels and Password login screen goldie12 Forms 5 10-21-2010 04:59 AM
How to create a user login screen upon loading database Nabz-78 Forms 1 03-15-2008 10:34 AM
a simple login screen / also access in full screen f_disk General 3 07-28-2006 02:04 AM
user login in screen rix General 3 05-03-2006 12:07 PM




All times are GMT -8. The time now is 05:13 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World