Access Login Form Password Change and Update : SQL / VBA

Question

I have a login form that I plan on using to control user access to a database. I want to initialize with the user password = "password" and prompt the user to change their password upon first login. I am having difficulties UPDATE-ing the usertable that stores my user credentials.

Ordinary login VBA is associated with frm_Login:

Option Compare Database
Option Explicit

Private Sub btnLogin_Click()
    Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("X_tblUsers", dbOpenSnapshot, dbReadOnly)

rs.FindFirst "UserName='" & Me.txtUserName & "'"

If rs.NoMatch Then
    Me.lblWrongUser.Visible = True
    Me.txtUserName.SetFocus
    Exit Sub
End If
Me.lblWrongUser.Visible = False

If rs!Password <> Nz(Me.txtPassword, "") Then
    Me.lblWrongPass.Visible = True
    Me.txtPassword.SetFocus
    Exit Sub
End If
Me.lblWrongPass.Visible = False

TempVars("UserName").Value = Me.txtUserName.Value

If Me.txtPassword = "password" Then
    DoCmd.OpenForm "frm_PassChange"
End If

If rs!UserType = 3 Then
    Dim prop As Property
    On Error GoTo SetProperty
    Set prop = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False)

    CurrentDb.Properties.Append prop

SetProperty:
    If MsgBox("Turn on Bypass key?", vbYesNo, "Allow Bypass") = vbYes Then
        CurrentDb.Properties("AllowByPassKey") = True
    Else
        CurrentDb.Properties("AllowByPassKey") = False
    End If

End If

Me.Visible = False
Globals.Logging "Logon"

DoCmd.OpenForm "frm_Main"

End Sub

Form frm_PassChange invokes if the password is "password" (plan to expand upon this to include user request changes as well). In this form I have the user enter the new password 2x for verification and then want to UPDATE the usertable with the new password, but this is not working:

Private Sub btnChangePass_Click()
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("X_tblUsers")

If Me.txtNewPass <> Me.txtNPConfirm Then
    Me.lblPassMismatch.Visible = True
    Me.txtNewPass.SetFocus
    Exit Sub
End If
Me.lblPassMismatch.Visible = False

TempVars("Password").Value = Me.txtNewPass.Value

CurrentDb.Execute "Update X_tblUsers SET X_tblUsers.Password = Value(" & Me.txtNewPass.Value & ")"

Me.Visible = False
Globals.Logging "PWChange"

End Sub

How do I properly perform the usertable update?

(tks to Steve Bishop for helping me get this far)

regardless admin or not, none of them should be allowed to bypass the shift key :) :) give them controlled access not full access! — Krish

Andre Andre · Accepted Answer · 2016-09-29T22:59:06

CurrentDb.Execute 
    "Update X_tblUsers SET X_tblUsers.Password = Value(" & Me.txtNewPass.Value & ")"

There are a few problems with that:

Value() doesn't belong there
Password is a string, so you need to quote it:
"Update X_tblUsers SET X_tblUsers.Password = '" & Me.txtNewPass.Value & "'"
Which will create problems if the password itself contains a quote, so:
"Update X_tblUsers SET X_tblUsers.Password = '" & Replace(Me.txtNewPass.Value, "'", "''") & "'"
Congratulations! You have just changed the password for all users! You need a WHERE clause:
"... WHERE UserName='" & theUserName & "'"

Also note that storing passwords as plain text is really bad. Please read about password hashing.

And about parameterized queries.

Access Login Form Password Change and Update : SQL / VBA

1 Answers