Problem appending to table with autonumber primary keys Allen Browne's Audit Trail

[babamc4]

I have a main form (mainformlung) with 5 subforms (followupacute, followuplate, biochemresults, haemresults and pftresults). I have copied Allen Browne's Audit Trail code (thanks to Allen Browne) and this is working great, edit, insert etc is working bar 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 with Allen Brownes Audit Trail. When I delete the record it is written into the temporary audit table but can't be written into the 'permanent' audTable as there is already the same autonumber index causing duplication. Has anyone had a similar problem and can anyone suggest a fix, I've looked at Allen Browne's tips and bugs section to see if I can get a fix but I can see nothing obvious. 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 experts!

PS. I'm using Access 2000.

[FishVal]

Hello.

I guess it is designed to work with single table audit.
For multiple tables you may create multiple audit tables or store table PK in non-indexed field of single audit table - sure you need an additional field to detect which table record is stored.
Or, what makes more sense - merge multiple "results" table to one. Do you have any special reason(s) to store data in multiple tables while it does fit the structure of single audit table?

Kind regards,
Fish

[babamc4]

Hello.

I guess it is designed to work with single table audit.
For multiple tables you may create multiple audit tables or store table PK in non-indexed field of single audit table - sure you need an additional field to detect which table record is stored.
Or, what makes more sense - merge multiple "results" table to one. Do you have any special reason(s) to store data in multiple tables while it does fit the structure of single audit table?

Kind regards,
Fish

Hi Fish

Thanks for replying. Unfortunately I had made just a little mistake (doh!) and I should not have put in a primary key autonumber in the temp audit table, sorry for that. With regards to your question re storing data in multiple tables as opposed to just one, I am not entirely sure what you mean here. I have just followed the way my DB is set up (ie one to many) is this what you mean?

Thanks
Aine

[FishVal]

... With regards to your question re storing data in multiple tables as opposed to just one, I am not entirely sure what you mean here...

I just guessed that as soon as you store deleted records from multiple tables in a single audit table they may have the same set of fields. If so, then the tables may be advantageously merged into a single one following database normalization rules.

Regards,
Fish

[babamc4]

I just guessed that as soon as you store deleted records from multiple tables in a single audit table they may have the same set of fields. If so, then the tables may be advantageously merged into a single one following database normalization rules.

Regards,
Fish

Hi Fish,

Thanks I had a look at that article that you sent. Yes you are quite correct re normalisation and my DBs are normalised (to the 3NF level), but if I was to merge into one table (which I will honestly say I don't know how to do in VBA) they will be extremely large and not very easy to read look at. My audit tables will be monitored by organisations (such as the FDA if you are from the states) and while it may be a good idea to 'hide' things (lol!) in the data it would be inappropriate for me to present them with large tables and/or reports. Does the fact that I have multiply audit tables (which still comply with 1 to many rule) mean that my DBs are no longer normalised?

Many thanks
Baba

[FishVal]

Hello, Baba.

• I suggest you to post results tables metadata to make our conversation more substantial.Her e is an example of how to post table MetaData :
  Table Name=tblStudent
  

  [i]Field; Type; IndexInfo[/i]
  StudentID; AutoNumber; PK
  Family; String; FK
  Name; String
  University; String; FK
  Mark; Numeric
  LastAttendance; Date/Time

• In general tables are not supposed to be "easy to read" - this is what queries/forms/reports, in one word user interface, are for.
• Performance may be a reason to store identically structured records in different tables. Is this your case?
• As far as I know, software has to be validated to satisfy FDA inspection. The issue seems to be interesting. If you have certain knowledge about it, then I would be glad to receive any links from you.

Regards,
Fish

[babamc4]

Hello, Baba.

• I suggest you to post results tables metadata to make our conversation more substantial.Her e is an example of how to post table MetaData :
  Table Name=tblStudent
  

  [i]Field; Type; IndexInfo[/i]
  StudentID; AutoNumber; PK
  Family; String; FK
  Name; String
  University; String; FK
  Mark; Numeric
  LastAttendance; Date/Time

• In general tables are not supposed to be "easy to read" - this is what queries/forms/reports, in one word user interface, are for.
• Performance may be a reason to store identically structured records in different tables. Is this your case?
• As far as I know, software has to be validated to satisfy FDA inspection. The issue seems to be interesting. If you have certain knowledge about it, then I would be glad to receive any links from you.

Regards,
Fish

Hi Fish,

Sorry for the delay in getting back to you, I have been working on another project that was given to me at the last minute so I've been very busy.

I agree with what you are saying re tables not being easy to read and I would like to give a report on what is in the table but unfortunately they will need to see the tables etc to ensure that there is no tampering (by me!).

I suppose that I try to keep certain data items in 'one box' so to say, such as biochemistry results seperate from the radiotherapy details, therefore the original tables are not related so I don't think I can have the audit tables combined into 1, but please correct me if I am wrong I am only doing this for 1.5yrs.

With regards to the FDA requirements and legislation, the catchall legislation being FDA, 21 CFR Part 11, http://www.fda.gov/ora/compliance_re.../ffinalcct.pdfthere is also the International Council of Harmonisation and Good Clinical Practive (ICH GCP) legislation http://www.emea.europa.eu/pdfs/human/ich/013595en.pdf The EU Directive, and our local legislation Control of Clinical Trials Act, (Ireland), so you can see we work in a very tightly regulated area and in as such you have to 'prove' that you aren't doing anything wrong as opposed to the assumption that there is nothing wrong.
The FDA themselves do not come over from the US and audit us, but do ask that our local regulatory bodies use their legislation if the trial orgininated in the US. I apologise if I have rambled on but it is not an easy topic to write about in a few short paragraphs. I have had to extensivily research all the legislations and condense into a report for my bosses even so the document was 6 pages long!

Before I start writing down all the table and field names, which ones (ie audit) tables are you interested.

Again many thanks for all your help and expertise.

Baba