I have a data entry form with a Save button that runs a SQL insert statement. If I enter data in the form and use the record navigation buttons, the record saves without any issues but when I click the button, I get the warning that I am about to append a record, which is fine, and then I get this error:
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key,or
relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit
duplicate entries and try again."
However, if I look in the table at this point, the new record is there. Also, the record selector icon remains a pencil, indicating that the record is being edited and has not been saved, even though it has. I've seen a few posts on this error but none where the error was given AND the record was actually saved.
Here is the table structure:
ID > Autonumber, PK
RegulationSetID > number, FK to an ID on another table
RegulationCode > short text
RegulationText > long text
RegulationReference > hyperlink
I have a unique index on the combination of RegulationSetID and RegulationCode to prevent duplicates. There are non-unique indexes on RegulationCode and RegulationSetID.
Here is the code on the 'Save' button:
DoCmd.RunSQL "INSERT INTO tblRegulation (RegulationSetID, RegulationCode, RegulationText, RegulationReference)" _
& " VALUES ([cboRegSet].value, [txtRegCode].value, [txtRegText].value, [txtRegReference].value)"
What am I missing?
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key,or
relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit
duplicate entries and try again."
However, if I look in the table at this point, the new record is there. Also, the record selector icon remains a pencil, indicating that the record is being edited and has not been saved, even though it has. I've seen a few posts on this error but none where the error was given AND the record was actually saved.
Here is the table structure:
ID > Autonumber, PK
RegulationSetID > number, FK to an ID on another table
RegulationCode > short text
RegulationText > long text
RegulationReference > hyperlink
I have a unique index on the combination of RegulationSetID and RegulationCode to prevent duplicates. There are non-unique indexes on RegulationCode and RegulationSetID.
Here is the code on the 'Save' button:
DoCmd.RunSQL "INSERT INTO tblRegulation (RegulationSetID, RegulationCode, RegulationText, RegulationReference)" _
& " VALUES ([cboRegSet].value, [txtRegCode].value, [txtRegText].value, [txtRegReference].value)"
What am I missing?