1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Create a "Change Password" form for Login in Access 2007

Discussion in 'Business Applications' started by krs1716, Apr 27, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Your Password change form doesn't actually need a combo a normal text field would do, then you could have
    "[lngEmpID] =" & me.[lngEmpID]

    To test the passwords just use something like
    if me.password1 <> me.password2 then
    msgbox "Sorry Passwords do not match"
    me.password2 = ""
    me.password2.setfocus
    end if
     
  2. krs1716

    krs1716 Thread Starter

    Joined:
    Feb 10, 2012
    Messages:
    438
    Haven't had a chance to try that yet. Something is wrong with my VBA...Surprise!!!

    This is what I have (what's in red is what Access is highlighting as an error):

    Private Sub Command1_Click()
    '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.lngEmpID.Value) Then
    lngMyEmpID = Me.Str.Value
    'Close Change Password Form
    DoCmd.Close acForm, "Change Password Form", acSaveYes
    Else
    MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
    "Invalid Entry!"
    Me.txtPassword.SetFocus
    End If
    If Me.password1 <> Me.password2 Then
    MsgBox "Sorry 'New Password' and 'Confirm Password' do not match"
    Me.password2 = ""
    Me.password2.SetFocus
    End If
    End Sub
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    What is me.str?
    What was the Error Message?
    Where is your Error Trapping? ;)

    Why do you need to reset lngMyEmpID anyway?
     
  4. krs1716

    krs1716 Thread Starter

    Joined:
    Feb 10, 2012
    Messages:
    438
    I should clarify...I didn't write a word of this. I took some of it from the Login button on the Login form, and then, I just pieced together some things that looked right from a variety of sources (none of which I remember).

    So...I have no idea what me.str is. The error message was "Compile Error: Method or data member not found." What is error trapping? And I have no idea why I need to reset lngMyEmpID.:D
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    So just delete that line.
    Error trapping is using the OnError event to trap the error and provide an error message, because you don't always get one.
    One of the most dangerous things to use in the on error is Resume next as you can get in to all sorts of trouble, it is much better to stop code execution to sort out the error.
     
  6. krs1716

    krs1716 Thread Starter

    Joined:
    Feb 10, 2012
    Messages:
    438
    I deleted the line. Now an error message with the following explanation pops up: "Run-time error '2467': The expression you entered refers to an object that is closed or doesn't exist." It currently highlights "If Me.password1 <> Me.password2 Then"

    As far as error trapping goes...do I put that as a separate On Error event for the whole form?
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Do you have fields called Password1 & Password2 to enter the passwords?
    The error trap goes in each module, the On error goto errortrap
    goes after the Private Sub Command1_Click() line
    The rest of goes before the End Sub
    Exit Sub
    errorcatch:
    MsgBox Err.Description
     
  8. krs1716

    krs1716 Thread Starter

    Joined:
    Feb 10, 2012
    Messages:
    438
    I think the error message is because I have the form closing after the original password is verified (whoops). If I put Me.password1.SetFocus after the then instead of closing the form, will that be a valid statement?

    I will try error trapping as soon as this problem is resolved.
     
  9. krs1716

    krs1716 Thread Starter

    Joined:
    Feb 10, 2012
    Messages:
    438
    Um...just noticed today that the audit trail isn't tracking correctly. It isn't tracking all changes to all forms (that have the before update VBA) and it doesn't always recognize who is making the changes.

    Thoughts?
     
  10. krs1716

    krs1716 Thread Starter

    Joined:
    Feb 10, 2012
    Messages:
    438
    I think I fixed the problem (an error message is no longer popping up). I added the Line MsgBox Err.Description after PrivateSub Command1_Click(). Now I need the VBA to change the original password to the new password in tblEmployees.

    I found a code that looks like it might work:
    Dim strSQL As String
    strSQL = "UPDATE A INNER JOIN B ON A.ID = B.ID SET B.Field1 = <Some value>"
    CurrentDb.Execute strSQL, DbFailOnError

    Will this work with some alterations to the field references?
     
  11. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    That would be a valid statement.

    I am not sure why the audit trail is not working, is there any pattern to the changes it does not register?
    The Public Variable to identify the user should be available at all times, unless some other code resets it.
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    You can just use a recordset to update password.
     
  13. krs1716

    krs1716 Thread Starter

    Joined:
    Feb 10, 2012
    Messages:
    438
    The audit trail doesn't seem to recognize who adds a record. It does know who edits a record. On that note, does the audit trail work on continuous forms and/or memo fields?

    How would I use a recordset to update the password?
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    This piece of code opens a query based on the current Password (it could use the UserID), you would then add a couple of lines of code to edit the Password to new one. You would obviously need to use your Table name.
    Dim rs As Object, SQL As String
    On Error GoTo Eventerror
    SQL = "SELECT ULS.* " & _
    "FROM ULS " & _
    "WHERE Password = '" & Me.Password & "'"
    Set rs = CurrentDb.OpenRecordset(SQL)
    If rs.RecordCount = 0 Then

    The code to edit the password would be
    with rs
    .edit
    .password = me.password2
    .Update
    .Bookmark = .LastModified
    End With
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    In the Audit Trail code

    If Me.NewRecord Then
    action = "Added Record"
    With rstable
    .AddNew
    !UserID = myuserid
    !Form = Me.Name
    !action = action
    ![Action Date/Time] = Now()
    .Update
    .Bookmark = .LastModified
    End With
    rstable.Close
    Set rstable = Nothing
    Exit Sub

    Add a Message box after the
    action = "Added Record"
    msgbox "action is " & action
    that will tell you if it identifying a New Record correctly

    It will only work for a brand new record, once the record is saved any edits will be shown as edited.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1051023