(Updated October 14, 2003 to allow administrators to change anybody's password.)
For an Access database in which User-Level Security has been activated, some way must be provided to let users change their logon passwords. You could give them a way to get to the User and Group Accounts dialog (Tools|Security menu), but that displays their user/group associations on the default page, which can be confusing. As an alternative, you can provide a small form which you can open as a dialog from a menu item, command button, etc.
The form I use is described below. Properties are given for the form and for each of its controls, in the order the properties appear in the Properties sheet. Properties for which the default settings are appropriate are not listed.
Property names marked with "*" are important and should be entered as shown. The other properties will give the form a pleasing layout and size, but don't affect its operation. If you prefer to design your own form layout, you can ignore these extra properties.
Note that this form allows members of the Admins group in the current workgroup information file to change the password of any user. (There's no need to worry about unauthorized users getting into the code behind the form to give themselves this ability, however; if they try they'll get a runtime error. This form doesn't circumvent or modify any permissions established in the workgroup file.)
Form: frmChangePassword
=======================
*Record Source: (blank)
Caption: "Change Password"
*Scroll Bars: Neither
*Record Selectors: No
*Navigation Buttons: No
*Auto Center: Yes
*Pop Up: Yes
*Modal: Yes
*Border Style: Dialog
*Control Box: No
*Min Max Buttons: None
*Close Button: No
Width: 3.6"
*On Open: "[Event Procedure]"
Section: Detail0
================
Height: 1.4"
Back Color: -2147483633
Text Box: txtUserName
=====================
*Enabled: No
*Locked: Yes
Left: 1"
Top: 0.1"
Width: 1.3083"
Height: 0.1875"
Back Style: Transparent
Border Style: Transparent
Font Name: Tahoma
Font Size: 8
(attached label properties)
Caption: "User Name:"
Left: 0.1"
Top: 0.1"
Width: 0.6354"
Height: 0.1667"
Back Style: Transparent
Font Name: Tahoma
Font Size: 8
Text Box: txtOldPW
==================
*Input Mask: Password
*Tab Index: 1
Left: 1"
Top: 0.4"
Width: 1.3083"
Height: 0.1875"
Back Color: -2147483643
Font Name: Tahoma
Font Size: 8
(attached label properties)
Caption: "&Old Password:"
Left: 0.1"
Top: 0.4"
Width: 0.7708"
Height: 0.1667"
Font Name: Tahoma
Font Size: 8
Text Box: txtNewPW
==================
*Input Mask: Password
*Tab Index: 2
Left: 1"
Top: 0.7"
Width: 1.3083"
Height: 0.1875"
Back Color: -2147483643
Font Name: Tahoma
Font Size: 8
(attached label properties)
Caption: "&New Password:"
Left: 0.1"
Top: 0.7"
Width: 0.8229"
Height: 0.1667"
Font Name: Tahoma
Font Size: 8
Text Box: txtVerify
==================
*Input Mask: Password
*Tab Index: 3
Left: 1"
Top: 1"
Width: 1.3083"
Height: 0.1875"
Back Color: -2147483643
Font Name: Tahoma
Font Size: 8
(attached label properties)
Caption: "&Verify:"
Left: 0.1"
Top: 1"
Width: 0.3854"
Height: 0.1667"
Font Name: Tahoma
Font Size: 8
Combo Box: cboUserName
======================
*Row Source Type: "Value List"
*Limit To List: Yes
Left: 1" *Note: These 4 should be the same as txtUserName
Top: 0.1"
Width: 1.4583"
Height: 0.1875"
Back Color: -2147483643
Font Name: Tahoma
Font Size: 8
*After Update: "[Event Procedure]"
(attached label properties)
Caption: "&User Name:"
Left: 0.1" *Note: These 4 should be the same as txtUserName's label
Top: 0.1"
Width: 0.6354"
Height: 0.1667"
Back Style: Transparent
Font Name: Tahoma
Font Size: 8
Command Button: cmdOK
=====================
*Caption: OK
*Default: Yes
Tab Index: 4
Left: 2.6042"
Top: 0.1458"
Width: 0.7958"
Height: 0.2396"
*On Click: "[Event Procedure]"
Command Button: cmdCancel
=========================
*Caption: Cancel
*Cancel: Yes
Tab Index: 5
Left: 2.6042"
Top: 0.4792"
Width: 0.7958"
Height: 0.2396"
*On Click: "[Event Procedure]"
Form Module:
Code:
'==================================================================='
' '
' Change Password Form '
' '
'==================================================================='
' Open this form as a dialog whenever the user needs to change his/her password.
' Members of the Admins group are allows to change any user's password.
Option Compare Database
Option Explicit
Dim strUserName As String
Private Sub cboUserName_AfterUpdate()
strUserName = cboUserName
End Sub
Private Sub cmdCancel_Click()
DoCmd.Close
End Sub
Private Sub cmdOK_Click()
Dim strOldPW As String, strNewPW As String, strVerifyPW As String
strOldPW = Nz(txtOldPW)
strNewPW = Nz(txtNewPW)
strVerifyPW = Nz(txtVerify)
If strNewPW <> strVerifyPW Then
txtNewPW.SetFocus
Beep
MsgBox "The New Password does not match the Verify password" _
& vbCrLf & vbCrLf & "Verify the password by retyping it in the " _
& "Verify box and clicking OK", vbExclamation
Exit Sub
End If
On Error GoTo ErrorHandler
DBEngine(0).Users(strUserName).NewPassword strOldPW, strNewPW
MsgBox "The password has been changed", vbInformation
DoCmd.Close
ErrorExit:
Exit Sub
ErrorHandler:
If Err.Number = 3033 Then
Beep
MsgBox "The password you entered into the Old Password box is incorrect." _
& vbCrLf & vbCrLf _
& "Please enter the correct password for this account.", vbInformation
txtOldPW.SetFocus
Resume ErrorExit
Else
Beep
MsgBox "Run time error (" & Err.Number & "):" & vbCrLf & vbCrLf _
& Err.Description, vbExclamation
DoCmd.Close
End If
End Sub
Private Sub Form_Open(Cancel As Integer)
strUserName = CurrentUser()
If UserIsInGroup(strUserName, "Admins") Then
cboUserName.RowSource = UserNameList()
cboUserName = strUserName
cboUserName.Visible = True
txtUserName.Visible = False
Else
txtUserName = strUserName
txtUserName.Visible = True
cboUserName.Visible = False
End If
End Sub
Private Function UserIsInGroup(UserName As String, GroupName As String) As Boolean
Dim grp As DAO.Group
On Error Resume Next
Set grp = DBEngine(0).Users(UserName).Groups(GroupName)
UserIsInGroup = Not grp Is Nothing
Set grp = Nothing
End Function
Private Function UserNameList()
Dim usr As DAO.User
Dim strResult As String
For Each usr In DBEngine(0).Users
If usr.Name <> "Creator" And usr.Name <> "Engine" Then _
strResult = strResult & usr.Name & ","
Next usr
UserNameList = Left$(strResult, Len(strResult) - 1)
End Function
