Allen Browne's Audit Trail - Problem with Index PrimaryKey

[Aine McElroy]

Hi All,

I'm a newbie here so please be gentle! I have a main form with 5 subforms run off it. I have copied Allen Browne's Audit Trail code (thanks to Allen Browne) and I have everything working fine, the only problem that I have is when I try to delete a record in one of my subforms (I'm in test stage at the mo) I get a run time error 3022 'The changes you requested to the table where not successful because they would create duplicate values in the index, primary key or relationship, change the data in the field or fields and that contain duplicate data, remove the index or re define the index to permit duplicate entries and try again'. As I am sure you are all aware you have to use an index primary key field. When I delete the record it is written into the temporary audit table awaiting completion and because there is already the same autonumber index (due to testing with insert and editing) with the same numbers I am getting the above message. Has anyone had a simular problem and can anyone suggest a fix, I'm sorry if there is a very simple way to do this but i'm in brain freeze at the minute!

Thanks in advance.

Aine

PS I'm on Access 2000

 

[Allen Browne]

As you discovered, the primary key field from your original table cannot be
the primary key field of the audit table.

As the article at:
http://allenbrowne.com/AppAudit.html
explains (under "Create the Temporary Table and Audit Log Table"):
3. Open the temp table in Design view. Remove all indexes
(including the Primary Key), but do not delete the fields.

You can add another key field (e.g. AutoNumber) as the *last* field in the
audit table. (You don't need one in the temp table.)

 

[Aine McElroy]

Hi Allen,

Many thanks for your reply. Thank you I must have missed that step and rectifying it with what you suggested worked. I am still testing at the minute so hopefully I have done everything else right.

Many thanks again

Aine