Run time error 2001 (1 Viewer)

[accessaspire219]

Does anyone know why this statement is causing error 2001?

If Me.password.Value = DLookup("[Password]", "UserInformation", "[UserID]=" & Me.UserName.Value) Then

password and UserName are names of text boxes in a form, while Password and UserID are names of columns in the table UserInformation.

What this code does is it compares the password entered by the user to the password stored in the UserInformation table for that user.

Thank you!

 

[pbaldy]

What is the data type of UserID? Is the UserID entered in the UserName textbox?

http://www.mvps.org/access/general/gen0018.htm

 

[accessaspire219]

No, every person has a UserID (text), UserName (text) and password(text) in the table. In the text box on the form, the user selects a username from a drop down list(combo box which points back to the table for the available names to choose from)

 

[pbaldy]

Did you review the link I provided? A text value requires a different syntax.

 

[accessaspire219]

It worked when I corrected the syntax!! Thanks!!
Another issue came up: whenever I try to log in, even if I put the correct password it keeps giving me the invalid password error. Here is the code.

If Me.password.Value = DLookup("Password", "UserInformation", "[UserID]='& Me.UserName.Value&'") Then
UserID = Me.UserName.Value
DoCmd.Close acForm, "Login_Form", acSaveNo
DoCmd.OpenForm "Performance_Reports", acNormal
Else
MsgBox "Password Invalid! Please try again.", vbOKOnly, "Login Failed"
Me.password.SetFocus
End If
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to use this database. Please contact if you need access.", vbCritical, "Restricted Access"
Application.Quit
End If
UserName.Value = ""
password.Value = ""
Me.UserName.SetFocus
End Sub

Does anything look wrong with the code?

 

[pbaldy]

Well, that's not the correct syntax. Try this:

If Me.password.Value = DLookup("Password", "UserInformation", "[UserID]='" & Me.UserName.Value & "'") Then

 

[accessaspire219]

If Me.password.Value = DLookup("Password", "UserInformation", "[UserID]='" & Me.UserName.Value & "'") Then

Thanks!!

Does this look ok? The logic where it compares the password doesnt seem to work though, because I keep getting invalid password even if I enter the correct password.

 

[pbaldy]

I'm still concerned with that ID vs Name thing. What is the rowsource SQL of the combo, and what is the bound column?

 

[accessaspire219]

The row source is UserInformation which is the table that contains all the details and column 1 of the table is username. (see attached screen shot)

 

[pbaldy]

If the first column is user name, the it looks like you're comparing UserName to UserID, so you will never get a match. Does this work?

If Me.password.Value = DLookup("Password", "UserInformation", "[UserName]='" & Me.UserName.Value & "'") Then

 

[accessaspire219]

When I change it to [UserName] I get error 2465 saying Cant find field "|" referred to in your expression and it highlights

[UserID] = Me.txtUserName.Value

this part of the code.

 

[pbaldy]

Can you post the db?

 

[accessaspire219]

Here it is. I dont mind if I have to remove the UserID field altogether. It is not required.

 

[pbaldy]

What are you trying to do with this line?

[UserName] = Me.txtUserName.Value

The test appears to be working fine. It's failing on that line.

 

[Poppa Smurf]

What is the purpose of this line of code
[UserName] = Me.txtUserName.Value ?

 

[accessaspire219]

Thanks guys!! I took that line off and it works fine!! What is the syntax for DLookup if the criteria is a variable generated in the form? I mean I want to use a variable which is generated in the form to lookup a table and return a column. Also, what is the best way to lock the code so that no one see it and change it? I will also need to hide the table that contains the passwords- I am not sure of the best way to do this.

 

[pbaldy]

Good reference on the DLookup:

http://www.mvps.org/access/general/gen0018.htm

I typically distribute MDE files to users, which do not expose code or allow design changes to forms/reports. Most of my users only have the runtime version of Access, and the ones that have the full version I force into runtime mode, which takes away the database window completely.

 

[accessaspire219]

Thanks for the link. How do you force someone into runtime mode? Also, in runtime mode the user will not be ableto see the code? I would also need to hide the table containing passwords so that no one finds it...any idea how I can do that?

 

[pbaldy]

With an MDE they won't be able to see the code, runtime mode or not. Like many developers, I use a version control utility to make sure users have the most current version of my application. The utility checks that, copies the more current version if needed, then starts my application. I use the /Runtime switch at that point. More info on "command line switches" in Access help. IIRC, the runtime switch isn't listed, but does work.

Runtime mode will hide the tables, but MDE won't. If you don't use the runtime, you can go into Tools/Startup and uncheck the Show Database Window option, and you can hide the table. Knowledgeable users will get past either one.

 

[accessaspire219]

Thanks Pbaldy. Appreciate all the help!!