By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Login Forms

P: 17
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!
Nov 10 '06 #1
Share this Question
Share on Google+
19 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub txtPassword_AfterUpdate()
  3. Dim pswd As String
  4. Dim confirm As String
  5.  
  6.   pswd = DLookup("[PasswordField]","TableName", "[UserNameField]='" & Me.txtUser & "')"
  7.  
  8.   If pswd = "password" Then
  9.  
  10. EnterPassword:
  11.  
  12.     pswd = InputBox "Please enter a new password", "Change Password"
  13.     confirm = InputBox "Please enter again for confirmation", "Confirm Password"
  14.     If pswd <> confirm Then
  15.      MsgBox "Passwords don't match. Please try again.", vbOKOnly
  16.      GoTo EnterPassword
  17.     Else
  18.      DoCmd.RunSQL "UPDATE tablename SET [passwordfield]= '" & Me.pswd & "' WHERE [UserNameField]='" & Me.txtUser & "';"
  19.     End If
  20.   Else
  21.      'Your normal password checking code here
  22.   End If    
  23.  
  24. End Sub
  25.  
  26.  
Nov 12 '06 #2

P: 17
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Text12_AfterUpdate()
  3. Dim pswd As String
  4. Dim confirm As String
  5.  
  6.   pswd = DLookup("[strEmpPassword]", "strEmployees", "[IngEmpID]='" & Me.Combo10. & "')"
  7.  
  8.   If pswd = "password" Then
  9.  
  10. EnterPassword:
  11.  
  12.     pswd = MsgBox "Please enter a new password", "Change Password"
  13.     confirm = InputBox "Please enter again for confirmation", "Confirm Password"
  14.     If pswd <> confirm Then
  15.      MsgBox "Passwords don't match. Please try again.", vbOKOnly
  16.      GoTo EnterPassword
  17.     Else
  18.      DoCmd.RunSQL "UPDATE strEmployees SET [EmpPassword]= '" & Me.pswd & "' WHERE [IngEmpID]='" & Me.Combo10 & "';"
  19.     End If
  20.   Else
  21. 'Check value of password in tblEmployees to see if this
  22. 'matches value chosen in combo box
  23.  
  24. If Me.Text12.Value = DLookup("strEmpPassword", "strEmployees", "[IngEmpID]=" & Me.Combo10.Value) Then
  25.  
  26. IngMyEmpID = Me.Combo10.Value
  27.  
  28. 'Close logon form and open Employee records
  29.  
  30.     Dim stDocName As String
  31.     Dim stLinkCriteria As String
  32.     stDocName = "Read Only Records"
  33.  
  34.     stLinkCriteria = "[IngEmpID]=" & Me![IngEmpID]
  35.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  36.  
  37. Exit_Go_Click:
  38.     Exit Sub
  39.  
  40. Else
  41. MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
  42. Me.Text12.SetFocus
  43. End If
  44.  
  45. 'If User Enters incorrect password 3 times database will shutdown
  46.  
  47. intLogonAttempts = intLogonAttempts + 1
  48. If intLogonAttempts > 3 Then
  49. MsgBox "You do not have access to this database.Please contact admin.", vbCritical, "Restricted Access!"
  50. Application.Quit
  51. End If
  52.   End If
  53.  
  54. End Sub
  55.  
  56.  



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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub txtPassword_AfterUpdate()
  3. Dim pswd As String
  4. Dim confirm As String
  5.  
  6.   pswd = DLookup("[PasswordField]","TableName", "[UserNameField]='" & Me.txtUser & "')"
  7.  
  8.   If pswd = "password" Then
  9.  
  10. EnterPassword:
  11.  
  12.     pswd = InputBox "Please enter a new password", "Change Password"
  13.     confirm = InputBox "Please enter again for confirmation", "Confirm Password"
  14.     If pswd <> confirm Then
  15.      MsgBox "Passwords don't match. Please try again.", vbOKOnly
  16.      GoTo EnterPassword
  17.     Else
  18.      DoCmd.RunSQL "UPDATE tablename SET [passwordfield]= '" & Me.pswd & "' WHERE [UserNameField]='" & Me.txtUser & "';"
  19.     End If
  20.   Else
  21.      'Your normal password checking code here
  22.   End If    
  23.  
  24. End Sub
  25.  
  26.  
Nov 16 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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]", "strEmployees", "[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]", "strEmployees", "[IngEmpID]=" & Me.Combo10 & ")"
Nov 16 '06 #4

P: 17
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command14_Click()
  3. 'Check to see if data is entered into the UserName combo box
  4.  
  5. If IsNull(Me.Combo10) Or Me.Combo10 = "" Then
  6. MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
  7. Me.Combo10.SetFocus
  8. Exit Sub
  9. End If
  10.  
  11. 'Check to see if data is entered into the password box
  12.  
  13. If IsNull(Me.Text12) Or Me.Text12 = "" Then
  14. MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
  15. Me.Text12.SetFocus
  16. Exit Sub
  17. End If
  18.  
  19. 'Check to see if default password needs to be changed
  20.  
  21. Dim cmnd As String
  22. Dim confirm As String
  23.  
  24.  
  25. pswd = DLookup("[strEmpPassword]", "strEmployees", "[IngEmpID]=" & Me.Combo10 & ")"
  26.  
  27. If pswd = "Password" Then
  28.  
  29. EnterPassword:
  30.  
  31.     pswd = InputBox "Please enter a new password", "Change Password"
  32.     confirm = InputBox "Please enter again for confirmation", "Confirm Password"
  33.     If pswd <> confirm Then
  34.      MsgBox "Passwords don't match. Please try again.", vbOKOnly
  35.      GoTo EnterPassword
  36.     Else
  37.      DoCmd.RunSQL "UPDATE strEmployees SET [strEmpPassword]= '" & Me.pswd & "' WHERE [IngEmpID]='" & Me.Combo10 & "';"
  38.     End If
  39.   Else
  40.      'Check value of password in tblEmployees to see if this
  41. 'matches value chosen in combo box
  42.  
  43. If Me.Text12.Value = DLookup("strEmpPassword", "strEmployees", "[IngEmpID]=" & Me.Combo10.Value) Then
  44.  
  45. IngMyEmpID = Me.Combo10.Value
  46.  
  47. 'Close logon form and open Employee records
  48.  
  49.     Dim stDocName As String
  50.     Dim stLinkCriteria As String
  51.     stDocName = "Read Only Records"
  52.  
  53.     stLinkCriteria = "[IngEmpID]=" & Me![IngEmpID]
  54.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  55.  
  56. Exit_Go_Click:
  57.     Exit Sub
  58.  
  59. Else
  60. MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
  61. Me.Text12.SetFocus
  62. End If
  63.  
  64. 'If User Enters incorrect password 3 times database will shutdown
  65.  
  66. intLogonAttempts = intLogonAttempts + 1
  67. If intLogonAttempts > 3 Then
  68. MsgBox "You do not have access to this database.Please contact admin.", vbCritical, "Restricted Access!"
  69. Application.Quit
  70. End If
  71.  
  72. End Sub
  73. Private Sub Command15_Click()
  74. On Error GoTo Err_Command15_Click
  75.  
  76.  
  77.     DoCmd.Close
  78.  
  79. Exit_Command15_Click:
  80.     Exit Sub
  81.  
  82. Err_Command15_Click:
  83.     MsgBox Err.Description
  84.     Resume Exit_Command15_Click
  85.  
  86. End Sub
  87.  
  88. Private Sub Text12_AfterUpdate()
  89.  
  90.      'Check value of password in tblEmployees to see if this
  91. 'matches value chosen in combo box
  92.  
  93. If Me.Text12.Value = DLookup("strEmpPassword", "strEmployees", "[IngEmpID]=" & Me.Combo10.Value) Then
  94.  
  95. IngMyEmpID = Me.Combo10.Value
  96.  
  97. 'Close logon form and open Employee records
  98.  
  99.     Dim stDocName As String
  100.     Dim stLinkCriteria As String
  101.     stDocName = "Read Only Records"
  102.  
  103.     stLinkCriteria = "[IngEmpID]=" & Me![IngEmpID]
  104.     DoCmd.OpenForm stDocName, , , stLinkCriteria
  105.  
  106. Exit_Go_Click:
  107.     Exit Sub
  108.  
  109. Else
  110. MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
  111. Me.Text12.SetFocus
  112. End If
  113.  
  114. 'If User Enters incorrect password 3 times database will shutdown
  115.  
  116. intLogonAttempts = intLogonAttempts + 1
  117. If intLogonAttempts > 3 Then
  118. MsgBox "You do not have access to this database.Please contact admin.", vbCritical, "Restricted Access!"
  119. Application.Quit
  120. End If
  121.  
  122. End Sub
  123.  



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]", "strEmployees", "[IngEmpID]=" & Me.Combo10 & ")"
Nov 16 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry this is me again. My brain is dead today

Change ...

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

to ...

pswd = DLookup("[strEmpPassword]", "strEmployees", "[IngEmpID]=" & Me.Combo10)
Nov 16 '06 #6

P: 17
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]", "strEmployees", "[IngEmpID]=" & Me.Combo10 & ")"

to ...

pswd = DLookup("[strEmpPassword]", "strEmployees", "[IngEmpID]=" & Me.Combo10)
Nov 23 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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")
Nov 23 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 23 '06 #9

P: 17
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
Nov 23 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 23 '06 #11

P: 17
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
Nov 23 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. If pswd = "Password" Then
  3. EnterPassword:
  4.  
  5.     pswd = InputBox "Please enter a new password", "Change Password"
  6.     confirm = InputBox "Please enter again for confirmation", "Confirm Password"
  7.     If pswd <> confirm Then
  8.        MsgBox "Passwords don't match. Please try again.", vbOKOnly
  9.        GoTo EnterPassword
  10.     Else
  11.        DoCmd.RunSQL "UPDATE strEmployees SET [strEmpPassword]= '" & Me.pswd & "' WHERE [IngEmpID]='" & Me.Combo10 & "';"
  12.     End If
  13. Else
  14.      'Check value of password in tblEmployees to see if this
  15.      'matches value chosen in combo box
  16.  
  17.  
  18.  
Nov 23 '06 #13

P: 17
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. If pswd = "Password" Then
  3. EnterPassword:
  4.  
  5.     pswd = InputBox "Please enter a new password", "Change Password"
  6.     confirm = InputBox "Please enter again for confirmation", "Confirm Password"
  7.     If pswd <> confirm Then
  8.        MsgBox "Passwords don't match. Please try again.", vbOKOnly
  9.        GoTo EnterPassword
  10.     Else
  11.        DoCmd.RunSQL "UPDATE strEmployees SET [strEmpPassword]= '" & Me.pswd & "' WHERE [IngEmpID]='" & Me.Combo10 & "';"
  12.     End If
  13. Else
  14.      'Check value of password in tblEmployees to see if this
  15.      'matches value chosen in combo box
  16.  
  17.  
  18.  
Nov 30 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
Forgive my ignorance, but where would the End If go then?
Trying to follow your code I would say just before End Sub.
Nov 30 '06 #15

P: 17
Hmmmm......I put the End If in and now it gives me a run time error 13 mismatch message and in the debugger highlights the line:

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

any ideas??


Trying to follow your code I would say just before End Sub.
Dec 8 '06 #16

MMcCarthy
Expert Mod 10K+
P: 14,534
Hmmmm......I put the End If in and now it gives me a run time error 13 mismatch message and in the debugger highlights the line:

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

any ideas??
Where and as what kind of variable have you declared pswd. I don't see it declared in the current code.

Mary
Dec 9 '06 #17

P: 17
I am not really sure....I have given up on this code and have since taken the "easy" way out and have just created a command button that opens another form that the employees can click. This other form is linked to the password table, so they can change it right in the table via the form. I don't know if this is a good way to do it, but I guess it works for now?

Thanks for your continued help and patience though Mary.

Charlie


Where and as what kind of variable have you declared pswd. I don't see it declared in the current code.

Mary
Dec 14 '06 #18

NeoPa
Expert Mod 15k+
P: 31,186
Charlie,

So near to the end, is it a good time to give up?
Try posting your whole procedure exactly as it is now for me please.
I'm sure we can get this working for you (well pretty confident anyway).
Some of these syntax problems can be hard to find but I've had some experience with some doozers in my time - lets give it a try.
Dec 14 '06 #19

NeoPa
Expert Mod 15k+
P: 31,186
A new question was added to this thread as a hijack. In case anyone's interested, the new (related) question can be found at Error 2001: You Canceled the Previous Operation.
Sep 24 '08 #20

Post your reply

Sign in to post your reply or Sign up for a free account.