Password Expiration

[owuraku]

I have a table with the following fields: usernames; passwords; and passworddates. When a user logs into my database it pulls up information from this table and authenticates and subsequently allows or disallows user access. Everything works fine up to this point, but I need to include an additional functionality to my login code. Since my department recertifies user access every 60 days, I want to create some code which will alert user 5 days prior to the 60-day limit that their password is about to expire so they should contact their DBA. If no action has been taken by the user after the 60 days I want to deny the said user access to the database with a message saying something like "password expired".

[ADezii]

The general logic could be something similar to this:

Dim varExpirationDate As Variant
Dim intDaysUntilPwdExpire As Integer

If IsNull(Me![txtUserName]) Then Exit Sub

varExpirationDate = DLookup("[Passworddates]", "tblUsers", "[Usernames] = '" & _
                              Me![txtUserName] & "'")

If Not IsNull(varExpirationDate) Then
  intDaysUntilPwdExpire = DateDiff("d", Now(), CDate(varExpirationDate))
  Select Case intDaysUntilPwdExpire
    Case Is <= 5
      MsgBox "You Password will expire in " & intDaysUntilPwdExpire & " days, please " & _
             "contact the Database Administrator", vbExclamation, "Password Expiration Notice"
    Case Is > 60
      'Deny User access to the DB
      MsgBox "Outta here!"
    Case Else
      'Password will expire between 6 and 60 days, do nothing
  End Select
Else
  MsgBox "The User Name [" & Me![txtUserName] & "] does not exist!", _
                        vbExclamation, "Invalid User Name"
End If

[owuraku]

The general logic could be something similar to this:

Dim varExpirationDate As Variant
Dim intDaysUntilPwdExpire As Integer

If IsNull(Me![txtUserName]) Then Exit Sub

varExpirationDate = DLookup("[Passworddates]", "tblUsers", "[Usernames] = '" & _
                              Me![txtUserName] & "'")

If Not IsNull(varExpirationDate) Then
  intDaysUntilPwdExpire = DateDiff("d", Now(), CDate(varExpirationDate))
  Select Case intDaysUntilPwdExpire
    Case Is <= 5
      MsgBox "You Password will expire in " & intDaysUntilPwdExpire & " days, please " & _
             "contact the Database Administrator", vbExclamation, "Password Expiration Notice"
    Case Is > 60
      'Deny User access to the DB
      MsgBox "Outta here!"
    Case Else
      'Password will expire between 6 and 60 days, do nothing
  End Select
Else
  MsgBox "The User Name [" & Me![txtUserName] & "] does not exist!", _
                        vbExclamation, "Invalid User Name"
End If

THANK YOU ADEZII!!!! It's working like a charm. Hey, I have a question basing off the one I asked earlier...my manager wants the database to send him notification in his email 5 days prior to the expiration of user passwords. In the email there could be an HTML-based message with the list of users and a checkbox by each name asking him if he wishes to recertify user access. If he checks a checkbox by a user name, the user retains access, if he checks off the checkbox, the user is denied access. Alternatively, he could simply receive an email stating that passwords of users would be expiring in 5 days and that to go a specific form in the database where the recertification can be done. This form should contain the names and checkboxes like I described in the email with two buttons...one that checks all and one that unchecks all. A check would grant access, no check means no access.

[ADezii]

THANK YOU ADEZII!!!! It's working like a charm. Hey, I have a question basing off the one I asked earlier...my manager wants the database to send him notification in his email 5 days prior to the expiration of user passwords. In the email there could be an HTML-based message with the list of users and a checkbox by each name asking him if he wishes to recertify user access. If he checks a checkbox by a user name, the user retains access, if he checks off the checkbox, the user is denied access. Alternatively, he could simply receive an email stating that passwords of users would be expiring in 5 days and that to go a specific form in the database where the recertification can be done. This form should contain the names and checkboxes like I described in the email with two buttons...one that checks all and one that unchecks all. A check would grant access, no check means no access.

Create a Query that will generate a list of all Users whose Passwords will expire within 5 days from the Current Date, then E-Mail the Query to your Boss, as in:

DoCmd.SendObject acQuery, "qryPasswordsAboutToExpire", "HTML(*.html)", "MyBoss@aol.com", "", "", _
                          "Users With Passwords About to Expire", "The Passwords of the following " & _
                          "Users will expire within 5 days!", True, ""