Password change after x days

[ramez75]

Ok I have a database....And I have a Login (Username+Password) built in, a table with users, a form that open up of opening the database that asks for user name and password.....So thats all good

My question I would like to force the users to change their passwords every lets say 90 days or 60 days an x number of days. I just cant think of how to execute it.....

The idea I have is maybe add a date field and another criteria field that will be maybe a number or a future date to my tblSecurity (this table consist of ID, USername and Password fields)....

I also have a form to change password...I guess I need something to make the form popup when the users password expires.

So I guess when the user logs in the first time the database requires them to change the default password....I will need maybe to store the date when they do that and each time they login that dat will be subtracted from today date and checked against the criteria in tblSecurity and if doesnt meet then frmchangepassword will pop open

I hope I am making sense. So how can i make this to work the way I am thinking

thanks

 

[JANR]

See if this helps: http://www.access-programmers.co.uk/forums/showthread.php?t=111547

JR

 

[ramez75]

Thanks but what i was looking for how to enforce the user to change the password in X number of days

 

[boblarson]

Put a date field in your table where the usernames and passwords are stored and then when you open the database and it checks to make sure the username/password match it can also check to see if the datediff is a particular number of days old -

If DateDiff("d",VariableWithTheirDate, Date()) >= 90 Then... etc.

You will probably need to use a DLookup to get the date associated with that user account (hence my VariableWithTheirDate) and then when they change it on the form and submit it you would change the date in their record.

 

[ramez75]

Put a date field in your table where the usernames and passwords are stored and then when you open the database and it checks to make sure the username/password match it can also check to see if the datediff is a particular number of days old -

If DateDiff("d",VariableWithTheirDate, Date()) >= 90 Then... etc.

You will probably need to use a DLookup to get the date associated with that user account (hence my VariableWithTheirDate) and then when they change it on the form and submit it you would change the date in their record.

thats exactly what my thought process was going for I just didnt know how to implement it...I was aware of the darediff, I just didnt know how to capture the "date" they login to change their password.
So you are saying use the Dlookup function. If it is not to much to ask how will that be written. SO I have tblSecurity and I will add "VariableWithTheriDate" os I will add the datediff fucntion under my if condition in "frmUserLogon.", vbOKOnly + vbExclamation, "Change Password"
somthing like

If DateDiff("d",VariableWithTheirDate, Date()) >= 90 Then
Msgbox "Time to change your password"
DoCmd.OpenForm "frmUserLogonNew"
EndIf

Now how do I write the Dlookup to capture the date when the user change their password on the first time they login to the system and how to save it in tblSecurity --> VariableWithTheriDate"

Below is what I was thinking

Create a textbox in "frmUserLogonform"....this is the form where u change ur password when u login the first time to the database.
textbox with Control Source=VariableWithTheriDate and Visible = No and Default value = Date(). So I believe the current date will be saved in tblSecurity when its opened.

then I will use the above code to check

Is the logic workable???

 

[boblarson]

I believe you have it. The update would be accomplished using an Update query.

 

[ramez75]

Ok I tried what I said I wanted to do but for some reason the Defaultvalue= Date() on the textbox is not being saved in tblSecurity. What am I missing?

 

[boblarson]

Set it as the default on the field in the table, not on the text box.

 

[ramez75]

Set it as the default on the field in the table, not on the text box.

I tried the table, the problem with that it will add the Date() to a new record not to the existing record.......I need a command to transfer Date() to tblSecurity

 

[boblarson]

I tried the table, the problem with that it will add the Date() to a new record not to the existing record.......I need a command to transfer Date() to tblSecurity

It is SUPPOSED to work that way. You put the date as default for new records and for changes you use an update query.

 

[ramez75]

Ok what am I doing wrong...This what I have

I created an Update Query qrysecuritydate below is the SQL. Also added in tblSecurity a date/time field called VariableWithTheirDate

UPDATE tblSecurity INNER JOIN tblSecurityAccess ON tblSecurity.AccessID = tblSecurityAccess.AccessID SET tblSecurity.VariableWithTheriDate = Date()
WHERE (((tblSecurityAccess.AccessTitle)="User"));

I tested the Update query and tbl Security dates are modified.

Next

I put an invisible textbox in frmUserlogonNew, Control SOurce = VariableWithTheriDate

Next

Under the OK Click Event on frmUserLogonNew below is the code

    Dim stDocName As String
    Dim stLinkCriteria As String
    
    If Not IsNull(Me.txtNPassword) And Not IsNull(Me.txtCPassword) Then
        If Me.txtCPassword = Me.txtNPassword Then
            stDocName = "qryUpdateUserPassowrd"
            DoCmd.SetWarnings False
            DoCmd.OpenQuery stDocName, acNormal, acEdit
            DoCmd.SetWarnings True
            MsgBox "Password was successfully change", vbOKOnly + vbInformation, "Password Changed"
            stDocName = "frmStartup"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        ElseIf DateDiff("d", [VariableWithTheirDate], Date) >= 2 Then
            stDocName = "qrysecuritydate"
            DoCmd.SetWarnings False
            DoCmd.OpenQuery stDocName, acNormal, acEdit
            DoCmd.SetWarnings True
            MsgBox "Password was successfully change", vbOKOnly + vbInformation, "Password Changed"
            stDocName = "frmStartup"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        Else
            MsgBox "New password and confirmation do not match." & _
            Chr(13) & "Please enter the correct password and confirm", vbOKOnly + vbInformation, "Incorrect Password"
        End If
    Else
        MsgBox "You left the User Name and/or Password blank." & Chr(13) & _
        "Please enter the correct User Name and Password or " & Chr(13) & _
        "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Change Failed"
    End If

Next

Under the frmUserlogon OK CLick event I have the below code

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstV As Recordset
    Dim stDocName As String
    Dim stLinkCriteria As String
    
    Set db = currentdb()
    Set rst = db.OpenRecordset("tblSecurity", dbOpenDynaset)
    
    If Not IsNull(Me.txtUser) And Not IsNull(Me.txtPassword) Then
        rst.FindFirst "Password = '" & Me.txtPassword & "'" & " And UserID = '" & Me.txtUser & "'"
    
        If rst.NoMatch Then
            MsgBox "You entered the wrong User Name or Password." & Chr(13) & _
            "Please enter the correct User Name and Password or " & Chr(13) & _
            "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
        ElseIf Me.txtPassword = "password" Then
            MsgBox "This is the first time using the database or your passowrd has been reset." & Chr(13) & _
            "You must change your password before you can enter the database.", _
            vbOKOnly + vbExclamation, "Change Password"
            stDocName = "frmUserLogonNew"
            stLinkCriteria = "[UserID]=" & "'" & Me![txtUser] & "'"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
[COLOR=red]        ElseIf DateDiff("d", [VariableWithTheirDate], Date) >= 2 Then
[/COLOR]            MsgBox "Time to change your password." & Chr(13) & _
            "You must change your password before you can enter the database.", _
            vbOKOnly + vbExclamation, "Change Password"
            stDocName = "frmUserLogonNew"
            stLinkCriteria = "[UserID]=" & "'" & Me![txtUser] & "'"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        Else
            stDocName = "frmStartup"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        End If
    Else
        MsgBox "You left the User Name and/or Password blank." & Chr(13) & _
        "Please enter the correct User Name and Password or " & Chr(13) & _
        "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
    End If
    
    With User
        .AccessID = rst.Fields("AccessID")
        .ViewID = rst.Fields("ViewID")
        .Active = rst.Fields("Active")
        .Password = rst.Fields("Password")
        .SecurityID = rst.Fields("SecurityID")
        .UserID = rst.Fields("UserID")
    End With
    
    rst.close

but the thing is not working, I am getting the error as soon as the database open and i type the password
"Database cant find the field '|' referred to in your experession"

I dont know what that means.....It seems could be coming from the above red highlighted line

What am I doing wrong

 

[boblarson]

You don't put a variable inside square brackets. You leave it all by itself with no brackets, quotes, octothorpes, etc. Nothing.

 

[ramez75]

You don't put a variable inside square brackets. You leave it all by itself with no brackets, quotes, octothorpes, etc. Nothing.

The reason I used the brackets is if I dont I get a Compile error (Variable not defined)

How do I define the variable "VariableWithTheirDate" which is in tblSecurity and the textfield is located on "frmUserLogonNew".

I use in Datediff ("d", VariableWithTheirDate, Date) in "frmUserLogon"

Should I add it to the "rst.FindFirst" statement

Thanks

 

[boblarson]

If the text field is on the form, just use the form reference (as long as it stays open, you can hide it, it is valid).

ElseIf DateDiff("d", Forms!YourFormNameHere.YourTextBoxNameHere, Date) >= 2 Then

 

[ramez75]

If the text field is on the form, just use the form reference (as long as it stays open, you can hide it, it is valid).

ElseIf DateDiff("d", Forms!YourFormNameHere.YourTextBoxNameHere, Date) >= 2 Then

It is on frmUserLogonNew which is not opened yet when frmUserLogon is open.

So when I use the above which I tried I get
"Database cant find the form frmUserLogonNew referred in the macro expression or VBA..... I believe this is coz the form is not open yet.

What can I do to get around it

 

[boblarson]

So then you would need to get it from the table using a DLookup most likely:

Dim dteDate As Date
 
dteDate = DLookup("DateFieldNameHere", "TableNameHere", "[UserID]=" & Me!UserID)
 
[COLOR=#ff0000]ElseIf DateDiff("d", [/COLOR][B][COLOR=blue]dteDate[/COLOR][/B][COLOR=#ff0000], Date) >= 2 Then[/COLOR]
[COLOR=#ff0000]

[/COLOR]

 

[ramez75]

So then you would need to get it from the table using a DLookup most likely:

Dim dteDate As Date
 
dteDate = DLookup("DateFieldNameHere", "TableNameHere", "[UserID]=" & Me!UserID)
 
[COLOR=#ff0000]ElseIf DateDiff("d", [/COLOR][B][COLOR=blue]dteDate[/COLOR][/B][COLOR=#ff0000], Date) >= 2 Then[/COLOR]

Bob,

The UserID is that the field in the table that is the considered the Primary Key......Or is it the USERID, I have a field called USERID

The Dlookup makes sense but i get USERID us not referenced but its there weird

 

[boblarson]

UserID or USERID makes no difference unless you have the binary option set which is highly unlikely.

How about uploading a copy of your database (with bogus data of course) - at least the login parts including the applicable table/tables.

 

[ramez75]

boblarson,

I have attached the database, had to delete unnecessary items to make it smaller.
I have for testing purposes two users

Username - admin; password - admin
Username - user; password - user

 

[boblarson]

Here you go - it is revised and works.

I made several changes so you are going to want to look at it closely to see what I did.

1. I changed the field in the table to reflect what the date was. I only gave you the Variable...etc. as an example for you to rename to something applicable. So now it is like DatePwdChanged or something like that.

2. The update query that updates the date is no longer needed because you had the change password form bound to the table so we could just reference the text box.

3. The IF and ElseIF's have been changed to put them in an order that it needs so it hits the code for changing the password. Before it wasn't even getting there because of the comparison between the new and the old password so the date wasn't getting updated.

So here you go.