Login Forms

[CanuckChuck]

Hello,

I will mention that I am a beginner at Access in every sense of the word. I have created a database for all the training records of my employees. I have set up a login form that requires a password linked to the employee table.

My question is how do I create a command button or action that will prompt the login user and enable them to change their password once they have logged in or chosen a user name from the drop down.

Thank you in advance for the help!

[MMcCarthy]

Assuming your password is set to a default like "Password".

You don't say if the username is entered freely or chosen from a dropdown list. I'm assuming it's entered freely (txtUser for this example).

In the textbox where the password is entered (txtPassword for this example) create an After Update event using the following code.

 
Private Sub txtPassword_AfterUpdate()
Dim pswd As String
Dim confirm As String
 
  pswd = DLookup("[PasswordField]","TableName", "[UserNameField]='" & Me.txtUser & "')"
 
  If pswd = "password" Then
 
EnterPassword:
 
	pswd = InputBox "Please enter a new password", "Change Password"
	confirm = InputBox "Please enter again for confirmation", "Confirm Password"
	If pswd <> confirm Then
	 MsgBox "Passwords don't match. Please try again.", vbOKOnly
	 GoTo EnterPassword
	Else
	 DoCmd.RunSQL "UPDATE tablename SET [passwordfield]= '" & Me.pswd & "' WHERE [UserNameField]='" & Me.txtUser & "';"
	End If
  Else
	 'Your normal password checking code here
  End If	
 
End Sub

[CanuckChuck]

Thank you for your response. I have tried the code, modifying it to match my data and I can't seem to get it to work.

The username is chosen from a dropdown list named Combo10. My textbox for the password entry is named Text12.

I will attach my modified version of the code so you can see what is wrong with it.

I keep getting a syntax error for the line with the DLookup function, as well I forgot to mention in my first message I am using Access 2003.

Also can this code be attached to the OnClick event of the command button that I am using to verify the password and enter the record form?

Thank you for the help.

Here is my code:

Private Sub Text12_AfterUpdate()
Dim pswd As String
Dim confirm As String
 
  pswd = DLookup("[strEmpPassword]", "strEmployees", "[IngEmpID]='" & Me.Combo10. & "')"
 
  If pswd = "password" Then
 
EnterPassword:
 
    pswd = MsgBox "Please enter a new password", "Change Password"
    confirm = InputBox "Please enter again for confirmation", "Confirm Password"
    If pswd <> confirm Then
     MsgBox "Passwords don't match. Please try again.", vbOKOnly
     GoTo EnterPassword
    Else
     DoCmd.RunSQL "UPDATE strEmployees SET [EmpPassword]= '" & Me.pswd & "' WHERE [IngEmpID]='" & Me.Combo10 & "';"
    End If
  Else
'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

If Me.Text12.Value = DLookup("strEmpPassword", "strEmployees", "[IngEmpID]=" & Me.Combo10.Value) Then

IngMyEmpID = Me.Combo10.Value

'Close logon form and open Employee records
  
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Read Only Records"
    
    stLinkCriteria = "[IngEmpID]=" & Me![IngEmpID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
Exit_Go_Click:
    Exit Sub

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.Text12.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 If
 
End Sub

Assuming your password is set to a default like "Password".

You don't say if the username is entered freely or chosen from a dropdown list. I'm assuming it's entered freely (txtUser for this example).

In the textbox where the password is entered (txtPassword for this example) create an After Update event using the following code.

 
Private Sub txtPassword_AfterUpdate()
Dim pswd As String
Dim confirm As String
 
  pswd = DLookup("[PasswordField]","TableName", "[UserNameField]='" & Me.txtUser & "')"
 
  If pswd = "password" Then
 
EnterPassword:
 
	pswd = InputBox "Please enter a new password", "Change Password"
	confirm = InputBox "Please enter again for confirmation", "Confirm Password"
	If pswd <> confirm Then
	 MsgBox "Passwords don't match. Please try again.", vbOKOnly
	 GoTo EnterPassword
	Else
	 DoCmd.RunSQL "UPDATE tablename SET [passwordfield]= '" & Me.pswd & "' WHERE [UserNameField]='" & Me.txtUser & "';"
	End If
  Else
	 'Your normal password checking code here
  End If	
 
End Sub

[MMcCarthy]

Also can this code be attached to the OnClick event of the command button that I am using to verify the password and enter the record form?

Yes it can.

pswd = DLookup("[strEmpPassword]", "strEmploye es", "[IngEmpID]='" & Me.Combo10. & "')"

Firstly, you have a dot after combo10 so remove it.
Secondly, if the value in [lngEmpID] is a number (I suspect it is) then remove single quotes as follows. They are only required when comparing strings.

pswd = DLookup("[strEmpPassword]", "strEmploye es", "[IngEmpID]=" & Me.Combo10 & ")"

[CanuckChuck]

O.K. So, I tried attaching the code to my OnClick event of my command button and I keep getting a Compile Error: syntax error pop up. I also have recieved the message Compile Error: Expected: List Separator or ) when trying to fix it.

I will attach the full code that is attached to my command button

Private Sub Command14_Click()
'Check to see if data is entered into the UserName combo box

If IsNull(Me.Combo10) Or Me.Combo10 = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.Combo10.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.Text12) Or Me.Text12 = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.Text12.SetFocus
Exit Sub
End If

'Check to see if default password needs to be changed

Dim cmnd As String
Dim confirm As String
 
 
pswd = DLookup("[strEmpPassword]", "strEmployees", "[IngEmpID]=" & Me.Combo10 & ")"

If pswd = "Password" Then
 
EnterPassword:
 
    pswd = InputBox "Please enter a new password", "Change Password"
    confirm = InputBox "Please enter again for confirmation", "Confirm Password"
    If pswd <> confirm Then
     MsgBox "Passwords don't match. Please try again.", vbOKOnly
     GoTo EnterPassword
    Else
     DoCmd.RunSQL "UPDATE strEmployees SET [strEmpPassword]= '" & Me.pswd & "' WHERE [IngEmpID]='" & Me.Combo10 & "';"
    End If
  Else
     'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

If Me.Text12.Value = DLookup("strEmpPassword", "strEmployees", "[IngEmpID]=" & Me.Combo10.Value) Then

IngMyEmpID = Me.Combo10.Value

'Close logon form and open Employee records
  
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Read Only Records"
    
    stLinkCriteria = "[IngEmpID]=" & Me![IngEmpID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
Exit_Go_Click:
    Exit Sub

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.Text12.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
Private Sub Command15_Click()
On Error GoTo Err_Command15_Click


    DoCmd.Close

Exit_Command15_Click:
    Exit Sub

Err_Command15_Click:
    MsgBox Err.Description
    Resume Exit_Command15_Click
    
End Sub

Private Sub Text12_AfterUpdate()

     'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

If Me.Text12.Value = DLookup("strEmpPassword", "strEmployees", "[IngEmpID]=" & Me.Combo10.Value) Then

IngMyEmpID = Me.Combo10.Value

'Close logon form and open Employee records
  
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "Read Only Records"
    
    stLinkCriteria = "[IngEmpID]=" & Me![IngEmpID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
Exit_Go_Click:
    Exit Sub

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.Text12.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

Yes it can.



Firstly, you have a dot after combo10 so remove it.
Secondly, if the value in [lngEmpID] is a number (I suspect it is) then remove single quotes as follows. They are only required when comparing strings.

pswd = DLookup("[strEmpPassword]", "strEmploye es", "[IngEmpID]=" & Me.Combo10 & ")"

[MMcCarthy]

Sorry this is me again. My brain is dead today

Change ...

pswd = DLookup("[strEmpPassword]", "strEmploye es", "[IngEmpID]=" & Me.Combo10 & ")"

to ...

pswd = DLookup("[strEmpPassword]", "strEmploye es", "[IngEmpID]=" & Me.Combo10)

[CanuckChuck]

O.K., now I am getting a "Compile error: syntax error" for the line:

pswd = InputBox "Please enter a new password", "Change Password"

Sorry this is me again. My brain is dead today

Change ...

pswd = DLookup("[strEmpPassword]", "strEmploye es", "[IngEmpID]=" & Me.Combo10 & ")"

to ...

pswd = DLookup("[strEmpPassword]", "strEmploye es", "[IngEmpID]=" & Me.Combo10)

[MMcCarthy]

O.K., now I am getting a "Compile error: syntax error" for the line:

pswd = InputBox "Please enter a new password", "Change Password"

You left out the brackets

pswd = InputBox ("Please enter a new password", "Change Password")

[MMcCarthy]

You left out the brackets

pswd = InputBox ("Please enter a new password", "Change Password")

Or to be more accurate. I left them out.

Sorry...

Mary

[CanuckChuck]

now the code stops running at this line with the error message: "method or data member not found" and highlights the pswd part. But the message box is not appearing when I enter the default password, and I am under the impression that it should be appearing regardless of the error in the line below as it occurs after the password would be changed?

DoCmd.RunSQL "UPDATE strEmployees SET [EmpPassword]= '" & Me.pswd & "' WHERE [IngEmpID]='" & Me.Combo10 & "';"


Thanks

Or to be more accurate. I left them out.

Sorry...

Mary

[MMcCarthy]

now the code stops running at this line with the error message: "method or data member not found" and highlights the pswd part. But the message box is not appearing when I enter the default password, and I am under the impression that it should be appearing regardless of the error in the line below as it occurs after the password would be changed?

DoCmd.RunSQL "UPDATE strEmployees SET [EmpPassword]= '" & Me.pswd & "' WHERE [IngEmpID]='" & Me.Combo10 & "';"


Thanks

I understood the pswd was the global string variable that the user entered password was stored in. In which case you don't want the Me. part. That is only used for controls on the form.

Mary

[CanuckChuck]

Now it executes all the way to the very end, and then there is an error "Block If Without End If" and it highlights the End Sub command, but I can't see where the incongruency in the code is?

Thanks

Charlie

I understood the pswd was the global string variable that the user entered password was stored in. In which case you don't want the Me. part. That is only used for controls on the form.

Mary

[MMcCarthy]

Now it executes all the way to the very end, and then there is an error "Block If Without End If" and it highlights the End Sub command, but I can't see where the incongruency in the code is?

Thanks

Charlie

No problem Charlie.

You have to go through all the code when this happens because the compiler can't tell where the error occurred and just goes to the end of the function or procedure.

In your case the following If statement has no End If.

 
If pswd = "Password" Then
EnterPassword:

	pswd = InputBox "Please enter a new password", "Change Password"
	confirm = InputBox "Please enter again for confirmation", "Confirm Password"
	If pswd <> confirm Then
	   MsgBox "Passwords don't match. Please try again.", vbOKOnly
	   GoTo EnterPassword
	Else
	   DoCmd.RunSQL "UPDATE strEmployees SET [strEmpPassword]= '" & Me.pswd & "' WHERE [IngEmpID]='" & Me.Combo10 & "';"
	End If
Else
	 'Check value of password in tblEmployees to see if this
	 'matches value chosen in combo box

[CanuckChuck]

Forgive my ignorance, but where would the End If go then?

No problem Charlie.

You have to go through all the code when this happens because the compiler can't tell where the error occurred and just goes to the end of the function or procedure.

In your case the following If statement has no End If.

 
If pswd = "Password" Then
EnterPassword:

	pswd = InputBox "Please enter a new password", "Change Password"
	confirm = InputBox "Please enter again for confirmation", "Confirm Password"
	If pswd <> confirm Then
	   MsgBox "Passwords don't match. Please try again.", vbOKOnly
	   GoTo EnterPassword
	Else
	   DoCmd.RunSQL "UPDATE strEmployees SET [strEmpPassword]= '" & Me.pswd & "' WHERE [IngEmpID]='" & Me.Combo10 & "';"
	End If
Else
	 'Check value of password in tblEmployees to see if this
	 'matches value chosen in combo box

[MMcCarthy]

Forgive my ignorance, but where would the End If go then?

Trying to follow your code I would say just before End Sub.