Key value violation with macro append from spreadsheet

hello,

I am having problems with an append from a spreadsheet into a pre-populated table. I am using a macro to do this.



The table is in the middle of a hierarchy of tables. For example:



table 1 - Audit - key: AuditNumber & AuditName

table 2 - Process - key: AuditNumber & AuditName & ProcessRef

table 3 - Risk - key: AuditNumber & AuditName & ProcessRef & RiskRef

table 4 - Controls - key: AuditNumber & AuditName & ProcessRef & RiskRef & ControlRef

table 5 - Control tests - key: AuditNumber & AuditName & ProcessRef & RiskRef & ControlRef & TestRef



The front end of the database has a form for each table. Once you create an Audit in table 1 you can create multiple Processes underneath this one audit. Underneath a process you can create multiple Risks...... hence a hierarchy.



I am trying to append data to table 4 - controls. I have copied the format exactly into excel. I have also prepopulated the Audit, Process and Risk data in the required tables.



The reason I want this functionality is to enable team members to populate control information (oftentimes up to 25) in excel and then upload them easily into the database.



The macro I have created will work when created a new table. but when I point it at the existing table 4 - Controls I get the Key value violation error.



Does anybody have any ideas?



Thanks for your help in advance.



J

Key value violation with macro append from spreadsheet