469,366 Members | 2,265 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

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

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!
Jan 23 '11 #1
8 46639
12,516 Expert Mod 8TB
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
Jan 23 '11 #2
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
MsgBox "The username has been found in the database"

End If

I just don't understand exactly how DLookup works....
Jan 23 '11 #3
12,516 Expert Mod 8TB
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.
Jan 23 '11 #4
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.
Jan 23 '11 #5

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:

Expand|Select|Wrap|Line Numbers
  1. If Me.Username.Value = DLookup("[Username]", "LoginTable", "[Username]='" & Me.Username & "'") Then
  2. MsgBox "The username has been found in the database", vbOkOnly, "Correct VBA Notice"
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. 'Lookup correct password for value entered in Username
  3.     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.

Expand|Select|Wrap|Line Numbers
  1. 'Check to see if data is entered into Username
  3.     If IsNull(Me.Username) Or Me.Username = "" Then
  4.         MsgBox "You must enter the username of an active employee.", vbOKOnly, "Required Data"
  5.             Me.Username.SetFocus
  6.         Exit Sub
  7.     End If
  9. 'Check to see if data is entered into Password
  11.     If IsNull(Me.Password) Or Me.Password = "" Then
  12.         MsgBox "You must provide a password.", vbOKOnly, "Required Data"
  13.             Me.Password.SetFocus
  14.         Exit Sub
  15.     End If
  17. 'Lookup correct password for username entered in Username
  19.     Me.PasswordLookup.Value = DLookup("[Password]", "LoginTable", "[Username] ='" & Me.Username & "'")
  22. 'Compare value of Password to PasswordLookup and
  24.     If Me.Password.Value = Me.PasswordLookup.Value Then
  27.         'Close logon form
  29.         DoCmd.Close acForm, "TheNameOfYourLoginForm", acSaveNo
  31.     Else
  32.             MsgBox "Invalid Username/Password. Please try again.", vbOKOnly, "Invalid Entry!"
  33.         Me.Password.SetFocus
  34.         Exit Sub
  35.     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.
Jan 23 '11 #6

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:

Expand|Select|Wrap|Line Numbers
  1. 'set the variables
  2. Dim UN As String
  3. Dim PW As String
  4. Dim user, pass As Boolean
  5. 'make sure none of the fields are null, or blank
  6. UN = Text
  7. PW = Text
  8. If IsNull(Username) Then
  9. MsgBox "You must enter a username."
  10. Username.SetFocus
  11. Else
  12. 'assign true to user
  13. user = True
  14. End If
  15. If IsNull(Password) Then
  16. MsgBox "You must enter a password."
  17. Password.SetFocus
  18. Else
  19. pass = True
  20. End If
  21. If user = True And pass = True Then
  22. UN = DLookup("[Username]", "LoginTable", "[Username]= '" & Me.Username & "'")
  23. PW = DLookup("[Password]", "LoginTable", "[Password] = '" & Me.Password & "'")
  24. End If
  25. If Me.DummyUser = Me.Username And Me.DummyPass = Me.Password Then
  26. MsgBox "Access granted."
  27. Else
  28. MsgBox "Access denied."
  29. End If
Jan 23 '11 #7

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
Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Dlookup ("[Password]", "Employees", "[EID]='" & Me.TheNameOfTheFieldWhereTheUserEntersTheirEID & "'")

Hope that helps!
Jan 23 '11 #8
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!
Jan 23 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by nitin005 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.