Code for DLookup for username and password for login

Question

I want to create a registration form, but I don’t know how to get or confirm that what I typed in the user and password text box is in my table using DLookup in my login button.

Here is my current code:

Dim u As Variant Dim p As Variant Dim inu As String Dim inp As String u = DLookup("cusername", "tbl_users", "inuser.Value") p = DLookup("cpassword", "tbl_users", "inpass.Value") inu = inuser.Value inp = inpass.Value If inu = u And inp = p Then DoCmd.OpenForm "frm_userlog" MsgBox "Welcome " & tuser & "!" ElseIf IsNull(Me.inuser.Value) And inpass.Value = 1 Then MsgBox "You must input a username" ElseIf IsNull(Me.inpass.Value) And inuser.Value = 1 Then MsgBox "you must input a password" ElseIf IsNull(Me.inuser.Value) And IsNull(Me.inpass.Value) Then MsgBox "you must input a username and password" Else MsgBox "The username or password you entered is invalid" End If End Sub 

Answer 1

The third DLookup argument, criteria, is an optional string expression that is similar to the WHERE clause in an SQL statement, without the word WHERE ".

In yours, you seem to be trying to assign the value of a control named inuser . However, you are actually passing a string containing the text "inuser.Value".

 DLookup("cusername", "tbl_users", "inuser.Value") 

But that will not give you what you want, even if you remove the quotation marks.

If you want to find the cusername from tbl_users where some kind of field (maybe user_id ) matches inuser.Value ...

 DLookup("cusername", "tbl_users", "user_id = " & inuser.Value) 

If this field, user_id , is a textual rather than a numeric data type, quotes the criteria string ...

 DLookup("cusername", "tbl_users", "user_id = '" & inuser.Value & "'") 

It seems to me that you have a problem with the same type with DLookup("cpassword", ...) , so if I got the first one on the right, make a similar change there.

For more information on DLookup , see DLookup () Usage Description, Access 2000 Examples, and Troubleshooting . Although this old article still applies to the latest AFAICT access versions.

Answer 2

According to my comment, I use this:

 Private Sub cmdlogin_Click() If IsNull(Me.cb_user) Then MsgBox "You didn't select a user", vbCritical, "Error" Me.cb_user.SetFocus Exit Sub Else If IsNull(Me.txtpass) Then MsgBox "You didn't enter a password", vbCritical, "Error" Me.txtpass.SetFocus Exit Sub Else If encrypt(Me.txtpass.Value) = DLookup("password", "users", "user_id = " & Me.cb_user.Value) Then DoCmd.OpenForm ("home") Me.Visible = False Else MsgBox "Incorrect password. Please try again", vbCritical, "Incorrect password" Me.txtpass = Null Me.txtpass.SetFocus End If End If End If End Sub 

Where cb_user is a field with a list of usernames.

Encrypt is the main ROT 13 encryption that I put in the module:

 Public Function encrypt(strInput As String) Dim n As Integer, i As Integer n = 13 For i = 1 To Len(strInput) Mid(strInput, i, 1) = Chr(Asc(Mid(strInput, i, 1)) + n) Next i encrypt = strInput End Function 

If not necessary, omit the encrpyt bypass around the password search so that:

 If encrypt(Me.txtpass.Value) = DLookup("password", "users", "user_id = " & Me.cb_user.Value) Then 

becomes

 If Me.txtpass.Value = DLookup("password", "users", "user_id = " & Me.cb_user.Value) Then