Password Expire In One Month

[Eranka]

Hi Guys

Private Sub Command12_Click()Dim User As String
Dim UserLevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String
Dim but As String
Dim SysID As String


If IsNull(Me.txtUserName) Then
 MsgBox "Please Enter User Name", vbInformation, "Username required"
 Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
 MsgBox "Please Enter Password", vbInformation, "Password required"
 Me.txtPassword.SetFocus
Else
 If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
 MsgBox "Invalid Username or Password!"
 Else
 TempID = Me.txtUserName.Value
 UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLevel = DLookup("[UserSecurity]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 ID = DLookup("[ID]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 but = "Login"
 SysID = Environ("username")
 CurrentDb.Execute "INSERT INTO UserLog (User_name,Log_Type, Log_Time, Log_Date,Computer_User)" & " VALUES ('" & Me.txtUserName & "','" & but & "',Now(),Now(),'" & SysID & "')"
 DoCmd.Close
 If (TempPass = "password") Then
 MsgBox "Please change Password", vbInformation, "New password required"
 DoCmd.OpenForm "frmUserinfo", , , "[ID] = " & ID
 Else
 'open different form according to user level
 If UserLevel = 1 Then ' for admin
 DoCmd.OpenForm "Navigation Form"
 ElseIf UserLevel = 2 Then ' for academic
 DoCmd.OpenForm "Navigation Form"
 ElseIf UserLevel = 3 Then ' for exam
 DoCmd.OpenForm "Navigation Form"
 Else
 DoCmd.OpenForm "Main_Menu_2"
 Forms![Navigation Form]![Text861] = UserLogin
 Forms![Main_Menu_2]!Command19.Enabled = False
 End If


 End If
 End If
End If
End Sub

i used above code for login, now i want password to be expired in 30 days and enter new password, how to do this part?

[Gicu]

Do you have a date field in your tblUser table that holds the PasswordDate? If no add it, then simply add the logic into your login procedure: you dim a datPassDate as Date variable and a intDaysOld as integer variable, get the value from the table using a dlookup like datPassDate =DLookup("[PasswordDate]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'"), then you check how old it is intDaysOld=Date()-datPassDate, if over 30 you open the change password form and exit the procedure.

Cheers,
Vlad

[Eranka]

HI

Where do i place the below code ?

intDaysOld=Date()-datPassDate,

[Eranka]

HI

Dim User As StringDim UserLevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String
Dim but As String
Dim SysID As String
Dim datPassDate As Date
Dim intDaysOld As Integer


intDaysOld = 1
If IsNull(Me.txtUserName) Then
 MsgBox "Please Enter User Name", vbInformation, "Username required"
 Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
 MsgBox "Please Enter Password", vbInformation, "Password required"
 Me.txtPassword.SetFocus
Else
 If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
 MsgBox "Invalid Username or Password!"
 Else
 TempID = Me.txtUserName.Value
 UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLevel = DLookup("[UserSecurity]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 ID = DLookup("[ID]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 datPassDate = DLookup("[PDate]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 but = "Login"
 SysID = Environ("username")
 CurrentDb.Execute "INSERT INTO UserLog (User_name,Log_Type, Log_Time, Log_Date,Computer_User)" & " VALUES ('" & Me.txtUserName & "','" & but & "',Now(),Now(),'" & SysID & "')"
 DoCmd.Close
 If (TempPass = "password") Then
 MsgBox "Please change Password", vbInformation, "New password required"
 DoCmd.OpenForm "frmUserinfo", , , "[ID] = " & ID
 ElseIf (intDaysOld > Date - datPassDate) Then
  MsgBox "Password Expired Please change Password", vbInformation, "New password required"
  DoCmd.OpenForm "frmUserinfo", , , "[ID] = " & ID
 Else
 'open different form according to user level
 If UserLevel = 1 Then ' for admin
 DoCmd.OpenForm "Navigation Form"
 ElseIf UserLevel = 2 Then ' for academic
 DoCmd.OpenForm "Navigation Form"
 ElseIf UserLevel = 3 Then ' for exam
 DoCmd.OpenForm "Navigation Form"
 Else
 DoCmd.OpenForm "Main_Menu_2"
 End If


 End If
 End If
End If

please see above code and let me know whether i have placed it in the correct places.

[Gicu]

Please see below:

Dim User As String
Dim UserLevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String
Dim but As String
Dim SysID As String
Dim datPassDate As Date
Dim intDaysOld As Integer






If IsNull(Me.txtUserName) Then
    MsgBox "Please Enter User Name", vbInformation, "Username required"
    Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
    MsgBox "Please Enter Password", vbInformation, "Password required"
    Me.txtPassword.SetFocus
Else
     If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
        MsgBox "Invalid Username or Password!"
     Else
        TempID = Me.txtUserName.Value
        UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
        UserLevel = DLookup("[UserSecurity]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
        TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
        UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
        ID = DLookup("[ID]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
        datPassDate = DLookup("[PDate]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
        intDaysOld = Date - Nz(datPassDate, Date)
        but = "Login"
        SysID = Environ("username")
        CurrentDb.Execute "INSERT INTO UserLog (User_name,Log_Type, Log_Time, Log_Date,Computer_User)" & " VALUES ('" & Me.txtUserName & "','" & but & "',Now(),Now(),'" & SysID & "')"
        DoCmd.Close
        
        If (TempPass = "password") Then
            MsgBox "Please change Password", vbInformation, "New password required"
            DoCmd.OpenForm "frmUserinfo", , , "[ID] = " & ID
        ElseIf intDaysOld > 30 Then
            MsgBox "Password Expired Please change Password", vbInformation, "New password required"
            DoCmd.OpenForm "frmUserinfo", , , "[ID] = " & ID
        Else
            'open different form according to user level
            If UserLevel = 1 Then ' for admin
                DoCmd.OpenForm "Navigation Form"
            ElseIf UserLevel = 2 Then ' for academic
                DoCmd.OpenForm "Navigation Form"
            ElseIf UserLevel = 3 Then ' for exam
                DoCmd.OpenForm "Navigation Form"
            Else
                DoCmd.OpenForm "Main_Menu_2"
            End If
        End If
    End If
End If

Also, if not already there you need to reset the PDate from frmUSerInfo when new password is entered.

Cheers,
Vlad

[Eranka]

Hi Gicu


awesome, that worked.


thanks alot mate.

[Gicu]

Glad to help!