Unexpected row inserted in a code table

[fearless]

I have the following tables, tblAudit and tblCriteria. The first records companies and period of audit, the second contains the criteria against which they will be audited. The audit period determines which criteria apply.
Results are recorded in a third table, tblAuditResults.

Relationships
tblAudit 1:M tblAuditResults M:1 tblCriteria

My problem is when I try to add a new result by selecting a criteria via a combo box a new row is inserted in the criteria table as well as in the result table. Inserting a row in the criteria table is wrong.

I have attached the database for you to look at.

Thanks for any advice you can offer.
Fearless

 

[andrew93]

Hi

In your form you have 4 fields : 'CriteriaText', 'CriteriaKey', 'CriteriaNB' and 'ExceptionNB' into which you can enter data. 2 of these fields are stored in 'tblCriteria' (i.e. 'CriteriaText' & 'CriteriaNB'), so when you try to enter data into the form it is trying to add new records to 'tblCriteria'. This is the problem you have described, but I suspect the issue runs deeper than that.

In your 'tblAuditResults' there are 4 fields but you only have 2 of the fields on the form. If you are trying to enter the audit results, then you should have all 4 fields on the form. If you don't want to enter data into all 4 fields, then maybe you need to change your data structures.

You need to have a think about what data you are trying to capture and make sure your data structures accurately reflect what it is you are trying to do. If you aren't sure have a read of the FAQ's - from memory there is an article on 'normalising' your data.

Assuming your data structures are correct (I haven't given an opionion on that, although I suspect they need changing) then you need to alter your form and possibly your query. On your form, make sure you are only editing fields sourced from tblAuditResults if you don't want to create new records in tblCriteria. View the properties for each item on the form and make sure the control source points to a field from the query that is part of 'tblAuditResults'. If you need to enter data into one of the other fields, then you might want to change your data structures. If you want to see the data from the other table, without editing it, then you can include it on your form but set the enabled property to 'No' for each item you don't want to edit. Lastly, giving a control on the form the same name as the control source of another item will lead to confusion - it's not good practice.

HTH, Andrew

 

[fearless]

Thanks for your responses, I am studying your suggestions now.