Security: Changing Passwords in Code with Form

Only Works With Access 2000 & Up; Uses DAO

Sunday, June 15, 2003 22:15:06

Related: Link to Steve Nyber's Handy Password Changer Form, which works with Access 97 and now (as of Jan 2003) works with 2000 as well

Link to My Form Which Handles Adding/Deleting User AND Password Administration On ONE Form

Derived from Microsoft Knowledge Base Article 200665.

Note: my suggestion is slightly different than the KB article suggestion, but is derived from it.

Using DAO (which appeared in Access 2000 for the 1st time), you can create a form for changing passwords. You can (a) create a form for the administrator to use for changing any user's passwords and (b)a page for allowing the currently logged-in user to change their password (and ONLY their password).

Create a new module. Name it basSecurityCode
Insert the following code


Public Sub ChangeResetPassword(StrAction As String, StrUsername As _
	String, StrAdminLogon As String, StrAdminPass As String, Optional _
	StrNewPassword As Variant)

'*******************************************************************************
' This function is for allowing the administrator to change anyone's password
'*******************************************************************************

Dim ws As Workspace
On Error GoTo ChangeResetPassword:

' Create a new Administrative Workspace. If The StrAction passed to the
' function is "Change" then change the Password of the User named in
' StrUsername to the password saved in StrNewPassword.
' If the StrAction passed is "Reset", Then reset the password of
' the User mentioned in StrUsername. If neither "Change" or "Reset"
' is passed to the function in the StrAction argument, inform the
' user of an error and exit the procedure.

Set ws = DBEngine.CreateWorkspace("AdminWorkspace", StrAdminLogon, _
StrAdminPass)
If StrAction = "change" Then

     If Not IsNull(StrNewPassword) Then
        ws.Users(StrUsername).NewPassword "", StrNewPassword
        MsgBox "Password Change Successful", vbOKOnly
     Else
         MsgBox "When Attempting to Change A User's Password, You " & _
           "Must Include a New Password", vbOKOnly
     End If

Else

If StrAction = "reset" Then
    'When the current user is in the admins group and reseting
    'his/her own password, the current password must be supplied.
    'In all other cases, the current user password is not needed
    'for a reset.
    If StrUsername = ws.UserName And StrAdminLogon = ws.UserName Then
           ws.Users(StrUsername).NewPassword StrAdminPass, ""
           MsgBox "Password Successfully Reset", vbOKOnly
    Else
           ws.Users(StrUsername).NewPassword "", ""
           MsgBox "Password Successfully Reset", vbOKOnly
    End If
Else
    MsgBox "You must Select a StrAction of either '" & "Change'" & _
      "' or '" & "Reset'.", vbOKOnly
End If

ws.Close
Set ws = Nothing
Exit Sub

ChangeResetPassword:
     MsgBox Err.Description
End Sub



Public Sub ChangeUserPassword(StrUsername As String, StrOldPassword _
	As String, StrNewPassword As String)

'*******************************************************************************
' This function is for allowing a user to change his/her own password ONLY
'*******************************************************************************

On Error GoTo ChangeUserPassword_Err:
DBEngine(0).Users(StrUsername).NewPassword StrOldPassword, _
  StrNewPassword

MsgBox "Password Change Successful", vbInformation
Exit Sub

ChangeUserPassword_Err:
    MsgBox Err.Description
End Sub

Save and Close the Module

Create the Form For Adminstrators

Create a new unbound form. Select the forms' properties and assign it the following attributes:

Tab	Property	Setting
Format	Caption	PW Form for Administrators
	Record Selectors	No
	Navigation Buttons	No
	Control Box	No
	Auto Center	Yes
	Min/Max Buttons	None
	Close Buttons	No

Create the following objects for your form:

Text Boxes (and Labels)

Name	Input Mask	Caption for Label	"Object" Name for Label
txtUserName		User Name	lblUserName
txtOldPassword	Password	Old Password	lblOldPassword
txtNewPassword	Password	New Password	lblNewPassword
txtVerifyPassword	Password	Verify Password	lblVerifyPassword
txtAdminUsername	Password	Admin User Name	lblAdminUserName
txtAdminPassword	Password	Admin Password	lblAdminPassword

Command Buttons

Name	Input Mask
cmdChangeAdmin	Change Password
cmdReset	Reset Password
cmdExit	E&xit

Save the form as frmChangePasswordsAdmin

Create the Form For Users (Shown)

Create a new unbound form. Select the forms' properties and assign it the following attributes:

Tab	Property	Setting
Format	Caption	PW Form for Administrators
	Record Selectors	No
	Navigation Buttons	No
	Control Box	No
	Auto Center	Yes
	Min/Max Buttons	None
	Close Buttons	No

Create the following objects for your form:

Text Boxes (and Labels)

Name	Input Mask	Caption for Label	"Object" Name for Label
txtUserName		User Name	lblUserName
txtOldPassword	Password	Old Password	lblOldPassword
txtNewPassword	Password	New Password	lblNewPassword
txtVerifyPassword	Password	Verify Password	lblVerifyPassword

Command Buttons

Name	Caption
cmdChange	Change Password
cmdExit	E&xit
cmdClear	&Clear

Select the text box txtUserName; selects its properties. Assign the following attributes:

Property Setting

Locked Yes
Control Source =CurrentUser()
Tab Stop No
Save the form as frmChangePasswordsUsers

Copy and paste the following code in both forms in the declarations section:

Option Explicit

Paste the following code in the form module of frmChangePasswordsAdmin:



Private Sub cmdChangeAdmin_Click()

On Error GoTo CmdChangeAdmin_err

' Test to make sure appropriate text boxes are filled in because this
' is an administrative function.

If Not IsNull(Me!txtAdminPassword) And Not _
  isNull(Me!txtAdminUsername) And Not IsNull(Me!txtUserName) Then
    If Me!txtNewPassword <> Me!txtVerifyPassword Then
       MsgBox "The New and Verified Passwords Do Not Match. Please" & _
         " Re-enter", vbCritical
       Me!txtNewPassword = ""
       Me!txtNewVerifyPassword = ""
       Me!txtNewPassword.SetFocus

   Else
       Call ChangeResetPassword("Change",Me!txtUserName, _
         me!txtAdminUsername, me!txtAdminPassword, Me!txtNewPassword)
   End If

Else

 MsgBox "The textboxes for UserName, New Password, Verified " & _
    "Password, Admin UserName and Admin Password must be complete " & _
    "for this function to operate correctly.", vbOKOnly

End If
Exit Sub

CmdChangeAdmin_err:
   MsgBox Err.Description

End Sub

Private Sub cmdReset_Click()

On Error GoTo cmdReset_err

' Test to make sure appropriate textboxes are filled in because this is
' an administrative function.

If Not IsNull(Me!txtAdminPassword) And Not _
  IsNull(Me!txtAdminUsername) And Not IsNull(Me!txtUserName) Then
  Call ChangeResetPassword("Reset", Me!txtUserName, _
  Me!txtAdminUsername, Me!txtAdminPassword)
Else
  MsgBox "The textboxes for UserName, New Password, Verified " & _
    "Password, Admin UserName and Admin Password must be complete " & _
    "for this function to operate correctly.", VBOKOnly
End If
Exit Sub

cmdReset_err:
  MsgBox Err.Description

End Sub

Private Sub cmdExit_Click()
DoCmd.Close
End Sub

Paste the following code into frmChangePasswordsUsers


Private Sub cmdChange_Click()
If IsNull(Me.txtNewPassword) Or IsNull(Me.txtOldPassword) Or IsNull(Me.txtVerifyPassword) Then
    MsgBox "You left a field blank; action cannot be completed", vbCritical, "Error"
    DoCmd.GoToControl "txtOldPassword"
    End
End If

Dim response As Integer, message As String
message = "Are you sure you wish to change your password (Y/N)?"
response = MsgBox(message, vbYesNo, "Password Change Confirmation")
If response = vbNo Then
    MsgBox "Password was NOT changed.", vbCritical, "Action Cancelled"
    End
End If

On Error GoTo cmdChange_err
' Make sure the password typed in the New Password text box
' matches what has been typed in the Verify Password text box.
If Me!txtNewPassword <> Me!txtVerifyPassword Then
   MsgBox "The New and Verified Passwords Do Not Match. " & _
     "Please Re-enter", vbCritical
   Me!txtNewPassword = ""
   Me!txtNewVerifyPassword = ""
   Me!txtNewPassword.SetFocus
Else
   ' Check to make sure the Old Password has been filled in and
   ' if it has, change the password.
   If IsNull(Me!txtOldPassword) Then
       MsgBox "Leaving the Old Password textbox empty will cause " & _
         "an error unless you currently have no password", vbOKOnly
     Call ChangeUserPassword(CurrentUser(), "", Me!txtNewPassword)
   Else
     Call ChangeUserPassword(CurrentUser(), Me!txtOldPassword, _
        Me!txtNewPassword)
   End If
End If

Exit Sub

cmdChange_err:
          MsgBox Err.Description
End Sub

Private Sub cmdClear_Click()
Me.txtNewPassword = Null
Me.txtOldPassword = Null
Me.txtVerifyPassword = Null
DoCmd.GoToControl "txtOldPassword"
End Sub

Private Sub cmdExit_Click()
OKToClose = True
DoCmd.Close
OKToClose = True
End Sub

Private Sub Form_Load()
Me.Caption = "Password Administration for " & CurrentUser()
End Sub


Private Sub txtNewPassword_AfterUpdate()
Me.txtNewPassword = StrConv(Me.txtNewPassword, vbLowerCase)
End Sub

Private Sub txtOldPassword_AfterUpdate()
Me.txtOldPassword = StrConv(Me.txtOldPassword, vbLowerCase)
End Sub

Private Sub txtVerifyPassword_AfterUpdate()
Me.txtVerifyPassword = StrConv(Me.txtVerifyPassword, vbLowerCase)
End Sub

Here are screenshots of how my particular password forms look. There are some differences between them and the ones you probably created, namely the checkbox for allowing the user to turn off the password input mask.

Property	Setting
Locked	Yes
Control Source	=CurrentUser()
Tab Stop	No