DlookUp in SQL

Question

Below is a DLookUp expression I used in a access table that works perfectly. It reads from the table "tblUsers"

If Me.Password.Value = DLookup("Password", "tblUsers", "UserName='" & Me.Username.Value & "'") Then

DoCmd.OpenForm "frmNavigation", acNormal

Else

MsgBox "The password you entered is incorrect", vbOKOnly, "Login"

End If

I have since converted the table to SQL and changed the table from which the DLookUP would read from "tblUsers" to "dbo_tblUsers":

If Me.Password.Value = DLookup("Password", "dbo_tblUsers", "UserName='" & Me.Username.Value & "'") Then

DoCmd.OpenForm "frmNavigation", acNormal

Me.UserLookup.Value = (myUser)

Else

MsgBox "The password you entered is incorrect", vbOKOnly, "Login"

End If

I know that the username and password are correct but with the SQL table (dbo_tblUsers) I keep getting the message "The password you entered is incorrect", which is the 'Else' message from the If statement"

How I I fix this?

Thanks for any help I can get

Answer 1

Thanks for all the assistance. I found a work around. It seems like the field in SQL is been evaluated as something else in MS access. I used DLookUp to evaluate the Username and Password as a number (ID) and then do a match so that what was entered matched the database. I will have to use this until I can come up with a more efficient method.

Answer 2

Hmm. Have you stepped through the code and confirmed the value of Me.Username? And the value the DLookup returns?

The code looks correct, but if its choosing the Else clause, then the value the DLookup is returning is not matching what was typed.

My login routine doesn't work the way your does. You can see how I do it at https://scottgem.wordpress.com/?s=login. Basically, I use a combobox for the user to select themselves. The combo returns the user's ID (an autonumber field). The password is also part of the Rowsource so I compare to the column in the rowsource, instead of using a DLookup (DLookups are usually my last resort).

Answer 3

Thanks for your quick reply. I have done all that and have conducted tests that shows that the DLookUP works on the same form from the same table "dbo_tblUsers"

Answer 4

So you migrated your data to SQL Server? You relinked your front end to the correct SQL Server back end? Can you open dbo_tblUsers from the Navigation Pane?

Have you tried testing the DLookup in Immediate mode?