Creating a form to change user passwords

[Darren]

In a secure database, I wish to create a form that users can use to change
their passwords. I created a standard form with three unbound fields and a
command button. The intention was that the user would enter their old
password in field 1, the new password in field 2 then confirm it in field 3.
They would then click on the "Change Password" command button and voila!

The result was not what I wanted. All I got was an error message regards
objects not defined etc (at the first Dim statement). I checked the
helpfile re examples of cose used for changing passwords and even by copying
and pasting, they too failed with the same error.

Below is the code I used:

Sub Change_Password()

Dim CrntUser As User
Dim OldPW As String
Dim NewPW As String
Dim ConfPW As String

Set CrntUser = CurrentUser
OldPW = Me.txtOldPassword
NewPW = Me.txtNewPassword
ConfPW = Me.txtConfirmPassword

' Check that new password and confirmation matches
If NewPW <> ConfPW Then
MsgBox "The confirmation of password does not match, please
re-enter", vbExclamation + vbOKOnly, "Password Error - No Match"
Me.txtNewPassword = ""
Me.txtConfirmPassword = ""
DoCmd.GoToControl "Me.txtNewPassword"
Exit Sub
End If

' Check that the new password is greater that 6 characters
If Len(NewPW) < 6 Then
MsgBox "Your password must be a minimum of 6 characters long, please
re-enter", vbExclamation + vbOKOnly, "Password Error - Too Short"
Me.txtNewPassword = ""
Me.txtConfirmPassword = ""
DoCmd.GoToControl "Me.txtNewPassword"
Exit Sub
End If

' Check that the password does not exceed 14 characters
If Len(NewPW) > 14 Then
MsgBox "Your password can be no more than 14 characters long, please
re-enter", vbExclamation + vbOKOnly, "Password Error - Too Long"
Me.txtNewPassword = ""
Me.txtConfirmPassword = ""
DoCmd.GoToControl "Me.txtNewPassword"
Exit Sub
End If

CrntUser.NewPassword OldPW, NewPW
MsgBox "Password change successful", vbInformation + vbOKOnly,
"Change Confirmed"

End Sub


Any ideas?

Regards
Darren

 

[Andy Cole]

Darren

Try this;

Sub Change_Password()

Dim CrntUser As String
Dim OldPW As String
Dim NewPW As String

On Error GoTo Err_Change_Password

' Get the User
CrntUser = CurrentUser()
' Check for 'empty' values
If Nz(Me.txtNewPassword, "") = "" Then
MsgBox "The 'New Password' may not be empty." & vbCrLf & vbCrLf & _
"Please try again.", vbExclamation, "Missing Data"
Me.txtNewPassword.SetFocus
Exit Sub
ElseIf Nz(Me.tbConfirmPassword, "") = "" Then
MsgBox "The 'Confirm Password' may not be empty." & vbCrLf & vbCrLf
& _
"Please try again.", vbExclamation, "Missing Data"
Me.txtConfirmPassword.SetFocus
Exit Sub
ElseIf ((Len(Me.txtNewPassword.Value) < 6) Or (Len(Me.txtNewPassword) >
14)) Then
MsgBox "The 'New Password' must be between 6 and 14" & vbCrLf & _
"characters. Please adjust your entry.", _
vbExclamation, "Invalid Length"
Me.txtNewPassword.SetFocus
Exit Sub
ElseIf Me.txtNewPassword.Value <> Me.txtConfirmPassword.Value Then
MsgBox "Please re-enter your new Password", vbExclamation,
"Verification Error"
Me.txtConfirmPassword.Value = ""
Me.txtNewPassword.Value = ""
Me.txtNewPassword.SetFocus
Exit Sub
Else
NewPW = Me.txtNewPassword.Value
OldPW = Nz(Me.txtOldPassword.Value, "")
DBEngine.Workspaces(0).Users(CrntUser).NewPassword OldPW, NewPW
End If

Exit_Change_Password:
ExitSub

Err_Change_Password:
MsgBox "An error occurred: " & Err.Number & " (" & Err.Description & ")"
Resume Exit_Change_Password

End Sub

HTH

Andy

 

[Darren]

Andy

You are the man !!

Many thanks, it works a treat.

Regards
Darren

 

[Andy Cole]

Glad to be of help

Andy

Andy

You are the man !!

Many thanks, it works a treat.

Regards
Darren


&

 

[Darren]

One other thing that would be useful, if it is possible to do. If a user
account is created with no password, can the database force the user to
change the password when they log into it?

Regards
Darren

 

[Andy Cole]

Darren

Yes it can be done but there are no built-in methods you can use as Access
dies *not* provide methods to read a User's password - only to set it when
creating a User and to change it with the code I posted. What I've done in
the past is to create a table containing an autonumber ID, the User's Login
Name and a Yes/No field called InitPW. In my form to maintain users, a new
user account creates an entry in this table and sets the InitPW field. In
the code that runs immediately after logon, I run a check against this table
and if InitPW is set, I call the Change Password routine. This also updates
the 'RegisteredUsers' table, setting InitPW false if the user successfully
changes their password and quits the app if not.

HTH

Andy

 

[Darren]

Now much closer to the end regards passwords etc. One final item is how can
I get the PasswordSet field in the table to change from N to Y once the user
has set their password at logon? I have tried a reverse use of the DLookup
line but did not hold any hope in it working.......bombed out with "Object
Required" message and nothing else.

Having defined PWCheck as a string value I used the following line

PWCheck = "Y"
DLookup("[PasswordSet]", "tblPasswordCheck", "[Username]=" & Chr(34)
& User & Chr(34)) = PWCheck

Apologies for so many questions but I am learning now.

Regards
Darren

Darren

The criteria on your Dlookup is incorrect. Use this instead;

PWCheck = DLookup("[PasswordSet]", "tblPasswordCheck", "[Username]=" &
Chr(34) & User & Chr(34))

The way your code was written would mean that you would be checking for a
Username called "User" rather than the *value* of the variable User

HTH

Andy

Andy

I have created a table and in it I then manually add the usernames of those
who will access the database, making sure the PasswordSet field defaults to
N (1 character text field, also tried standard yes/no).

Using a splash screen when the database first starts, I added the following
code to the OnOpen Event

Private Sub Form_Open(Cancel As Integer)

Dim PWCheck As String
Dim User As String

User = CurrentUser()
PWCheck = DLookup("[PasswordSet]", "tblPasswordCheck",
"[Username]=User")

If PWCheck = "N" Then
MsgBox "You must set a password before proceeding.", vbCritical,
"Password Required"
DoCmd.OpenForm "frmChangePassword", acNormal, , , acFormAdd,
acWindowNormal
End If

End Sub


Everything but the DLookup line works. If I keep in the criteria of
"[Username]=User" (also tried "[Username]=Currentuser()" I get a message
regards Invalid use of Null. I put in break stops in order to debug

and,

in
the debug window, the variable PWChar was indeed null, User picked up the
CurrentUser fine.

If I remove the criteria and leave it as "", the DLookup command picks out
the very first record it comes to.

I would be grateful for your further assistance. Whilst I am converse with
Access, I am still getting to grips with "more than basic" vba coding.

Also, is there an easier way to do this, thus avoid making matters hard for
myself?

Regards
Darren



done
in a
new vbCrLf

confirm

it button
and

 

[Andy Cole]

Hi Darren

No problem about asking questions - I wish I'd known about this NG when I
started learning! The Dlookup cannot be used this way. The easiest method
is to run an update query. Add the following to the ChangePassword routine;

immediately after the DBEngine.Workspaces(0).Users.... line, add these;

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblPasswordCheck " & _
"SET PasswordSet = True WHERE Username=" & _
Chr(34) & User & Chr(34) & _
" WITH OWNERACCESS OPTION;"
DoCmd.SetWarnings True

The SetWarnings False line switches off the messages that Access would
normally display when running an 'action' query. If you use it elsewhere,
remember to switch it back to True afterwards.

HTH

Andy

Now much closer to the end regards passwords etc. One final item is how can
I get the PasswordSet field in the table to change from N to Y once the user
has set their password at logon? I have tried a reverse use of the DLookup
line but did not hold any hope in it working.......bombed out with "Object
Required" message and nothing else.

Having defined PWCheck as a string value I used the following line

PWCheck = "Y"
DLookup("[PasswordSet]", "tblPasswordCheck", "[Username]=" & Chr(34)
& User & Chr(34)) = PWCheck

Apologies for so many questions but I am learning now.

Regards
Darren

Darren

The criteria on your Dlookup is incorrect. Use this instead;

PWCheck = DLookup("[PasswordSet]", "tblPasswordCheck", "[Username]=" &
Chr(34) & User & Chr(34))

The way your code was written would mean that you would be checking for a
Username called "User" rather than the *value* of the variable User

HTH

Andy

Andy

I have created a table and in it I then manually add the usernames of those
who will access the database, making sure the PasswordSet field

defaults
to

N (1 character text field, also tried standard yes/no).

Using a splash screen when the database first starts, I added the following
code to the OnOpen Event

Private Sub Form_Open(Cancel As Integer)

Dim PWCheck As String
Dim User As String

User = CurrentUser()
PWCheck = DLookup("[PasswordSet]", "tblPasswordCheck",
"[Username]=User")

If PWCheck = "N" Then
MsgBox "You must set a password before proceeding.", vbCritical,
"Password Required"
DoCmd.OpenForm "frmChangePassword", acNormal, , , acFormAdd,
acWindowNormal
End If

End Sub


Everything but the DLookup line works. If I keep in the criteria of
"[Username]=User" (also tried "[Username]=Currentuser()" I get a message
regards Invalid use of Null. I put in break stops in order to debug

and,

in
the debug window, the variable PWChar was indeed null, User picked up the
CurrentUser fine.

If I remove the criteria and leave it as "", the DLookup command picks out
the very first record it comes to.

I would be grateful for your further assistance. Whilst I am converse with
Access, I am still getting to grips with "more than basic" vba coding.

Also, is there an easier way to do this, thus avoid making matters

hard
for

myself?

Regards
Darren



Darren

Yes it can be done but there are no built-in methods you can use as Access
dies *not* provide methods to read a User's password - only to set

it
when

creating a User and to change it with the code I posted. What I've done
in
the past is to create a table containing an autonumber ID, the User's
Login
Name and a Yes/No field called InitPW. In my form to maintain

users,

a field.
In user
to

vbCrLf

& can
use confirm

 

[Darren]

Andy

I found another way using hidden fields on the Change Password form and a
simple Query.

The query (username critera = CurrentUser()) contains the fields from the
Password checking table and, when the user clicks on the "Change Password"
button, as well as the normal stuff, the code will also update the hidden
field txtPWset from N to Y.

Your way requires less objects so I think I take it on board.

Many thanks
Darren

Hi Darren

No problem about asking questions - I wish I'd known about this NG when I
started learning! The Dlookup cannot be used this way. The easiest method
is to run an update query. Add the following to the ChangePassword routine;

immediately after the DBEngine.Workspaces(0).Users.... line, add these;

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblPasswordCheck " & _
"SET PasswordSet = True WHERE Username=" & _
Chr(34) & User & Chr(34) & _
" WITH OWNERACCESS OPTION;"
DoCmd.SetWarnings True

The SetWarnings False line switches off the messages that Access would
normally display when running an 'action' query. If you use it elsewhere,
remember to switch it back to True afterwards.

HTH

Andy

Now much closer to the end regards passwords etc. One final item is how can
I get the PasswordSet field in the table to change from N to Y once the user
has set their password at logon? I have tried a reverse use of the DLookup
line but did not hold any hope in it working.......bombed out with "Object
Required" message and nothing else.

Having defined PWCheck as a string value I used the following line

PWCheck = "Y"
DLookup("[PasswordSet]", "tblPasswordCheck", "[Username]=" & Chr(34)
& User & Chr(34)) = PWCheck

Apologies for so many questions but I am learning now.

Regards
Darren

Darren

The criteria on your Dlookup is incorrect. Use this instead;

PWCheck = DLookup("[PasswordSet]", "tblPasswordCheck",

"[Username]="

&

for

a

Username called "User" rather than the *value* of the variable User

HTH

Andy

Andy

I have created a table and in it I then manually add the usernames of
those
who will access the database, making sure the PasswordSet field defaults
to
N (1 character text field, also tried standard yes/no).

Using a splash screen when the database first starts, I added the
following
code to the OnOpen Event

Private Sub Form_Open(Cancel As Integer)

Dim PWCheck As String
Dim User As String

User = CurrentUser()
PWCheck = DLookup("[PasswordSet]", "tblPasswordCheck",
"[Username]=User")

If PWCheck = "N" Then
MsgBox "You must set a password before proceeding.", vbCritical,
"Password Required"
DoCmd.OpenForm "frmChangePassword", acNormal, , , acFormAdd,
acWindowNormal
End If

End Sub


Everything but the DLookup line works. If I keep in the criteria of
"[Username]=User" (also tried "[Username]=Currentuser()" I get a message
regards Invalid use of Null. I put in break stops in order to debug and,
in
the debug window, the variable PWChar was indeed null, User picked

up
the

CurrentUser fine.

If I remove the criteria and leave it as "", the DLookup command

picks
out

the very first record it comes to.

I would be grateful for your further assistance. Whilst I am converse
with
Access, I am still getting to grips with "more than basic" vba coding.

Also, is there an easier way to do this, thus avoid making matters hard
for
myself?

Regards
Darren



Darren

Yes it can be done but there are no built-in methods you can use as
Access
dies *not* provide methods to read a User's password - only to set it
when
creating a User and to change it with the code I posted. What

I've
done

in
the past is to create a table containing an autonumber ID, the User's
Login
Name and a Yes/No field called InitPW. In my form to maintain

users,

a
new
user account creates an entry in this table and sets the InitPW field.
In
the code that runs immediately after logon, I run a check against this
table
and if InitPW is set, I call the Change Password routine. This also
updates
the 'RegisteredUsers' table, setting InitPW false if the user
successfully
changes their password and quits the app if not.

HTH

Andy

One other thing that would be useful, if it is possible to do.

If

a vbCrLf

14"

&

vbInformation

+