Help with code to fire a create password dialog.

**David618** · 04-29-2013, 03:46 PM

I have a new challenge with my existing Log In form. Currently, the code: (1) checks for null values in the Employee Name and Pasword boxes, (2) checks the passord itself and the active status and (3) opens a particular form depending on the user status. Now I need it to pop up a separate form (frmCreatePwd) prompting the user to enter a password if they have never logged in. One way I thought of it verifying this is if the password field (Emp_Pass) is currently null.

I suspect it needs to check this after it checks the active status. Any suggestions would be mucho appreciated.

Thanks,
Struggling Newbie muddling through one step at a time...

Code:

Private Sub cmdLogin_Click()
'Check to see if data has been selected from the Employee Name combo box
    If IsNull(Me.cboIdentity) Or Me.cboIdentity = "" Then
      MsgBox "You must select an Employee Name.", vbOKOnly, "Required Data"
        Me.cboIdentity.SetFocus
        Exit Sub
    End If
    'Check to see if data has been entered into the Password text 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 the value of the password in tblEmployeeRoster to see if this
    'matches the value chosen in combo box
    If Me.txtPassword.Value = Me.cboIdentity.Column(2) Then
        Emp_ID = Me.cboIdentity.Value
        
        'Check to verify if the employee is still active in the database
        
        If Me.cboIdentity.Column(3) = False Then
              MsgBox "You are no longer active in this database. Please contact XYZ Employee.", _
               vbCritical, "Restricted Access!"
            Application.Quit
        Else
            'Close the Log In form and open either the Editor or Main Menu
        
            If Me.cboIdentity.Column(4) = True Then
            
                DoCmd.OpenForm "frmMainMenu"
            Else
                DoCmd.OpenForm "frmEmployeeData"
            End If
            Me.Visible = False
        End If
    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 > 2 Then
      MsgBox "Apparently you do not have access to this database. Please contact XYZ Employee.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If
End Sub

**DepricatedZero** · 04-29-2013, 04:28 PM

Can't see everything that's going on but it looks like you're preloading the password from the table, or have the form bound to it?

Either way, it looks like the password is in cboIdentity.Column(2). I would put an afterupdate on that which checks to see if it's null and then acts on it if it is.

So say

Code:

If IsNull (cboIdentity.Column(2)) Then
   DoCmd.OpenForm "frmFirstTimePassword"
End If

**ssanfu** · 04-29-2013, 04:53 PM

I merged the code from DepricatedZero into your code. I restructured your code:
(untested!!!)

So... the logic I would use:
Check userID
Check if active - if not active no sense checking PW
Check if previous PW created - in not - open password form
Check PW is correct - if correct, open proper form

Code:

Private Sub cmdLogin_Click()

   'Check to see if data has been selected from the Employee Name combo box
   If IsNull(Me.cboIdentity) Or Me.cboIdentity = "" Then
      MsgBox "You must select an Employee Name.", vbOKOnly, "Required Data"
      Me.cboIdentity.SetFocus
      Exit Sub
   End If

   'Check to verify if the employee is still active in the database
    If Me.cboIdentity.Column(3) = False Then
      MsgBox "You are no longer active in this database. Please contact XYZ Employee.", _
             vbCritical, "Restricted Access!"
      Application.Quit
   End If

   If IsNull(cboIdentity.Column(2)) Then
      DoCmd.OpenForm "frmFirstTimePassword"
      '***  might need more code here ***
      ' somthing like
      '      Me.txtPassword = Null
      '      Me.txtPassword.SetFocus
      '      Exit Sub
   End If

   'Check to see if data has been entered into the Password text box
   ' I prefer to use    If Len(Trim(Me.txtPassword)) > 0 Then

   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 the value of the password in tblEmployeeRoster to see if this
   'matches the value chosen in combo box
   If Me.txtPassword = Me.cboIdentity.Column(2) Then
      Emp_ID = Me.cboIdentity.Value

      'Close the Log In form and open either the Editor or Main Menu

      If Me.cboIdentity.Column(4) = True Then

         DoCmd.OpenForm "frmMainMenu"
      Else
         DoCmd.OpenForm "frmEmployeeData"
      End If
      Me.Visible = False

   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 > 2 Then
      MsgBox "Apparently you do not have access to this database. Please contact XYZ Employee.", _
             vbCritical, "Restricted Access!"
      Application.Quit
   End If
End Sub

**David618** · 05-03-2013, 01:09 PM

Steve,

Sorry it took me a while to get back - I'm working on too many things at once! I revised my code, but now it skips over the part where it's supposed to check if the password field is empty (see where it says "If IsNull(cboIdentity.Column(2)) Then") and instead jumps to giving you the message box "Password Invalid. Please Try Again." So I suspect the "IsNull" part isn't doing the trick - ? Any suggestions?

Code:

Private Sub cmdLogin_Click()
'Check to see if data has been selected from the Employee Name combo box
   If IsNull(Me.cboIdentity) Or Me.cboIdentity = "" Then
      MsgBox "You must select an Employee Name.", vbOKOnly, "Required Data"
      Me.cboIdentity.SetFocus
      Exit Sub
   End If
'Check to see if data has been entered into the Password text 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 to verify if the employee is still active in the database
    If Me.cboIdentity.Column(3) = False Then
      MsgBox "You are no longer active in this database. Please contact XYZ Employee.", _
             vbCritical, "Restricted Access!"
      Application.Quit
   End If
'Check if password field is empty.  If so, open the password create form
   If IsNull(cboIdentity.Column(2)) Then
        MsgBox "You need to create a password.", _
             vbCritical, "Restricted Access!"
      
        DoCmd.OpenForm "frmCreatePwd"
   End If

'Check the value of the password in tblEmployeeRoster to see if this
    'matches the value chosen in combo box
    
   If Me.txtPassword = Me.cboIdentity.Column(2) Then
      Emp_ID = Me.cboIdentity.Value
    'Close the Log In form and open either the Editor or Main Menu
      If Me.cboIdentity.Column(4) = True Then
         DoCmd.OpenForm "frmMainMenu"
      Else
         DoCmd.OpenForm "frmEmployeeData"
      End If
      Me.Visible = False
   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 > 2 Then
      MsgBox "Apparently you do not have access to this database. Please contact XYZ Employee.", _
             vbCritical, "Restricted Access!"
      Application.Quit
   End If
   
End Sub

**ssanfu** · 05-03-2013, 04:41 PM

It doesn't look like you tried my code..

So, (again) ... the logic I would use:
User name/userID selected from combo box
Check if active - if not active no sense checking PW
Check if previous PW created... if not - open password form
Check if PW is correct - if correct, open proper form

Try commenting out your code and try mine. I did a little testing and it appears to work correctly.

Code:

Private Sub cmdLogin_Click()
 Const MaxTries As Integer = 2

'Check to see if data has been selected from the Employee Name combo box
    If Len(Trim(Me.cboIdentity & "")) = 0 Then
        MsgBox "You must select an Employee Name.", vbOKOnly, "Required Data"
        Me.cboIdentity.SetFocus
        Exit Sub
    End If

    'Check to verify if the employee is still active in the database
    If Me.cboIdentity.Column(3) = False Then
        MsgBox "You are no longer active in this database. Please contact XYZ Employee.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If

    If Len(Trim(Me.cboIdentity.Column(2) & "")) = 0 Then
        DoCmd.OpenForm "frmFirstTimePassword"
        'requery the combo box
        Me.cboIdentity.Requery
        Exit Sub
    End If

    'Check to see if data has been entered into the Password text box
    If Len(Trim(Me.txtPassword & "")) = 0 Then
        MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If

    'Check if the the password in the text box
    'matches the password column in the combo box
    If Me.txtPassword = Me.cboIdentity.Column(2) Then
        'password correct
        Emp_ID = Me.cboIdentity.Value

        'Close the Log In form and open either the Editor or Main Menu

        If Me.cboIdentity.Column(4) = True Then
            DoCmd.OpenForm "frmMainMenu"
        Else
            DoCmd.OpenForm "frmEmployeeData"
        End If
        Me.Visible = False

    Else
        'password wrong
        MsgBox "Password Invalid. Please Try Again" & vbNewLine & vbNewLine & MaxTries - intLogonAttempts & " tries remaining!!", vbOKOnly + vbExclamation, "Invalid Entry!"
        Me.txtPassword.SetFocus
        'If User Enters incorrect password 3 times database will shutdown
        intLogonAttempts = intLogonAttempts + 1
        If intLogonAttempts > 2 Then
            MsgBox "Apparently you do not have access to this database. Please contact XYZ Employee.", _
                   vbCritical, "Restricted Access!"
            Application.Quit
        End If
    End If
End Sub

**David618** · 05-07-2013, 01:28 PM

Steve,

I'll try it again as you have it written and let you know how it went. I tried it the way you had it for that one segment and it didn't seemed to work - it worked better the other way, but I'll give it another "college try" with how you have it written immediately above and let you know...

**DepricatedZero** · 05-08-2013, 06:03 AM

'It may be a 0-length array instead of null, changing If IsNull to If var = "" may fire it off appropriately.

edit: that's exactly what ssanfu's code does with If Len(Trim(Me.cboIdentity.Column(2) & "")) = 0 Then

His method is probably better practice

**David618** · 05-08-2013, 09:32 AM

Okay, I'm back. I tried it and it's close - it doesn't give any errors, however the following happens:

(1) It needs to temporarily hide the log in form (frmLogIn). As it currently is, it cascades one form on top of the other (the password create form - frmCreatePwd on top of the Log In form - frmLogIn) making it confusing for the end user since both but the "Enter" button from the Log In form and the "Save" button from the Password Create form are exposed. I need the Log In form to temporarily "go away" while the create password form is up and "come back" after the user clicks on the "Save" button and saves the password to the base table (in this case that would be a table named "tblEmployeeRoster" which saves all the demographic information on the employee). Probably easy enough - I'll experiment...

(2) After entering a new password on the create password form (frmCreatePwd) and hitting the "Save" command button (cmdSavePwd) the password form closes and goes back to the Log In form, however when you enter the password you thought you had saved (as a new user) it pulls up the create password form again (frmCreatePwd) so you're essentially caught in this endless loop. Apparently it's not saving the new password the user had entered (in a text box on the frmCreatePwd - named txtCreatePwd). It needs to save the value from txtCreatePwd to a table named "tblEmployeeRoster"...

Here's the current code:

Code:

Private Sub cmdLogin_Click()
Const MaxTries As Integer = 2
'Check to see if data has been selected from the Employee Name combo box
    If Len(Trim(Me.cboIdentity & "")) = 0 Then
        MsgBox "You must select an Employee Name.", vbOKOnly, "Required Data"
        Me.cboIdentity.SetFocus
        Exit Sub
    End If
'Check to verify if the employee is still active in the database
    If Me.cboIdentity.Column(3) = False Then
        MsgBox "You are no longer active in this database. Please contact XYZ Employee.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If
    If Len(Trim(Me.cboIdentity.Column(2) & "")) = 0 Then
        DoCmd.OpenForm "frmCreatePwd"
        'Requery the combo box
        Me.cboIdentity.Requery
        Exit Sub
    End If
'Check to see if data has been entered into the Password text box
    If Len(Trim(Me.txtPassword & "")) = 0 Then
        MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
'Check if the the password in the text box matches the password column in the combo box
    If Me.txtPassword = Me.cboIdentity.Column(2) Then
        'The password is correct...
        Emp_ID = Me.cboIdentity.Value
'Close the Log In form and open either the Editor or the Main Menu
        If Me.cboIdentity.Column(4) = True Then
            DoCmd.OpenForm "frmMainMenu"
        Else
            DoCmd.OpenForm "frmEmployeeData"
        End If
        Me.Visible = False
    Else
        'The password wrong...
        MsgBox "Password Invalid. Please Try Again" & vbNewLine & vbNewLine & MaxTries - intLogonAttempts & " tries remaining!!", vbOKOnly + vbExclamation, "Invalid Entry!"
        Me.txtPassword.SetFocus
        
'If the user enters an incorrect password 3 times the database will shutdown
        intLogonAttempts = intLogonAttempts + 1
        If intLogonAttempts > 2 Then
            MsgBox "Apparently you do not have access to this database. Please contact XYZ Employee.", _
                   vbCritical, "Restricted Access!"
            Application.Quit
        End If
    End If
    
End Sub

**David618** · 05-08-2013, 09:36 AM

PS - here's the code behind the button on the create password form (frmCreatePwd - button named cmdSavePwd):

Code:

Private Sub cmdSavePwd_Click()

'Verify that a password has been entered
   If IsNull(Me.txtCreatePwd) Or Me.txtCreatePwd = "" Then
      MsgBox "You must create a Password.", vbOKOnly, "Required Data"
      Me.txtCreatePwd.SetFocus
      Exit Sub
   End If
'Save the password, close the form and re-open the Log In form
    DoCmd.Close acForm, Me.Name, acSaveYes
    DoCmd.OpenForm "frmLogIn", acNormal
End Sub

**David618** · 05-08-2013, 03:35 PM

I think I've identified the problem, but it's still not quite working. The code for the create password form (on click). I think what it wasn't doing before was saving the value the user entered into the text box (txtCreatePwd) into the appropriate base table itself (the password field [Emp_Pass] in the employee roster table - tblEmployeeRoster), so I modified the "on click" code for the "Save" command button on that particular form (cmdSavePwd) and added the following:

Code:

    Update tblEmployeeRoster
    Set [Emp_Pass] = Me.txtCreatePwd
    Where Forms!frmlogin!cboIdentity = tblEmployeeRoster.Emp_ID

So the total code looks like this:

Code:

Private Sub cmdSavePwd_Click()

'Verify that a password has been entered
   If IsNull(Me.txtCreatePwd) Or Me.txtCreatePwd = "" Then
      MsgBox "You must create a Password.", vbOKOnly, "Required Data"
      Me.txtCreatePwd.SetFocus
      Exit Sub
   End If
'Save the password, close the form and re-open the Log In form
    Update tblEmployeeRoster
    Set [Emp_Pass] = Me.txtCreatePwd
    Where Forms!frmlogin!cboIdentity = tblEmployeeRoster.Emp_ID
    
    DoCmd.Close acForm, Me.Name, acSaveYes
    DoCmd.OpenForm "frmLogIn", acNormal
End Sub

However, when I run it, it gives me the following: "Compile Error: Sub or Function not defined" and it highlights at "Update." I know it's probably something stupid and you are probably laughing, but what am I missing here?

**June7** · 05-08-2013, 03:44 PM

Are you trying to execute an UPDATE sql action?

CurrentDb.Execute "UPDATE tblEmployeeRoster SET [Emp_Pass]='" & Me.txtCreatePwd & "' WHERE Emp_ID=" & Me.cboIdentity

However, if the form is bound to tblEmployeeRoster and it is filtered to the employee record, and assuming txtCreatePwd is not bound to the Emp_Pass field, just:

Me!Emp_Pass = Me.txtCreatePwd

If the form and textbox are bound, then the value entered is passed directly to table.

**David618** · 05-08-2013, 04:21 PM

It's not (currently) bound to that table - it's a simple form that pops up if the log in form cannot find a password for the user. I replaced what I had with your suggestion, but got the error, "Compile error: Method or data member not found" and it highlighted ".txtCreatePwd":

Code:

Private Sub cmdSavePwd_Click()

'Verify that a password has been entered
   If IsNull(Me.txtCreatePwd) Or Me.txtCreatePwd = "" Then
      MsgBox "You must create a Password.", vbOKOnly, "Required Data"
      Me.txtCreatePwd.SetFocus
      Exit Sub
   End If
'Save the password, close the form and re-open the Log In form
    
    CurrentDb.Execute "UPDATE tblEmployeeRoster SET [Emp_Pass]='" & Me.txtCreatePwd & "' WHERE Emp_ID=" & Me.cboIdentity
    
    DoCmd.Close acForm, Me.Name, acSaveYes
    DoCmd.OpenForm "frmLogIn", acNormal
End Sub

Thanks for responding to my plea!

**June7** · 05-08-2013, 05:05 PM

I was just using the control names from your posted code. What is the name of the control for user to enter new password? Since cboIdentity is on the login form need to reference that form instead of Me (my bad, sorry):

"' WHERE Emp_ID=" & Forms!frmlogin.cboIdentity

**David618** · 05-08-2013, 09:57 PM

The name of the control for the user to enter a new password is "txtCreatePwd" I pasted your suggestion and am still getting an error ("Run-time error '3464': Data type mismatch in criteria expression") and it highlights:

Code:

    CurrentDb.Execute "UPDATE tblEmployeeRoster SET [Emp_Pass]='" & Me.txtCreatePwd & "' WHERE Emp_ID=" & _
        Forms!frmlogin.cboIdentity

I think it's because Emp_ID is in the text format?

**Beetle** · 05-08-2013, 10:10 PM

Then change it to;

Code:

CurrentDb.Execute "UPDATE tblEmployeeRoster SET [Emp_Pass]='" & Me.txtCreatePwd & "' WHERE Emp_ID='" & _
        Forms!frmlogin.cboIdentity & "'"

Help with code to fire a create password dialog.

Thread Tools

Help with code to fire a create password dialog.

Revised it, but still doesn't work...

Mine still has a couple of bugs...

Code for save button on the create password form...

Getting closer...?

Error message...

Getting closer...?

Similar Threads

import/export of forms with VBA code password

Help fixing a code to change password programatically

Why Does Macros Dialog Pop-Up When I Try To Run Code In VB Editor?

How to create a Password Form

Login/Password Code not working

Tags for this Thread

Posting Permissions