Audit Trail and Find and Replace issue

[ChrisB37]

Hi,
I have been using the audit trail below, which seems to work fine for adding new records. I am trying to set up a form which will allow me to use a Find and Replace Command button to filter to the specific record I need to change. Once I have completed the change and try and log out of the form I get the error below:
2447 - There is an invalid use of the .(dot) or ! operator or invalid parentheses.
I am in desperate need of help to fix this. I need to record these changes to the data on the form in the Audit trail. The change actually occurrs in the table, but there is no audit trail stating the change.

Would someone please be able to tell me if the code for the Audit trail below has an error that would produce the 2447 error above when trying to record changes.

Thanks
ChrisB

Public Function Audit_Trail(MyForm As Form)
On Error GoTo Err_Audit_Trail

'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592


Dim ctl As Control
Dim sUser As String
' sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
' sUser = Environ("UserName") 'get the users login name
sUser = CurrentUser '=Admin if you are not using Access security with user workgroups and permissions

'If new record, record it in audit trail and exit function.
If MyForm.NewRecord = True Then
MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"
Exit Function
End If

'Set date and current user if the form (current record) has been modified.
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"

'Check each data entry control for change and record old value of the control.
For Each ctl In MyForm.Controls

'Only check data entry type controls.
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If ctl.Enabled = True Then
If ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
'If new and old value do not equal
If ctl.Value <> ctl.OldValue Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value & " by " & sUser & "; " & Now
'If old value is Null and new value is not Null
ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value & " by " & sUser & "; " & Now
'If new value is Null and old value is not Null
ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null" & " by " & sUser & "; " & Now
End If
End If
End Select

TryNextControl:
Next ctl

Exit_Audit_Trail:
Exit Function

Err_Audit_Trail:
If Err.Number = 64535 Then 'Operation is not supported for this type of object.
Exit Function
ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window
Beep
MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
Else
Beep
MsgBox Err.Number & " - " & Err.Description
End If
Resume Exit_Audit_Trail

End Function

 

[RuralGuy]

While I am looking at the code, the debugger will highlight the exact line it doesn't like. Which one is it?

 

[RuralGuy]

Why are you using two controls? MyForm!AuditTrail and MyForm!tbAuditTrail. The original code was additive and just kept adding additional data to the MyForm!Updates control. Your code will just overwrite MyForm!AuditTrail each time.

 

[ChrisB37]

I am unsure why there are two controls listed. I just copied this from the net. http://support.microsoft.com/default.aspx?scid=kb;en-us;197592.

I believe it has been discussed here many times on the proper way to set up the audittrail using this code. I have set it up the best I can, but have not seen this problem until I tried to use the Find and Replace Command Button along with it.

Thanks for your help.
CB

 

[RuralGuy]

Hi Chris,
You had posted the link before, which is why I asked the question. Your example from MS keeps adding to a MEMO field of the table in a control/field called Updates. That is not what your code is doing.

 

[ChrisB37]

Can you help me set up the code so that it will continue to add to the memo field?
Also, once I fix that issue will the code work in conjuction with the find and replace button on the same form?


CB

 

[RuralGuy]

Why not start by telling us the name of the field in the table that you use to record the changes. Then tell us the name of the Control to which you bound the field. It *is* a memo field isn't it? Once fixed we can look at the Find and Replace problem.

 

[ChrisB37]

Ok, now I am totally confused. I had this audit trail working fine and I must have done something nuts to it. Now it only records when I have a new record and any change is not recorded. (I believe this is what you were trying to tell me)

Anyway,
The field in my table is called AuditTrail. And yes it is a memo field.
The name on my control is tbAuditTrail.

I have tried going back to look at the original audittrail I downloaded from this site and cannot seem to locate the problem

Thanks,
CB

 

[RuralGuy]

I believe the AuditTrail is now functioning. Maybe a new thread on the Find & Replace issue so everyone will get involved.

 

[ChrisB37]

I am trying to load this new audit trail in and when I go to run I get this error with my Before Update Event.

Ambiguous name detected:Audit_Trail

Here is the Before Update Event. I haven't changed this one yet.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err

Call Audit_Trail(Me)

Form_BeforeUpdate_Exit:
Exit Sub

Form_BeforeUpdate_Err:
MsgBox Err.Number & " - " & Err.Description
Resume Form_BeforeUpdate_Exit

End Sub

Any thoughts?

 

[ChrisB37]

OK, fixed the ambiguous name problem.
I hadn't deleted my previous Audit Trail Module so it was confused.

After fixing this I get this error:

13-Type Mismatch

from previous threads I found this fix.

1. Pass the form as an argument of the function

Function AuditTrail(frm as Form)

2. Comment out these lines in the code
'Dim frm as Form
'Set frm = Screen.ActiveForm

3. Call the function in the BeforeUpdate event of the form and or subform as follows:

Call Audit_Trail(Me)

Now when I do this the audit trail runs without error, but I am not sure I am getting the editting trail, as before. What can I do to fix the 13- Type Mismatch error without doing the suggestions above. I am afraid this has caused my no audit trail on the editting remarks.

Here is the way my audit trail looks after the fix above.
Public Function Audit_Trail(MyForm As Form)
On Error GoTo Err_Audit_Trail

'ACC2000: How to Create an Audit Trail of Record Changes in a Form
'http://support.microsoft.com/default.aspx?scid=kb;en-us;197592

'Dim MyForm As Form
Dim ctl As Control
Dim sUser As String
'Set MyForm = Screen.ActiveForm
' sUser = "User: " & UsersID 'You need to identify your users if you are not using Access security with workgroups.
' sUser = Environ("UserName") 'get the users login name
sUser = CurrentUser '=Admin if you are not using Access security with user workgroups and permissions

'If new record, record it in audit trail and exit function.
If MyForm.NewRecord = True Then
MyForm!AuditTrail = MyForm!tbAuditTrail & "New Record added on " & Now & " by " & sUser & ";"
Exit Function
End If

'Set date and current user if the form (current record) has been modified.
MyForm!AuditTrail = MyForm!tbAuditTrail & vbCrLf & vbLf & "Changes made on " & Now & " by " & sUser & ";"

'Check each data entry control for change and record old value of the control.
For Each ctl In MyForm.Controls

'Only check data entry type controls.
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
If ctl.Name = "tbAuditTrail" Then GoTo TryNextControl 'Skip AuditTrail field.
'If new and old value do not equal
If ctl.Value <> ctl.OldValue Then
MyForm!tbAuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: " & ctl.Value
'If old value is Null and new value is not Null
ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
MyForm!tbAuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Was Previoulsy Null, New Value: " & ctl.Value
'If new value is Null and old value is not Null
ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
MyForm!tbAuditTrail = MyForm!tbAuditTrail & vbCrLf & ctl.Name & ": Changed From: " & ctl.OldValue & ", To: Null"
End If
End Select

TryNextControl:
Next ctl

Exit_Audit_Trail:
Exit Function

Err_Audit_Trail:
If Err.Number = 64535 Then 'Operation is not supported for this type of object.
Exit Function
ElseIf Err.Number = 2475 Then 'You entered an expression that requires a form to be the active window
Beep
MsgBox "A form is required to be the active window!", vbCritical, "Invalid Active Window"
Else
Beep
MsgBox Err.Number & " - " & Err.Description
End If
Resume Exit_Audit_Trail

End Function

Thanks,
CB

 

[RuralGuy]

CB,
You have what is called a moving target. I believe what I posted worked as expected and yet you now have something that does not function correctly. What was wrong with the dAuditTrail module in the attachment I posted? It seemed to function without any errors.

 

[ChrisB37]

Sorry, I am not trying to make this any more difficult.

I took the module as is and posted it into my database. When I tried to run the form upon closing I got the 13-Type Mismatch error.

I had previously done a search on the audit trail info and had found the fix I listed above. I have tried it and it doesn't flag the error any longer, but I am not getting my corrections logged into the database.

Any new record I enter is showing up in the audit trail of my table, so the first part of the module seems to be working. When I enter a change to a record I do not get this recorded in the table. I am however getting the "Invalid use of .(dot) or ! or parentheses" error on some of my forms, only when I try and make a change to the data. The change is recorded into the table correctly just no note of it into the audit trail.

 

[RuralGuy]

CB,
I believe you are going around in circles. You are now back to the very error that started this thread that I believe *had* been corrected. Did the AuditTrail I posted on post 9 work? I worked for me on both new records and edited ones. Where did we get off the track.

 

[ChrisB37]

I copied the audit trail module from your post nine and experienced the Type Mismatch error as soon as I ran it. I made the changes from post 11 and this error went away as I described. I am not sure what other way to edit the module to not flag this error without messing the code up. So no, this code does not work as it is supposed to when implemented. When I do post the code with the changes in 11 I can record to the audit trail in the table for new records only. When I try to do so for changes to the record I get the second error in post 13 (dot).

 

[Banana]

A suggestion- You may want to sidestep now and create another Find button. Have the find button add something trivial like adding a "Found" to the record instead of the replace functionality you are using. Drop the audittrail there.

See if you get no errors, which may point to the Find and Replace button as the problem.

 

[ChrisB37]

Ok, the problem does not lie with the find and replace button. I removed all command buttons from my form and tried making the changes again to the data. Again I can save the data to the table, but I still get the "Invalid use" error.

I had found a discussion about checking references in the code to make sure they were compliant so I loaded the AuditTrail2003-1 and checked to make sure all the references there were the same in my database and this code still flags the error.

Please help. I am so in a time crunch to get this fixed. And I am totally confused. I will try and load part of my database here, it is really huge, so that you can see what I have done.

I will try and get that back here asap.

Any thoughts in the meantime would be great!

Thanks to all.
CB

 

[ChrisB37]

Ok take a look at this. I am hoping it doesn't load as read only, but we shall see. I think you have to extract the file so it isn't read only.

 

[RuralGuy]

Chris,
The problem you are having is you are using generic code from MS that simply checks all Controls where data can be entered including controls that are not bound to fields. You are using unbound TextBoxes and that is where the code is blowing up. The code is already making sure it is *not* dealing with the Audit control (tbAuditTrail) so just add the other unbound control names to the list of controls to bypass (like [Assay 1 Maximum Specs]) and the others. You really need to break yourself of the habit of imbedding spaces in a name of any kind in Access. It *will* bite you. Use CamelFontNames instead. It is very readable and eliminates the problem. Here's some other links you need to read and follow.
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

Post back if you need additional assistance.

 

[ChrisB37]

RuralGuy Thank you so much for helping me eliminate that error. Your suggestion worked.

But I am still having the issue of not being able to record the changes made on the form into the audittrail of the table.

Any thoughts on what is wrong?

CB