Audit Trail

[karinos]

Hi,
My Audit Trail is not working for some reason; i am getting error 2465 when i make any changes to the records. I want Access to record any changes that happens only in the MainPageDetail form and i want to show the history of these changes in the Audit Trail tab which is located under the LOBSelectionTab. I would really appreciate any help or tips from you. Thanks in advance for your time. See attached.

 

[missinglinq]

Your AuditTrail function, that you call from your MainPageDetail form makes reference (MyForm!tbAuditTrail, where MyForm is the active form) to a control on that form named tbAuditTrail. As the error message told you, Access can't find a control on that form by that name, and neither could I!

 

[karinos]

Thanks for your tip but how can i fix this problem? Can you tell me what to do because i have spent a lot of time on this and could not figure out.

 

[missinglinq]

What you need to do is to go back and read/follow the instructions that were given with this code! You've substituted tbAuditTrail for Updates in the function in your db, so you need to do the same thing in the directions below. You also need to substitute the actual name of your form (MainPageDetail) in the directions below for Customers form and the name of your table for the Customers Table in the directions.

5.Open the Customers table in Design view and add a new field called Updates. Set the data type of the field to Memo. Close and save the table.
6. Open the Customers form in Design view.
7. In the BeforeUpdate event of the form, type =AuditTrail().
8. If the field list is not displayed, click Field List on the View menu and drag the Updates field from the field list to the form.
9. Open the form in Form view, make a change to the Company Name field of the current record, and press SHIFT+ENTER to save the record.
Note that the Updates field has an entry showing the change that you made to the Company Name field. You can also hide the Updates field if you do not want to see it on the form.

 

[karinos]

Thanks for your quick feed back but i am wondering why i am getting the error and i thought we can change names of table or forms as long as we reference them correctly.

 

[karinos]

Can any one help me here?

 

[missinglinq]

You can substitute names as long as you reference them correctly, but as I told you, you have not followed the instructions! You have not done this in your database:

5.Open the Customers table in Design view and add a new field called Updates. Set the data type of the field to Memo. Close and save the table.

8. If the field list is not displayed, click Field List on the View menu and drag the Updates field from the field list to the form.

There is no memo field named either Updates or tbAuditTrail in your table nor on your form!

 

[karinos]

Thanks man, it is working now. But i have another problem.. why it is not capturing when i make changes in both the "Comments field" and the "Check box". For instance if i write something on the comment field and check or un check one of the check boxes; it only records one of them and i want to capture both changes. Do you have any idea? See attached.
Many thanks.

 

[missinglinq]

It's not capturing the change in the checkbox control because the code doesn't tell it to:

Case acTextBox, acComboBox, acListBox, acOptionGroup

It checks textboxes, comboboxes, listboxes and option groups.

Try changing that line to

Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckbox

 

[karinos]

I am not sure if you are really looking at my code but it is there: Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox

The problem is that when i make changes to both "Comment Box" and check one of the check boxes; it captures only one of the changes and not both of them. I want access to record if multiple changes i make with in one record. Thanks.

 

[missinglinq]

I've spent an hour or so going over the last copy of your app you posted. The object of the original code you copied from Microsoft is to write changes made to your form to a memo field on that form.

MyForm!Updates = MyForm!Updates & yada yada yada

is supposed to take the contents of the control Updates on the calling form, and add the new changes made to the previous contents. With

MyForm!AuditTrail = Forms!LOBSelectionTab!tbAuditTrail & yada yada yada

you're adding the contents of a control on a different form and trying to add it to the control on your calling form. The problem with that is, although you've added an AuditTrail field to the underlying table of your calling form, the control
MyForm!AuditTrail still doesn't exist! There is no AuditTrail textbox on your main form.

In short, you've taken the code you found online, and without really understanding how it works, have tried to adapt it to you app and have made a real mess of it! It's appropriate when using boilerplate code to replace the object names used in the code with the actual names of your objects, but you have to do so for every instance! If the code uses

MyForm!Updates

and your control is named AuditTrail you have to use

MyForm!AuditTrail

everytime

MyForm!Updates

appears in the code, not MyForm!AuditTrail sometimes and Forms!LOBSelectionTab!tbAuditTrail at other times. To do otherwise is to totally change the function of the code!

Don't feel bad, we've all done similar things when we were starting out! But I can't really help you out here other than to suggest that you scrap what you've done so far, vis a vis the audit trail, and start over again, paying closer attention this time to the instructions.

Or you might want to take a look at this paper from Allen Browne where he details setting up an audit trail and explains what the code actually does:

http://allenbrowne.com/AppAudit.html

Good Luck!