How to make Login Form with VBA that validates username and password?

[Marty L]

Hey everyone,

I know this question has been asked many times, but I can't seem to get a handle on it.

In a nutshell, I just want a form that validates the username and password someone puts in that's in a database. I know the code will the use the DLookup function, but that's where I get a little mixed up. I know what it's used for, but I'm not sure how to make use of it.

I currently have a table called LoginTable, with the fields ID (as PK), Username, and Password.

If anyone could help explain to me how I would get this to work, that would be great!

Thanks in advance!

[Rabbit]

This thread will give you a starting point, in addition to the pitfalls of using Access for data that needs to be secure. http://bytes.com/topic/access/answer...-access-2007-a

[Marty L]

Thanks for the reply Rabbit.

While that is a great reference, I still don't know how I would code it. For instance, here's what I have so far, and I know it isn't right because it isn't working, haha.

If Me.Username.Value = DLookup("[Username]", "LoginTable", "Username" = Username) Then
Else
MsgBox "The username has been found in the database"

End If

I just don't understand exactly how DLookup works....

[Rabbit]

Well... Seeing as how that thread has the code for a login form and all you would have to do is change the names of the objects to your names, I suggest you study that code a little closer.

[Marty L]

I have been studying the code most of the day. I know vb.net fairly well, but VBA is different. I'll try to look it over some more tomorrow, and if I still can't get it, I'll come back.

[Jesse Jones]

Marty

I'm not an expert in this, but I understand where you are at and have been working on this myself for a few days. Before I explain, let me say this: As Rabbit likes to point out, using VBA to secure a database is not the greatest option. There are many, many ways around it. If however, you are simply trying to keep employees from messing things up or if your users are not VBA (or really Access) knowledgeable,this will do okay for you.

Let me begin by telling you what's wrong with your DLookup that you used for an example: the criteria is not marked correctly. Compare it to one of the codes I used. Because of the nature of the criteria, it needs to be marked as an expression. You need the quotes to do that. Here's your code corrected:

If Me.Username.Value = DLookup("[Username]", "LoginTable", "[Username]='" & Me.Username & "'") Then
MsgBox "The username has been found in the database", vbOkOnly, "Correct VBA Notice"
 
End If
 
 

As for your login table code - One quick tip: put a textbox on your login form whose visible property is set to no. Name this "PasswordLookup" and make it a DLookup for the password that is correct for the user entered. I did this with a line of code included with the rest attached to the command button. The exact line you want would be:

'Lookup correct password for value entered in Username
 
    Me.PasswordLookup.Value = DLookup("[Password]", "LoginTable", "[Username] ='" & Me.Username & "'")
 
 

Now all you have to do is have the command button check if the value in the password text box (that the person trying to log in typed) is = to the password that the hidden dlookup found. I hope that makes sense.

If you do that, this code will work. I have updated mine to match what yours should be. Your login will work if you simply copy and paste this to an event that should run on click of the command button.

'Check to see if data is entered into Username
 
    If IsNull(Me.Username) Or Me.Username = "" Then
        MsgBox "You must enter the username of an active employee.", vbOKOnly, "Required Data"
            Me.Username.SetFocus
        Exit Sub
    End If
 
'Check to see if data is entered into Password
 
    If IsNull(Me.Password) Or Me.Password = "" Then
        MsgBox "You must provide a password.", vbOKOnly, "Required Data"
            Me.Password.SetFocus
        Exit Sub
    End If
 
'Lookup correct password for username entered in Username
 
    Me.PasswordLookup.Value = DLookup("[Password]", "LoginTable", "[Username] ='" & Me.Username & "'")
 
 
'Compare value of Password to PasswordLookup and
 
    If Me.Password.Value = Me.PasswordLookup.Value Then
 
 
        'Close logon form
 
        DoCmd.Close acForm, "TheNameOfYourLoginForm", acSaveNo
 
    Else
            MsgBox "Invalid Username/Password. Please try again.", vbOKOnly, "Invalid Entry!"
        Me.Password.SetFocus
        Exit Sub
    End If
 

The only thing in this code you need to adjust is in the last action: put the name of your login form in the quotation marks, then you're good to go.

Also, remember that for this to be any good, you have to make this form modal, disable the design view for the form, disallow Default Shotcut Menu (if you are using tabbed objects) and set the property of the close button to no. Otherwise, the user can simply close the form and have access to every object in the database. Also, you may want to make yourself aware of the shift bypass key feature. In other words, this coding will work for you, but please be aware of the security breaches that are possible by securing your database in this way.

Hope this all helps. I understand the headache of trying to find the answers.

[Marty L]

Jesse,

Thank you so much for the help and explaining how everything is broken down. Last night, I actually "fixed" the DLookup but it had errors when i entered a username that wasn't found in the database. As I've said before, I'm familiar with VB.Net and VBA code is a little different. For instance, if I want to close the current form, I'd just type me.Hide(). To open a new form, i'd type NameOfForm.Show(). I guess all I need to do is understand the syntax of it. What I got out of DLookup is the following

Dlookup(this space is used to find what row you're looking for, this space is what row that particular table is in, this row is to find a value of that row you were looking for)

Also, I know that using Access to make a password-protected database isn't the best option, I just wanted to get some practice with it and learn how to use DLookup. It isn't being used for any business; I'm a college student and I'm taking Visual Basic 2. The more I know about programming, the better.

When I saw your code starting out with [Password], I was a little confused. Here is my code:

'set the variables
Dim UN As String
Dim PW As String
Dim user, pass As Boolean
'make sure none of the fields are null, or blank
UN = Text
PW = Text
If IsNull(Username) Then
MsgBox "You must enter a username."
Username.SetFocus
Else
'assign true to user
user = True
End If
If IsNull(Password) Then
MsgBox "You must enter a password."
Password.SetFocus
Else
pass = True
End If
If user = True And pass = True Then
UN = DLookup("[Username]", "LoginTable", "[Username]= '" & Me.Username & "'")
PW = DLookup("[Password]", "LoginTable", "[Password] = '" & Me.Password & "'")
End If
If Me.DummyUser = Me.Username And Me.DummyPass = Me.Password Then
MsgBox "Access granted."
Else
MsgBox "Access denied."
End If
 

[Jesse Jones]

Mary


I'm afraid I'm not sure what you're question is. I can say that I am a complete novice in this. If you're studying VBA, I think you will quickly pass my knowledge level. However, here is how a dlookup works (as I understand it).

The first thing in the syntax is an expression defining the field for which you are looking. Dlookup ("[Password]"....) is using the dlookup to find the value entered in a field named "Password". But this is obviously not enough information. The lookup must know the domain the password is on. In my case, the password is stored in a table named "Employees" so that is the domain. A dlookup function will not run without the field you are trying to find and the domain that field is on. So, when I'm looking for a password stored on the table (domain) Employees in a field named "Password", my code is

DLookup ("[Password]", "Employees"). 

This will run. The problem is that it returns any random password stored on any record's "Password" field on the domain "Employees". I don't want any random password, so I need the dlookup to use a criteria to limit it's return. You do this with the third piece of the syntax. On a login form, you're going to want to find the password for a specific user. So, the criteria needs to be defined by the username entered in the login form. In my case, the login username is the employee's EID. I need the code to lookup the password stored in the domain "Employees" on the record with the proper EID. I hope I'm still making sense. So, my criteria for the lookup is that the target record's EID = the EID entered on the login form. All put together, the dlookup is like this:

Dlookup ("[Password]", "Employees", "[EID]='" & Me.TheNameOfTheFieldWhereTheUserEntersTheirEID & "'")
 

Hope that helps!

[Marty L]

Ahhhh... a light-bulb just stuck! That makes perfect sense! Thank you so much for your help!

If I do surpass your knowledge and you need anything, just let me know!

Thanks again Jesse!