X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Access 2007 User Login using Query / VBA assigning user Level    
 
   
PhillyBilly
post Jun 5 2010, 12:25 AM
Post #1

New Member
Posts: 10
From: Philadelphia, PA



Hi All,
Yes, I registered to ask this question, and I'm sure it won't be the last - so consider this my introduction... This seems like it has the most intelligent forum users...
Oknow there are many ways to do this, but this is the way I was attempting it...
TABLE - UserAccounts
UserAccountID - AutoNumber
UserAccountName - Text
UserAccountLogin - Text
UserAccountPassword - Password
UserAccountAccessLevel - Number
FORM - LogInDialog
LogInUser - TextBox - Unbound
LogInPassword - TextBox - Unbound
LogInButton - Button - LogInButton_Click()
QUERY - DoesLoginMatch - Type:Select
Table - UserAccounts
Fields - All from UserAccounts
Criteria - UserAccountLogin - [Forms]![LogInDialog]![LogInUser]
Cirteria - UserAccountPassword - [Forms]![LogInDialog]![LogInPassword]
MACRO - OpenUA
Opens the intro window upon successful login.
VBA - LogInButton_Click()
CODE
Private Sub LogInButton_Click()
  
   Dim loginFailed
   If (Forms.LogInDialog.LogInUser = Queries.DoesLoginMatch.UserAccountLogin) Then DoCmd.RunMacro "OpenUA" Else loginFailed = MsgBox("INCORRECT LOGIN!", vbOKOnly, "Login Failure")
  
End Sub

And after it successfully logs in, I need it to GLOBALLY store the UserAccountAccessLevel so it can be referenced for security reasons - to keep them out of certain parts of the DB, unless their security level allows access by that user. Just give me the code to store it globally, I'll try to figure the rest out on my own.
Everything works great until I add the reference to "Queries.DoesLoginMatch.UserAccountLogin" in the VBA code. Quite obvious that is the incorrect way to do it - but I have scoured the internet, and my F1 key is beginning to fade from over use - so I figured I would ask you guys/girls for a bit of help...
Thanks!
William
Go to the top of the page
 
+
PropMgr
post Jun 5 2010, 12:53 AM
Post #2

UtterAccess Ruler
Posts: 1,199
From: Sacramento, CA USA



Rather than use two text boxes I generally use a combo box to look up the user
When in a fourth column of the combo box I have the password
It is then easy to compare the fourth column with the text box entry for the pass word.
In the third column I have the user level
This seems to work well for me and is fairly simple
If Me.txtPassWord = Me.cmboUserID.Column(4) Then

TempVars!UserID = Me.cmboUserID
TempVars!UserLevel = Me.cmboUserID.Column(3)
TempVars!DfltCityID = DLookup("DfltCityID", "tblCompanyInfo", "CompanyID = " & 1)
DoCmd.Close , ""
Else
MsgBox "Password Incorrect"
End If
Go to the top of the page
 
+
HairyBob
post Jun 5 2010, 01:49 AM
Post #3

UtterAccess Guru
Posts: 657
From: London



Try this (not tested):
!--c1-->
CODE
    Dim rst As DAO.Recordset
    Dim loginFailed
    
    Set rst = CurrentDb.OpenRecordset("DoesLoginMatch", dbOpenSnapshot)
    
    If Not rst.EOF Then
        rst.MoveFirst
        
        If (Forms.LogInDialog.LogInUser = rst!UserAccountLogin) Then
            DoCmd.RunMacro "OpenUA"
        Else
            loginFailed = MsgBox("INCORRECT LOGIN!", vbOKOnly, "Login Failure")
        End If
    End If
    
    rst.Close
    Set rst = Nothing
Go to the top of the page
 
+
HiTechCoach
post Jun 5 2010, 02:12 AM
Post #4

UtterAccess VIP
Posts: 18,680
From: Oklahoma City, Oklahoma



I would recommend that you take a look at this:
Boiler Plate Database
Go to the top of the page
 
+
niesz
post Jun 5 2010, 07:12 AM
Post #5

Utter A-fishin'-ado
Posts: 17,957
From: Cincinnati, Ohio, USA . . . ><((((°>



As always, there's generally more than one correct way of accomplishing something with Access.
The most common way of matching a user ID to a password in a table would be with a DLookup(). It can return the password back as a string which can be compared to your unbound Password control.
HAs far as the global variable is concerned, there again are many ways of dealing with it. Probably the two most common are:
1) Leave the login form open, but simply make it invisible. Then you can just reference the user ID control of the form when the user ID is needed.
2) Create a global variable in a standard module (not in a form module). This exposes the variable to the whole project and you can assign it or retrieve it's value from anywhere.
CODE
Public gstrUserID as String

Keep in mind with the global variable approach, if your code throws any errors that are unhandled, all global variables lose their value.
These are only but two of the ways to accomplish what you're trying to do.
Go to the top of the page
 
+
PhillyBilly
post Jun 6 2010, 02:24 AM
Post #6

New Member
Posts: 10
From: Philadelphia, PA



Wow, I got all these responses so quickly - if I knew this forum was so active, I wouldn't have waited a day to check it - very impressive, people...
Thank you all for your help!
Oended up, as niesz recommended, using the DLookup() method. I should have done it that way from the beginning.
This is what some research (and modifications) came up with - and it works great:
CODE
    If IsNull(Me.LogInUser) Or Me.LogInUser = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.LogInUser.SetFocus
        Exit Sub
    End If
    If IsNull(Me.LogInPassword) Or Me.LogInPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.LogInPassword.SetFocus
        Exit Sub
    End If
    If Me.LogInPassword.Value = DLookup("UserAccountPassword", "UserAccounts", _
            "[UserAccountID]=" & Me.LogInUser.Value) Then
        TempVars("MyUserAccountID").Value = Me.LogInUser.Value

By the way, I learned a new [for me], very useful method for dealing with variables that will span multiple forms, are only valid for the local workstation, and die upon closing Access - TempVars. I can see myself using them a lot.
Example:
CODE
'Declare the variable
TempVars("MyUAAccessID").Value = Val("0")
'Assign a value to the variable
TempVars("MyUAAccessID").Value = DLookup("UserAccessID", "UserAccess", "[UserAccessID]=" & TempVars("MyUserAccess").Value)

Maybe not the correct topic for this, but since it is being used in the code, I may as well explain it.
Thanks, and you guys/girls will definitely be seeing me around here - the response was an overwhelming surprise!
-WW
Go to the top of the page
 
+
PhillyBilly
post Jun 10 2010, 10:00 PM
Post #7

New Member
Posts: 10
From: Philadelphia, PA



Thanks niesz (and everyone else). Everything you said is absolutely true, and this is a very cool site with GREAT response times - definitely THE premiere site for Access. You will all be seeing me around as I work on this project, and most likely be able to help others as well. I look forward to being a part of this very intelligent community. Have a great day/night everyone!
And when my project is complete, there will definitely be a "Thank You" to UtterAccess.com in the credits - without a doubt... Free advertising, lol...
Go to the top of the page
 
+
xtajose
post May 25 2011, 07:58 AM
Post #8

New Member
Posts: 9



Thankx. I did try this script exacly. but i dont know how to create the macro..could you guide me please step by step..
Go to the top of the page
 
+
xtajose
post May 25 2011, 08:03 AM
Post #9

New Member
Posts: 9



This is the command for LogInButton_Click()
If IsNull(Me.LogInUser) Or Me.LogInUser = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.LogInUser.SetFocus
Exit Sub
End If
If IsNull(Me.LogInPassword) Or Me.LogInPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.LogInPassword.SetFocus
Exit Sub
End If
If Me.LogInPassword.Value = DLookup("Employee.[Sp.Passowrd]", "Employee", "Employee.[Sp Code]=" & Me.LogInUser.Value) Then
TempVars("Sp Code").Value = Me.LogInUser.Value
How to link up the macro
Go to the top of the page
 
+
thulgar
post Oct 19 2011, 10:28 AM
Post #10

New Member
Posts: 1



Hey folks, thanks for those that read this.
eres my question, i'm working on a project, in Access 2007. I am creating a Login Table for users. I have given them usernames and passwords in another table called "Login".
What i'm looking for, is how do i script it, so on the login form when they put in their username and password and click the button *Login* that it will check the table "Login" verifiy that the information is correct for username and password, and log them into another table by the name of *Main_Page* or *Administration* ? I have checked YouTube, and online and i can't find any helpful information pertaining to login setups for access 2007, not even ones for 2010. So i'm stuck and looking for help. Here are the files
table: Login
fields: us / ps *with username and password info*
form: Login
text boxes: Username / Password / Login *button*
Les
You can post here or send me an email to thulgar1@gmail.com either or thanks ahead of time. I have different login scripts and nothing seems to work out, even the one here from PhillyBilly and others.
Go to the top of the page
 
+
HiTechCoach
post Oct 19 2011, 11:19 AM
Post #11

UtterAccess VIP
Posts: 18,680
From: Oklahoma City, Oklahoma



Les,
welcome2UA.gif
To make a support forum helpful we need to always post replies, etc to the forum. This way everyone can benefit.
You stated you already have login scripts but cant get them to work. Have you tried You can use the boiler place example I posted in a post #3?
Since you have already tried to use some logon scripts and cant get them to work then I would recommend that you start you own thread and post a sample of your database. Withou seeing your database it will be hard to spot your issues.
PS: It is not recommend or considered good netiquette that you hijack someone else's post.
Go to the top of the page
 
+
Julianna
post Dec 4 2012, 12:14 PM
Post #12

New Member
Posts: 1



Hi All,
I'm trying to create a login screen and have used the code specified below... but getting some errors (using Access 2007) please can you help?
Private Sub Login_Click()
If IsNull(Me.Username) Or Me.Username = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.Username.SetFocus
Exit Sub
End If
If IsNull(Me.Password) Or Me.Password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.Password.SetFocus
Exit Sub
End If

If Me.Password.Value = DLookup("Login.[password]", "Login", "Login.[username]=" & Me.Username.Value) Then
TempVars("username").Value = Me.Username.Value
Exit Sub
End If
Go to the top of the page
 
+
jaggigagan
post Feb 10 2013, 06:42 PM
Post #13

New Member
Posts: 1



Hi
can you tell me how i can see the code behind the comand buttons on the example database(Boilers plate) in post#3. I need all the features that the example database has. I would appreciate if you can tell me how to look at the codes and design view of the login window in the FE. Please and thank you! OR please guide me in a direction to take. I am a beginner at Access and do not know alot.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 19th December 2014 - 02:13 AM