Audit Trail

[connerlowen]

Hi,

I have a database that will have employees enter information that will be used to generate quotations. I would like to include an Audit Trail that will track what is done, when it is done, and by whom is it done. I got a sample code from another source, however it is not working. I will post the code below.



Thanks,

Conner

 

[pbaldy]

Can you be a little more descriptive than "it is not working"? Do you get an error, and if so what is it?

 

[connerlowen]

there is no error at all. It seems like it is not running and not doing anything. I made a test form to start adding information in a table, but the audit trail table is empty. I wish it was giving me something to work off of.

 

[connerlowen]

Yes the Tag in the Test form is Audit.

 

[pbaldy]

Are you calling the function from the before update event of the form?

 

[connerlowen]

Yes, This gave me an error message "Database can't find the field 'EmployeeID' referred to in your expression."

 

[pbaldy]

Is that your ID field?

 

[connerlowen]

the "EmployeeID" field is an autonumber in the EmployeeT table.

 

[pbaldy]

And there's a control on the form with that name? Can you post the db here?

 

[connerlowen]

The form has nothing to do with the employees, other than the fact that the employees will be entering the data. I am not sure what you mean by control, and I am not sure if I can post the database.

 

[pbaldy]

This line, specifically the bit in red:

![RecordID] = Screen.ActiveForm.Controls(IDField).Value

is going to use the field name you passed to populate the RecordID field in the audit table. From fontstuff:

Along with the name of the procedure I have included a parameter which I have named IDField. When calling the procedure you will need to supply a value for this parameter which will be the name of the field that identifies the current record.

 

[connerlowen]

The RecordID that you are referring to should be the QuotatoinID because that is the thing they are editing.

 

[pbaldy]

So your code in the before update event should use that field instead of EmployeeID.

 

[connerlowen]

I made the change, and not I am getting another error saying that "Database can't find the field 'QuotationID' referred to in the expression", but it is there.

 

[connerlowen]

I had to add the field to the form, and delete it. It seems to be working just fine now. Thank you very much for your help.

 

[pbaldy]

No problem. You had it spelled 2 different ways here, so maybe that was the problem.

 

[connerlowen]

That came up a couple of times in the database as well. However, I fixed them and this feature seems to be working correctly. I will be able to test more as time goes on.