DLookup problem

[campo88]

Hi

I want to check whether a password entered in a form for a student corresponds with the password in a table in access. The code below does not seem to work. Can anyone help? I know the names of my tables and fields are correct.


If Me.Password.Value = DLookup("[Password]", "Student", "StudentID = " & [StudentID]) Then
MsgBox ("login successfull")

Else
Call MsgBox("Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!")
End If

 

[boblarson]

Try changing this line

If Me.Password.Value = DLookup("[Password]", "Student", "StudentID = " & [StudentID]) Then

to this:

If Me.Password.Value = DLookup("[Password]", "Student", "StudentID = " & Me.StudentID) Then

 

[campo88]

unfortunatly still not working

 

[boblarson]

Well, you might be suffering from the fact that PASSWORD is an Access Reserved Word.

Also, do you have a control on your form that is named StudentID or is it named something like Text0, etc.? You need to refer to the name of the control OR use the bang to refer to the field:

Me!StudentID (for the field)

Me.StudentID (for the control)

 

[campo88]

I can validate whether password is a blank field so it should work.

Yes, I do have StudentID as the control name.

 

[boblarson]

Try putting a breakpoint there at the code and see what values the controls are returning.

 

[campo88]

The field name in my table for StudentID does have a blank in it and so is:

Student ID

I am not sure if this makes a difference.

Heeeelp please

 

[campo88]

I am getting the below syntax error...

(missing operator) in query expression "Student ID = JC"

 

[boblarson]

The field name in my table for StudentID does have a blank in it and so is:

Student ID

I am not sure if this makes a difference.

Heeeelp please

Yes, that does make a difference. So you would need:

If Me.Password.Value = DLookup("[Password]", "Student", "[Student ID] = " & Me.StudentID) Then

 

[campo88]

Hi

I think we are nearly there. I am now getting the error message:

The expression you entered as a query paramater produced this error: "JC"

 

[boblarson]

Okay, so STUDENT ID is text (I would have thought it would be numeric). So for text you need:

If Me.Password.Value = DLookup("[Password]", "Student", "[Student ID] = " & Chr(34) & Me.StudentID & Chr(34)) Then

 

[campo88]

wow cheers that works perfect. Why did i have to enter that?

 

[boblarson]

wow cheers that works perfect. Why did i have to enter that?

Chr(34) is actually a double quote. I like using those instead of quotes just in case the data has quotes in it.

When trying to get text items you need to surround it with double, or single, quotes. When doing dates you use the octothorpe (#) and if numeric you don't use anything surrounding.

 

[campo88]

ok cheers ..could you let me know how i could do it using quotes then please?

 

[boblarson]

It would be:

with single quotes:
If Me.Password.Value = DLookup("[Password]", "Student", "[Student ID] = '" & Me.StudentID & "'") Then

with double quotes:

If Me.Password.Value = DLookup("[Password]", "Student", "[Student ID] = " & & """ & Me.StudentID & """) Then

 

[Lightwave]

Bob I hope Microsoft appreciate you!! Just admiring your patience

 

[Poppa Smurf]

In my databases when I want to validate the user's password I use the following code as I had problems with the correct syntax e.g. quotes etc.

In the code below frm_main is the login screen

dim check_password as string

check_password = DLookup("[password]", "tbl_users", "user_id=forms!frm_main!user_id")
If UCase(check_password) = UCase(Forms!frm_main!password) Then
.... do some processes
Else
... display messages etc.
End If

 

[boblarson]

In my databases when I want to validate the user's password I use the following code as I had problems with the correct syntax e.g. quotes etc.

In the code below frm_main is the login screen

dim check_password as string

check_password = DLookup("[password]", "tbl_users", "user_id=forms!frm_main!user_id")
If UCase(check_password) = UCase(Forms!frm_main!password) Then
.... do some processes
Else
... display messages etc.
End If

Although the UCase code is not necessary. In Access, mYpAssWord evaluates to MyPassword or MYPASSWORD just fine as it is case insensitive.