Audit Trail

[boblarson]

Get rid of the .FORM part. When you pass ME to it that IS the form.

So just:

Call AuditTrail(Me, Me![recordid])

(that is, if [recordid] is your field name)

 

[ranjeewaf9]

Hi Bob,

Thanks again. What do you mean by field name here? In the table created for Audit Trail (i.e. tbl_AuditLog) RecordID is the primary key. As all records in all tables are captured in the Audit Trail I thought this should be the field name. Pls. correct me if I'm wrong. Thanks for the help again.

 

[boblarson]

Hi Bob,

Thanks again. What do you mean by field name here? In the table created for Audit Trail (i.e. tbl_AuditLog) RecordID is the primary key. As all records in all tables are captured in the Audit Trail I thought this should be the field name. Pls. correct me if I'm wrong. Thanks for the help again.

If RecordID is the name of your primary key in your table then you would use

Me!RecordID

or

Me.RecordID (if referring to the control that houses the primary key on the form).

 

[ranjeewaf9]

Hi Bob,

When I put "Call AuditTrail (Me. Me!RecordID), I get a compile error: Method or Data Member Not found. Also RecordID (wording) automatically gets converted to lowercase and reads as recordid. But in the table, the field is labeled as RecordID.

 

[boblarson]

I noticed that you have a PERIOD (.) after the first ME. It should be a COMMA (,)

Call AuditTrail (Me, Me!RecordID)

 

[ranjeewaf9]

Hi Bob,

When I put a comma it gives me the same run time error as before saying access can't find the field "recordid" referred to in your expression. The table has that field and it is the primary key of the table. The only difference is the case of letters (RecordId - in the table and recordid in VBA). This automatically changes so I can't do anything to it. I've given a screenshot of the design view of tbl_AuditLog.

 

[boblarson]

Yes, RecordID is in your table. But is it in the FORM'S recordsource or on the form itself? Can you upload a copy of your database with all of the data removed? (if you need a quick tool to do that, you can use my free database reset tool which can strip the data).

 

[ranjeewaf9]

Hi Bob,

I've uploaded the database.

 

[boblarson]

Hi Bob,

I've uploaded the database.

Okay, I'll try not to get too frustrated. You are trying to use an ID which does not exist for the form's recordset.

For Form frm_NewBrnh you would need to use:

Call AuditTrail(Me, Me![Branch ID]) ' NOT RECORDID

You need to use the ID of the form's recordset (which I had stated over and over again).

So, try substituting

Call AuditTrail(Me, Me![Branch ID])

in your form's Before Update event and see what happens.

 

[ranjeewaf9]

Hi Bob,

Thanks a lot. It is working. I was breaking my head on this for so long.

 

[ranjeewaf9]

Hi,

I'm still having an issue with the code. Though the code works perfectly the information logged in the AuditLog table is wrong only for some fields. I have some combo boxes in the form. The table fields bound to them are indexed (with no duplicates). So if I try to select a value that's already there in the table it gives me the error in access that "this will create duplicate values and therefore I cannot save the record now". When I click yes to this message the field doesn't get updated. Yet in the tbl_AuditLog it shows an entry as this field has been changed to the selected value from the combo box when it's really not. How could I overcome this. Thanks for all the help

 

[ranjeewaf9]

Hi,

Could anyone help me please? I have uploaded a copy of the database few posts before. In tables some fields are indexed with no duplicates. When the field is changed to a value already present in the table via form the user gets the error as the change is not commited because if would create duplicate values. Still in the AuditLog table this shows as being changed which is not right. The whole point of having an AuditLog is of no use if changes not committed are recorded as changed. How could I resolve this please? I would appreciate if someone could help me. Thanks.

 

[boblarson]

Hi,

Could anyone help me please? I have uploaded a copy of the database few posts before. In tables some fields are indexed with no duplicates. When the field is changed to a value already present in the table via form the user gets the error as the change is not commited because if would create duplicate values. Still in the AuditLog table this shows as being changed which is not right. The whole point of having an AuditLog is of no use if changes not committed are recorded as changed. How could I resolve this please? I would appreciate if someone could help me. Thanks.

I think you would need to upload another version as the last one doesn't have the code you are currently using. It could be that you don't have your call to the audit log in the right place in the Before Update event OR you need to put validation in your form's Before Update event to check for duplicate values FIRST and cancel the update (Cancel = True) if a DCount of the records shows that field would be duplicated.

 

[ranjeewaf9]

Hi Bob,

Thanks for all your help. I'm new to programing. I've uploaded a copy of the database with this. I would appreciate if you could guide me how to do it. Just show me at least for one field so that I can do the same to other fields too where I don't want duplicate entries. Also one more thing, when using the code to check for duplicates should I still use table properties to use indexing with no duplicates or should I remove them? Thanks again.

 

[boblarson]

Hi Bob,

Thanks for all your help. I'm new to programing. I've uploaded a copy of the database with this. I would appreciate if you could guide me how to do it. Just show me at least for one field so that I can do the same to other fields too where I don't want duplicate entries. Also one more thing, when using the code to check for duplicates should I still use table properties to use indexing with no duplicates or should I remove them? Thanks again.

Okay, see my revision of your sample - see the form frm_NewMach which I have used and the table tbl_Machines which I had to modify to use a MachineID (autonumber) as a record ID.

 

[ranjeewaf9]

Hi Bob,

Thanks for the code. I tried to modify it to suit other data entry forms in the database but then it gives an error. I've attached the screenshots with this. I've tried to modify it for the frm_NewBrnh.

In your sample you've written "Me.Machine_Name". I'm wondering why an underscore is there between the two words as I can't find a control named as such. Sorry if I sound stupid. I would be grateful if you could clarify that to me.

Also, if you could please guide me on how to use the code in a form where there are several fields that need to be checked for duplicates. I'm not sure how to enter the parameters. Should I run several instances of Dcounts or can all the fields to be checked be specified under one? Thanks again.

 

[Medic11961]

I bought an application from epigate software that does all that and alot more. i tried for a long time to create an audit trail but could not get it to work with subforms. this works with subforms so it was worth the money to me. it works on my access 2010 32 bit, but it says it will work with 2003 and 2007. it tells me when a person logs in and out of the application and what changes they made. it shows the before and after values, who made changes and when so it is pretty cool. i tried a ton of code i got off the web but as i said before i wasted a ton of time and could not get it to work with subforms.

 

[ranjeewaf9]

Hi Bob,

Am I doing something wrong when replicating the code for other forms? Thanks.

 

[boblarson]

Hi Bob,

Am I doing something wrong when replicating the code for other forms? Thanks.

1. It looks to me like you are using the wrong control reference on the form when comparing to the ID.

2. post what you have again and I'll see what I can see.

And a note to Medic11961 it works fine with subforms if you put the right function call in.

 

[ranjeewaf9]

Hi Bob,

I've uploaded a copy of the database for your inspection. I've tried to replicate what you've done to the frm_NewMach in frm_NewBrnh but gets an error. Pls. let me know what I'm doing wrong. Also I would like an example how I could use the expression Dcount in a form where multiple controls need to be checked for duplicates (e.g. frm_EdData). Thanks a lot for all your help.