I’ve been tasked with creating an audit database using forms so I’m trying to do some research on what is the best practice for what I’m doing.

Here is the basic structure of my database; I have two tables for simplicity let’s call them “Audit Detail” and “Error Detail”.

The first table (“Audit Detail”) contains basic information such as:


·Audit Number (Autonumber,primary key)
·Audit pass/fail status

The second table (“Error Detail”) contains error information for each audit such as:
·Error Number (Autonumber,primary key)
·Audit Number (from above table)
·Document Type
·Error Type
·Solution

I’m running into some issues in setting up the two tables. My problem is that one audit can have multiple errors or zero errors, and if there are errors each error field is dependent on the other field. For example if the user selects a document type of “Address” the error type listed in the combo box on the form will refresh to only show say maybe “Incorrect Street” or “Incorrect Zip”. This same logic will flow through to the “Solution” combo box, such as “Zip Updated”.

I’ve tried two different approaches. I created a subform which was simply a single from view of my “Error Detail” table. This worked fine, except I could not seem to add multiple errors for a particular audit. I then tried to create a subform which had independent combo boxes fed by queries which I then used to update my “Error Detail” table, but ran into write conflicts.

Any suggestions or example databases out there?