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



## krs1716 (Feb 10, 2012)

Hello All! I have created a database that uses a login form that directs people to specific forms based on priviledges. However, I would like to provide a form that allows users to change their passwords (preferably with a confirm password box). Also, I have one user defined as Administrator, with a special form. I would like to create a special form for the Administrator to be able to add/delete/edit users and their security levels.

I found similar threads, but none that answered my specific questions- relative to my current setup.

Thoughts?


----------



## OBP (Mar 8, 2005)

Did you use my Login Form?
It would only take a minor modification to create what you need for changing the password.


----------



## krs1716 (Feb 10, 2012)

I don't know if I'm using your login form. I found coding and references in multiple places and pieced them together to create a login form that worked for me.

The database I'm referencing is the same one that we've discussed in other threads (i.e. Creating a monthly query/report in Access 2007)


----------



## OBP (Mar 8, 2005)

It is not my login form.
Do you want to use a VBA Recordset or SQL to update the tblEmployees table or use that table as the record Source for the Password change form?


----------



## krs1716 (Feb 10, 2012)

I don't really know what the benefits/faults are of each method...so it doesn't really matter to me. Do you think that one method is better than the other?


----------



## OBP (Mar 8, 2005)

The simplest method is to use the table/query as the Form record source.


----------



## krs1716 (Feb 10, 2012)

That works for me. Do I just start by making a form with that as the record source and then add the CBS?


----------



## krs1716 (Feb 10, 2012)

Whoops...vba. Autocorrect at work.


----------



## OBP (Mar 8, 2005)

Yes, that is the way, but you could hide the fields if you want and use unbound fields for the updating on the form and then VBA to update the hidden fields.


----------



## krs1716 (Feb 10, 2012)

Well, my original thought was that after employees chose their name, typed in their password, and clicked login, a form or message box (not sure which would be best) would appear asking if they would like to change their password. If they chose "No", they would be directed to the home page. If they chose yes, they would be directed to a form that showed their Name (unchangeable field) and a box where they type in the old password, new password, and confirm new password. VBA would then confirm the old password and check to make sure the new password and confirm new password were the same before changing the password in the table. Not sure how that fits into what you mentioned, but I'm slightly confused about the unbound fields and hidden fields.

Is that too complicated? Is there a better way? Would I be better off changing my login form to your login form and going about this from that perspective?


----------



## OBP (Mar 8, 2005)

Your method would work well, can I suggest that you just have a button on the Form for "Change Password" rather than a pop up?


----------



## krs1716 (Feb 10, 2012)

A button on the Home page?


----------



## OBP (Mar 8, 2005)

Yes or the login form?


----------



## krs1716 (Feb 10, 2012)

Okay. I put a button on the Home page, but I don't know how to make the right change password form to open. As of now, it opens a blank version.


----------



## OBP (Mar 8, 2005)

Have you created the new Password Change form?


----------



## krs1716 (Feb 10, 2012)

Yes. Should it have a record source?


----------



## OBP (Mar 8, 2005)

I think that would be the easiest way to do it.


----------



## krs1716 (Feb 10, 2012)

Okay. That's how I did it, but it doesn't bring up the person who logged in.

Also, my audit trail no longer works. I looked through the forum and found mention of one you made. Would that one work for me? If so, would you mind sending it to me?


----------



## OBP (Mar 8, 2005)

I posted it on here recently, along with the password table/form, it stores the Logged in person in a Public Variable
See http://forums.techguy.org/business-applications/1050513-solved-anyone-pls-recording-all-2.html
post #17.
The auditing stores the changes made to the individual fields (also stored) of the form containing the VBA, storing before & after values.


----------



## krs1716 (Feb 10, 2012)

The event procedure for the password uses ULS. How do I get it to use my setup?


----------



## krs1716 (Feb 10, 2012)

Do you know what "Microsoft Outlook 14.0 Object Library" is? It's in my References as "MISSING" and now my login form no longer works


----------



## OBP (Mar 8, 2005)

You would need to use your table and employee ID.
The missing Reference is to your version of Outlook, untick the missing one and find Microsoft Outlook on the Reference List and tick that.
Assuming that your VBA Editor is the same as mine you use Alt F11, Main Menu>Tools>References


----------



## krs1716 (Feb 10, 2012)

Okay. I finally figured out the MISSING issue (used 2010 at home, but 2007 at work)- formatting error.

However, I'm still trying to figure out the audit trail. I have an audit trail set up, but it doesn't record new records, and it doesn't work on subforms. I tried to read over yours, but I'm completely lost in the VBA language. Do you have any tips, tricks, ideas, suggestions? Anything?


----------



## krs1716 (Feb 10, 2012)

As for the logged in public variable: I have one established, but it reads "Public lngMyEmpID As Long" when my Employee ID is called IngEmpID. Could that be the issue with my change password form?


----------



## OBP (Mar 8, 2005)

Have you imported my audit trail table, query and form?
To use my vba you only need to paste it in to the Before Update event of any form, including subforms.
The only change you need to make is to these two identical lines

!UserID = myuserid

to

!UserID =lngMyEmpID


----------



## krs1716 (Feb 10, 2012)

I did. I'm having problems because my login isn't called ULS.


----------



## krs1716 (Feb 10, 2012)

I finally got the table to record changes as designed. However it doesn't recognize a User ID. I'm feeling really lost on this subject. Every time I think I have the solution in hand, I either forget or my concept doesn't work.

So...that being said. How can I make it so that IngEmpID is registered as the User ID no matter what is happening in the database?


----------



## krs1716 (Feb 10, 2012)

Yay!!!! The audit trail works. I just had to restructure the query and the form to reflect the names of my tables/queries and their respective fields.

Now...back to the change password form. How would I get the database to register that the person logged in is the person to change the password? Will it even work with multiple users logged in at the same time?


----------



## OBP (Mar 8, 2005)

Yes it should work with any number of users.

I thought your login form put the userID in the Public variable lngMyEmpID, that is what this code does

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
"[lngEmpID]=" & Me.cboEmployee.Value) Then

* lngMyEmpID = Me.cboEmployee.Value*

That is why I suggested the code change to my VBA in post #25


----------



## krs1716 (Feb 10, 2012)

Okay. Maybe the problem that I'm having is in the setup of my Change Password Form. I currently have the IngEmpID hidden. Then I have the Employee Name (strEmpName) field un-enabled, so that nobody can change another person's password. Then I have three unbound fields: one for the current password, one for the new password, and one to confirm the new password.

The problem that I'm currently experiencing is that when I open the form, it will only ever show Employee 1. Therefore, it's not recognizing the current user.


----------



## OBP (Mar 8, 2005)

Change the user name Combo to a "Find" combo, this will find the record for that person in the form.


----------



## krs1716 (Feb 10, 2012)

Okay. I created a Find Combo, but how do I get it to automatically pull the record for the person who is signed in?


----------



## OBP (Mar 8, 2005)

A find combo should do that automatically.
Are you sure it is a "find" combo.


----------



## krs1716 (Feb 10, 2012)

I chose the "Find a record on my form based on the value I selected in my combo box." option. Is that what I wanted to do?


----------



## OBP (Mar 8, 2005)

Yes, when you select the user for the password entry the form should move to the correct record, have you made the fields visible to see what is happening?


----------



## krs1716 (Feb 10, 2012)

Yes, I have all fields visible. I think I misrepresented my problem. I can choose anyone I want from the drop down menu, and the database knows to bring up that person's information. However, I wanted the form to recognize who was logged in, and automatically bring up that person's name and ID number. Then, I could disable the field, so that people can only have access to their own information.

Does that make sense? Can I do that?


----------



## OBP (Mar 8, 2005)

Sorry, I thought you were talking about the original Log in form, but now I assume that you are talking about the Change password form?
You can use VBA to filter the form when it is opened using the public variable.


----------



## krs1716 (Feb 10, 2012)

Okay...sorry for the miscommunication.

Yay! VBA!!! Is it possible/probable for me to do it or find it?


----------



## OBP (Mar 8, 2005)

Look at the form opening button's VBA it uses the variable 
Dim stLinkCriteria As String
add a line of code 
stLinkCriteria = lngMyEmpID


----------



## krs1716 (Feb 10, 2012)

That didn't work. Could I be missing something?


----------



## OBP (Mar 8, 2005)

No you are not missing anything, it is an Access 2007 bug.
The alternative is to add this to the Password Form's On Load Event Procedure.

Me.Filter = "[lngEmpID] = " & lngMyEmpID


----------



## krs1716 (Feb 10, 2012)

That still doesn't make the employee's name appear automatically. Do I need to link the forms together or something like that? I think I read an article on that a couple of weeks ago.


----------



## OBP (Mar 8, 2005)

It works for me. Do you want me to send you your database with the change in it?


----------



## krs1716 (Feb 10, 2012)

That would be great. Thanks.


----------



## krs1716 (Feb 10, 2012)

Yay! That worked. Just a note...the issue with my Change Password form vs. yours is that you had the field from the table representing the employee (i.e. strEmpName). I had an unbound combo box.

So sorry for the issues. I guess I had to see it to understand it.

Now, one more thing on this subject ...I have some VBA code written that I think will check the value of the Old password to see if it is correct. However, I have no idea how to write the code to check the "New Password" field against the "Confirm New Password" field and then use that confirmation to change the password in the employees table (tblEmployees).

The code I have to check the value in the Old Password field is as follows (after clicking a button called "OK":
Option Compare Database
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.cboEmployee.Value) Then
lngMyEmpID = Me.cboEmployee.Value
'Close Change Password Form
'Close Change Password form
DoCmd.Close acForm, "Change Password Form", acSaveYes
End If
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If
End Sub


----------



## OBP (Mar 8, 2005)

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 (Feb 10, 2012)

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 (Mar 8, 2005)

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

Why do you need to reset lngMyEmpID anyway?


----------



## krs1716 (Feb 10, 2012)

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 (Mar 8, 2005)

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 (Feb 10, 2012)

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 (Mar 8, 2005)

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 (Feb 10, 2012)

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 (Feb 10, 2012)

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 (Feb 10, 2012)

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 (Mar 8, 2005)

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 (Mar 8, 2005)

You can just use a recordset to update password.


----------



## krs1716 (Feb 10, 2012)

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 (Mar 8, 2005)

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 (Mar 8, 2005)

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.


----------



## krs1716 (Feb 10, 2012)

I haven't had a chance to add those changes. However, when adding a new contact to event details. The following error message pops up:

"A problem occurred when Microsoft Office Access was communicating with the OLE server or ActiveX Control. Close the OLE Server and restart it outside of Microsoft Office Access. Then try the original operation again in Microsoft Office Access."

It then won't save the changes. Another error message appears:

"The expression Before Update you entered as the event property setting produced the following error: '[the above error message appears here]."

The explanation for this error is as follows:
"This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to *=[Field]*, this error occurs because Access expects a macro or event name to run when the event is fired."

Thoughts?


----------



## OBP (Mar 8, 2005)

First of all I would check that all the required VBA Library References are still present, ensure you don't have any missing ones.
Do you get the opportunity to Debug the error to see if a line of code is causing the problem.
Also ensure that any Event Procedures on the form do have "Event Procedure" in them and not something else.


----------



## krs1716 (Feb 10, 2012)

Well, I tried to compile the database, but it keeps getting stuck on "On Error GoTo Eventerror" from the change password form. So, I can't tell what is the problem in the event details.

There are no missing references (OLE Automation is selected, though).

One of the buttons I have on the form ("Delete Record") is actually an embedded macro. Does it need to be changed to an Event Procedure in order for the Before Update Audit Trail to work?


----------



## krs1716 (Feb 10, 2012)

I finally checked the added function of the audit trail. I realized that the audit trail recognized that data was added, but it didn't recognize who added the data. So, I went in and checked the VBA, and I found that I had mistyped an "l" for an "I". Problem seemingly solved. 

Thank you!


----------



## OBP (Mar 8, 2005)

Is the event error fixed as well?


----------



## krs1716 (Feb 10, 2012)

No. The event error is not fixed (I still can't add any attendees to the events). I've actually had several reports of issues with the database. It seems to have something to do with the audit trail. The message says that Access doesn't recognize the forms and subforms referenced in a macro or Event Procedure.

Did I read right in another thread? Did you install 2007?


----------



## OBP (Mar 8, 2005)

Yes, my old computer is a bit broken due to the HP/Nvidea Graphics card overheating problem, so I had to buy a new one, I ahve had a copy of Office 2007 for a few years but avoided like the plague. Now I know it was the right thing to do.

Have you actually got an error trap called Eventerror?


----------



## krs1716 (Feb 10, 2012)

I'm sorry for your loss. I have never used 2003, so I don't know the troubles between the versions. I only know the trouble I have experienced with 2007.

No, I don't have an error trap called Eventerrror. I have now deleted that line. However, does that mean that there is no error trapping now?

I'm about ready to curl in a ball and forget this whole thing. People are now refusing to use the database because of error messages. I can't even begin to fathom how to fix these error messages they are complaining about because I don't get to see them. I only get a brief description. (though, like I said before, I think it has something to do with using the audit trail on the subforms-based on the descriptions).


----------



## OBP (Mar 8, 2005)

Yes it means that you have no error trapping, but you had no error trapping anyway, because it couldn't go to where it should.
All you need to do is re-instate that line and add the error trap at the end of the code this is what it looked like in the original after the line
Set rstable = Nothing


Exit Sub
Eventerror:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description


----------



## OBP (Mar 8, 2005)

I would ensure that you give it some testing before handing it over to the users.


----------



## krs1716 (Feb 10, 2012)

I added those lines, but now a blank message box comes up, and no error is reported. It returns to the Change Password Form and it doesn't actually change the password. Here is the code:

Private Sub Command1_Click()
MsgBox Err.Description
'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
Me.password1.SetFocus

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
Dim rs As Object, SQL As String
On Error GoTo Eventerror
Set rstable = Nothing

Exit Sub
Eventerror:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
SQL = "SELECT tblEmployees.*" & _
"FROM tblEmployees " & _
"WHERE strEmpPassword = '" & Me.txtPassword & "'"
Set rs = CurrentDb.OpenRecordset(SQL)
If rs.RecordCount = 0 Then
With rs
.Edit
.strEmpPassword = Me.password2
.Update
.Bookmark = .LastModified
End With
End If
'Close Change Password Form
DoCmd.Close acForm, "Change Password Form", acSaveYes
End Sub

I apologize for this continual pestering, but I am at a complete loss as to what to do.


----------



## OBP (Mar 8, 2005)

Sorry that is not my original code, which is On Error GoTo Eventerror
Dim rs As Object, count As Integer, data() As Variant, rstable As Object, db As Object, action As String
Set rs = Me.RecordsetClone
fieldcount = rs.Fields.count - 1
rs.Close
Set rs = Nothing
Set db = CurrentDb
Set rstable = db.OpenRecordset("Action Audit Trail")
ReDim data(fieldcount, 2)
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
Else
action = "Edited Record"
End If
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
For count = 0 To fieldcount
data(count, 1) = rs(count).Name
data(count, 2) = rs(count).Value
If IsNull(data(count, 2)) Then data(count, 2) = "None"
For Each ctl In Forms(Me.Name).Controls
If ctl.ControlType = acTextBox Then
If ctl.Name = rs(count).Name And ctl.Value <> data(count, 2) Then
With rstable
.AddNew
!UserID = myuserid
!Form = Me.Name
!FieldName = ctl.Name
!action = action
![Action Date/Time] = Now()
![Old Value] = data(count, 2)
![New Value] = ctl.Value
.Update
.Bookmark = .LastModified
MsgBox "added Record " & rs(count).Value & " becomes " & ctl.Value
End With
End If
End If
Next ctl
Next count
rs.Close
Set rs = Nothing
rstable.Close
Set rstable = Nothing
Exit Sub
Eventerror:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

so you would need to place it before the 
End Sub
in your version.


----------



## krs1716 (Feb 10, 2012)

I tried fixing it, but now a blank message box appears and the Change Password Form closes without changing the password to the new version.


----------



## OBP (Mar 8, 2005)

What does your code look like now, because you had to move the code from where you had it to the end of the Sub, if you left it where it was it will kick you out of the code.

Of course you could send me your copy.


----------



## krs1716 (Feb 10, 2012)

The database is currently in use, but as soon as I can, I will send you a copy of it. If you have 2007 now, I shouldn't have any problems with formatting and such.

If you could, try adding a customer, and see if you get any error messages that relate to the audit trail. I have a lot of users getting very irritated with me over that issue, and I don't know how to approach it.

Thank you! Thank you! Thank you!


----------



## OBP (Mar 8, 2005)

Don't forget you have change the code in every, form/subform where it is used.
It may be possible to move it to a Module, but I have never tried it.


----------



## OBP (Mar 8, 2005)

By the way you should have your own copy of the database for "Development" work, when you have got the change working then import the changed objects in to the Users version or replace the whole database.


----------



## OBP (Mar 8, 2005)

I have emailed you a new version.


----------



## krs1716 (Feb 10, 2012)

I e-mailed you back. I can't get in the database because I don't know the password. The good news...my plan works.


----------



## OBP (Mar 8, 2005)

What "Password" and what "Plan"?


----------



## krs1716 (Feb 10, 2012)

Password to login. My plan to lock people out who don't know what they are doing...in this instance, that person was me.


----------



## OBP (Mar 8, 2005)

Greeeaaat Plan.
You do know you can bypass the Login form don't you?

PS Well done spotting my "Deliberate Mistake", I was using the Microsoft programming principle of letting the user find the errors. 

PPS you can also stop the bypassing of the Login form as well.


----------



## krs1716 (Feb 10, 2012)

I know that you can hold in the Shift key and all of my careful planning falls apart. However, it is nice to have it right now in case something goes fearfully wrong. I plan to disallow that soon.

Great Principle! Glad to accomodate


----------



## OBP (Mar 8, 2005)

Actually on second thought, I don't think I use the table ID field in my code, it is only there because I copied the format of your original Audit Module.
So you could test it and see if it makes any difference.


----------



## OBP (Mar 8, 2005)

You can set up the bypass to be switched on and off when you like.


----------



## krs1716 (Feb 10, 2012)

How do I set that up?


----------



## OBP (Mar 8, 2005)

I should have known you would ask that. 
See this MS article on the subject.
http://support.microsoft.com/kb/826765
You could base it's use on the login form as to who could reset it.


----------



## krs1716 (Feb 10, 2012)

Yes...you should have known I would ask. I will check that out soon.

About the Audit Trail...
It worked for Events, but when I tried to alter information in Contact Details, I encountered an error. I had taken out the Record ID. When I took that out, I also took out the "Me" in "(*Me*, [Record ID])". Did I need to keep that? Because now Access is telling me that the argument is not optional.


----------



## OBP (Mar 8, 2005)

Me is needed, it is the form that you are in.


----------



## krs1716 (Feb 10, 2012)

Scratch the "It worked for Events". It didn't work for events, so apparently I need the argument including the Record ID.


----------



## OBP (Mar 8, 2005)

Sorry that I wasn't clearer, you need the argument, but it shouldn't matter what it is as far as the VBA is concerned.


----------



## krs1716 (Feb 10, 2012)

So, I could have left them all with CareerPlanID and it wouldn't have mattered?


----------



## OBP (Mar 8, 2005)

That is what I was suggesting you test.


----------



## krs1716 (Feb 10, 2012)

I missed that reference. Today has been a tough one, and I think my brain is just about finished. I may resort to spending the rest of the day on data entry and leave all the problem solving for later.


----------



## OBP (Mar 8, 2005)

It can get like that, some days I just want to sit and watch some sport on TV.


----------



## krs1716 (Feb 10, 2012)

Just got an error: "Compile Error: ByRef Argument Type Mismatch" when I switched from Services tab to Training tab in Contact Details. It highlights "Private Sub Form_BeforeUpdate(Cancel As Integer)
Call TAuditTrail(Me, ID)
End Sub"

I have no idea how to fix this issue...Ahhhhhhh!!!

Going to lunch...be back in 30 minutes or so.


----------



## OBP (Mar 8, 2005)

If you were in the top subform Subform_Training Categories called "Customer Services" then there is no field called ID , there is however "TRAININGID" which is probably the correct Key field for the Training table and that form.
That suggests that you do need the correct key field, which the VBA code is going to check.
You could delete that from the Sub Heading and make it 
Sub TAuditTrail(frm As Form)
Yes That works I have just used it with this
Call TAuditTrail(Me)
in the Career Plans form.
So you could just delete the second part from all the forms.


----------



## krs1716 (Feb 10, 2012)

Can you look at the Contact Detail form and see if you notice a Macro or Event Procedure that references CustomerServices S?

An error message keeps interrupting me telling me that it can't find the form CustomerServices S referred to in a Macro or Visual Basic code. I can't find any such reference.


----------



## OBP (Mar 8, 2005)

Customer Services S was your original subform when we first split the data on the Services tab.
When do you get the message?
I will be going out soon.


----------



## krs1716 (Feb 10, 2012)

Well, I have now changed the form back to CustomerServices S because I thought that was the problem. However, I have now noticed that the error message appears for Exit Information, Training and Services. It apparently has something to do with trying to close the form before performing some action, but I can't figure out the direct correlation.

The message seems to appear after changing information and then trying to exit the main form.


----------



## OBP (Mar 8, 2005)

I don't get that error when entering data in to the Contact Details form.


----------



## krs1716 (Feb 10, 2012)

Well, I am at a different job today, and I brought the database along with me, and I don't get the error message either. Also, my "Making Changes" copy and the copy you sent me do not get the error messages, but on the copy that everybody else accesses gives the error message. I have no idea how to approach this issue since there is no rhyme or reason to the errors.


----------



## krs1716 (Feb 10, 2012)

Okay. I finally got the message in the database that I'm working on now. I'm wondering if it has something to do with another error message.

How I got the error message to appear: I edited information on the Exit Information tab, and there was a problem with the Industry Cluster drop down menu. So, I removed the data from that field, saved the rest of the data changes, and fixed the Industry Cluster drop down menu. Then, I went in and tried to edit the data on the Exit Information tab again. That is when the error message appeared (only after fixing something else).


----------



## OBP (Mar 8, 2005)

Have you done a Compact & Repair after making the change?


----------



## krs1716 (Feb 10, 2012)

No. I always get nervous doing those. One time I did that, and all of my macros got messed up. Should I be doing those after each change I make to the database?


----------



## OBP (Mar 8, 2005)

Certainly after any major programming or if you have issues like this one.
You can always take a backup copy prior to the Compact & Repair.


----------



## krs1716 (Feb 10, 2012)

For now, compacting the database seems to have fixed the problem with the Contact Details form. However, the OLE issue in the Event Details is still occurring, but it does allow it to save if I don't have the audit trail on the Event Attendees form. I don't know if it has anything to do with the fact that I have the form displayed in Datasheet view.


----------



## OBP (Mar 8, 2005)

It looks as if the Module version of the Audit Trail does not work with subforms as the subform is not apparent to the code as it is on another form, whereas the code on the subform works because it knows the form it is in.
I obviously have to do some more work on this to see if it possible for it to work with subforms.
Do you know if your original module code worked with subforms?


----------



## krs1716 (Feb 10, 2012)

I don't think that it recognized any changes to the subforms. No error message came up, but it didn't record the changes.


----------



## krs1716 (Feb 10, 2012)

I found an interesting audit trail at this website: http://allenbrowne.com/appaudit.html.

I don't understand the coding or some of the steps, though. I don't know if any of the elements of the coding or formatting could help troubleshoot my problem.


----------



## OBP (Mar 8, 2005)

this does it.


----------



## krs1716 (Feb 10, 2012)

Um...I can't open the attachment.


----------



## OBP (Mar 8, 2005)

You just need to import the Module in the database.


----------



## krs1716 (Feb 10, 2012)

I still can't save changes to Event Attendees if I use the new Audit Trail. I believe that the error messages in Contact Details are fixed, but Events is still a disaster. An error message appears about the OLE no matter what I do to it, and if I add the Audit Trail to Event Attendees, any new additions to the attendees are not saved, either.

P.S. Any thoughts on recording deletions in the audit trail?


----------



## OBP (Mar 8, 2005)

If you remember I fixed the OLE error message a couple of days ago, I sent you a database with 3 versions of the Event attendees subform.
Try one of those in place of your current one.
Yes deletions could be added.


----------



## krs1716 (Feb 10, 2012)

I did replace the Event Attendees form with one of your versions, and it seems to be working. I think that I did that change to my version, but I forgot to make the switch on the regular version.

I tried to look to see if I could add "deletions" as an option in the Audit Trail, but I failed miserably. I couldn't find an action frm.deletions, and I didn't know how else to word it.


----------



## OBP (Mar 8, 2005)

Well it depends on how you want to do the deletions, if you use a Command button on the form to control the deletion you can pass a variable to another Module to just enter a record in the Audit trail table that says deleted record.


----------



## krs1716 (Feb 10, 2012)

I do have a command button that is delete, but it deletes the whole record. Is that what you mean?


----------



## OBP (Mar 8, 2005)

Yes, for deleted data in fields the current one will work, ie original data = "test", new data = "" because it was deleted.
So for record deletion we can use another module.


----------



## krs1716 (Feb 10, 2012)

Ahhh...I see. Will the new module affect the "Call TAuditTrail (Me)" that is included in each form/subform?


----------



## OBP (Mar 8, 2005)

No, because it would be in the Command Button's procedure.
I prefer not to actually delete data in most cases, I prefer to set a Check Box called deleted or archived which excludes the records from the normal forms, but can be used to show them if required in a query or on another form.
That way if someone deletes the wrong record the old record can be re-established at the click of the mouse.


----------



## krs1716 (Feb 10, 2012)

Okay. I may use that for Customer information, but I will keep delete for events because people often erroneously enter the same event twice, so we would actually want to delete it.


----------



## OBP (Mar 8, 2005)

So I suppose you want a module now, do you want it just for events?


----------



## krs1716 (Feb 10, 2012)

If you have an opportunity, I wouldn't mind  Just events would be sufficient.


----------



## OBP (Mar 8, 2005)

For just one form we could just VBA in with the current vba.


----------



## OBP (Mar 8, 2005)

I don't see any event forms with a delete button?


----------



## krs1716 (Feb 10, 2012)

That's because I forgot to put it on there.  Whoops! I will include it, and then send you a new version in an e-mail.


----------



## OBP (Mar 8, 2005)

I have sent you an updated version, for anybody following the Audit trail theme here is the code

Dim response, rstable As Object, action As String, db As Object
On Error GoTo Eventerror
response = MsgBox("Are You Really Sure You Want to Delete Record?", vbYesNo + vbExclamation + vbDefaultButton2)
If response = vbNo Then Exit Sub ' User chose No.
'User Chose Yes
action = "Deleted Record - " & Me.Title ' Me.Title should be replaced with whatever field data you want to record
Set db = CurrentDb
Set rstable = db.OpenRecordset("Action Audit Trail")
With rstable
.AddNew
!UserID = lngMyEmpID ' lngMyEmpID should be replaced with whatever User ID key field Public Variable you use
!Form = Me.Name
!action = action
![Action Date/Time] = Now()
.Update
.Bookmark = .LastModified
End With
rstable.Close
Set rstable = Nothing
DoCmd.RunCommand acCmdDeleteRecord
Exit Sub
Eventerror:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description


----------



## krs1716 (Feb 10, 2012)

I just installed your latest update, and it seems to working wonderfully. Thank you so much!!!


----------

