none
[VBA+Access 2003] How to sent password by Outlook in forgot password form? RRS feed

  • Question

  • Hi,

    here is the code that I use to run my logon form:

    SELECT [tblEmployees].[lngEmpID], [tblEmployees].[strEmpName]
    FROM tblEmployees;
    

    Private Sub cboEmployee_AfterUpdate()
    'After selecting user name set focus to password field
      Me.txtPassword.SetFocus
    End Sub
            
    Private Sub cmdLogin_Click()
    
    'Check to see if data is entered into the UserName combo box
    
      If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
       MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboEmployee.SetFocus
        Exit Sub
      End If
    
      'Check to see if data is entered into the password box
    
      If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
       MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
      End If
    
      'Check value of password in tblEmployees to see if this
      'matches value chosen in combo box
    
      If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
          "[lngEmpID]=" & Me.cboEmployee.Value) Then
    
        lngMyEmpID = Me.cboEmployee.Value
    
        'Close logon form and open splash screen
    
        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "frmSplash_Screen"
    
      Else
       MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
          "Invalid Entry!"
        Me.txtPassword.SetFocus
      End If
    
      'If User Enters incorrect password 3 times database will shutdown
    
      intLogonAttempts = intLogonAttempts + 1
      If intLogonAttempts > 3 Then
       MsgBox "You do not have access to this database.Please contact admin.", _
            vbCritical, "Restricted Access!"
        Application.Quit
      End If
    
    End Sub
    
    'Check to see if data is entered into the password box
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
      Me.txtPassword.SetFocus
      Exit Sub
    End If
    
    
     Error message stating that a User Password must be entered.
    
    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box
    
    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
                     "[lngEmpID]=" & Me.cboEmployee.Value) Then
    
      lngMyEmpID = Me.cboEmployee.Value
    
      'Close logon form and open splash screen
    
      DoCmd.Close acForm, "frmLogon", acSaveNo
      DoCmd.OpenForm "frmSplash_Screen"
    
    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
      Me.txtPassword.SetFocus
    End If
    
    'If User Enters incorrect password 3 times
    'database will shutdown
    
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
          vbCritical, "Restricted Access!"
      Application.Quit
    End If
    
    Public lngMyEmpID As Long
    

    and if someone forgot his password, he can request it by email by just putting his username in a text box.

    So, how can I design this, please?

    Wednesday, August 24, 2011 12:22 PM

Answers

  • Hi,

    it's ok, I found how to do it, just to convert SQL to VBASQL and it's working fine!!!

    • Marked as answer by iboumiza Monday, September 12, 2011 7:35 PM
    Monday, September 12, 2011 7:35 PM

All replies

  • There are lots of ways to send email from VBA. The simplest is to just use the DoCmd.SendObject method without any data (acSendNoObject). You would probably want to include some more helpful information in the subject or message text, but here's the basic idea:

      If intLogonAttempts > 3 Then
      
       ' Before quitting, check if user wants to send an email. If so,
       ' send the email.
       
       Dim mResult As VbMsgBoxResult, message As String
       message = "You do not have access to this database. Please contact admin." & vbCrLf & _
            "Do you want to send an email requesting a password reset?"
       mResult = MsgBox(message, (vbCritical + vbYesNo), "Restricted Access!")
       
       If mResult = vbYes Then
         DoCmd.SendObject acSendNoObject, _
          To:="MyDbAdmin@MyCompany.com", _
          Subject:="I forget my password again", _
          MessageText:="I need you to reset my database password"
       End If
       
       Application.Quit
      End If
    

     


    jmh
    • Proposed as answer by Joshua Honig Wednesday, August 31, 2011 4:32 PM
    Tuesday, August 30, 2011 3:32 PM
  • Private Sub btnLostPsswrd_Click()
    'Send
            Dim strSql As String
            Dim olApp As Object
            Dim objMail As Object
            
            strSql = "SELECT pass " & vbCrLf & _
                     "FROM logid " & vbCrLf & _
                     "WHERE (name = forms!lostpass!txtName);"
            
            'Keep going if there is an error
            On Error Resume Next
            
            'See if Outlook is open
            Set olApp = GetObject(, "Outlook.Application")
            
            'Outlook is not open
            If Err Then
            'Create a new instance of Outlook
            Set olApp = CreateObject("Outlook.Application")
            End If
            
            'Create e-mail item
            Set objMail = olApp.CreateItem(olMailItem)
            
            With objMail
    
    'Set body format to HTML
    .BodyFormat = olFormatHTML
    .To = "me@mycompany.com"
    .Subject = "I forget my password again"
    .HTMLBody = "<HTML><BODY><font face=Palatino Linotype size=2>Hi <b>" & Me.txtName.Column(1) & _
    "</b> ,<p> Your password is: " & strSql & _
    "</font></BODY></HTML>"
    .Display
    
    End With
    End Sub
    


    Here is what I want, but instead of having the password in the e-mail body, it shows me the SQL sequence...

    How do I fix that, please?

    Saturday, September 10, 2011 3:28 PM
  • Hi,

    it's ok, I found how to do it, just to convert SQL to VBASQL and it's working fine!!!

    • Marked as answer by iboumiza Monday, September 12, 2011 7:35 PM
    Monday, September 12, 2011 7:35 PM