[OBP]

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

[krs1716]

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

[OBP]

What is me.str?
What was the Error Message?
Where is your Error Trapping?

Why do you need to reset lngMyEmpID anyway?

[krs1716]

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.

[OBP]

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.

[krs1716]

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?

[OBP]

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

[krs1716]

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.

[krs1716]

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?

[krs1716]

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?

[OBP]

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.

[OBP]

You can just use a recordset to update password.

[krs1716]

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?

[OBP]

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

[OBP]

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.