Record Save Error (1 Viewer)

B99

Member
Local time
Today, 17:15
Joined
Apr 14, 2020
Messages
30
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?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:15
Joined
Oct 29, 2018
Messages
21,358
Hi. Sounds like the record is already saved to the table before your code runs. Is the data on a subform and your button is on the main form, for example?
 

B99

Member
Local time
Today, 17:15
Joined
Apr 14, 2020
Messages
30
It's all on one form. Not sure if this matters but the Regulation Set is a combo box that has a record source in a different table.

I had some other code on the form on the On Close and On Unload events that I just cleared out. Now I can enter data on the form and the save button works. But after it is saved, if I try to navigate away from the record, I get the error again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:15
Joined
Oct 29, 2018
Messages
21,358
It's all on one form. Not sure if this matters but the Regulation Set is a combo box that has a record source in a different table.

I had some other code on the form on the On Close and On Unload events that I just cleared out. Now I can enter data on the form and the save button works. But after it is saved, if I try to navigate away from the record, I get the error again.
You might consider posting a demo version of your form. It would be hard to guess what's going on without being able to see it in action.
 

B99

Member
Local time
Today, 17:15
Joined
Apr 14, 2020
Messages
30
Attached is what I have for this form along with some test data. I'm sure it's something small that I've missed somewhere.

Thank you for taking a look!
 

Attachments

  • Form Test.zip
    81 KB · Views: 274

theDBguy

I’m here to help
Staff member
Local time
Today, 14:15
Joined
Oct 29, 2018
Messages
21,358
Attached is what I have for this form along with some test data. I'm sure it's something small that I've missed somewhere.

Thank you for taking a look!
Hi. Thanks. I see two forms. Which one should I open, and can you please give me a step by step instruction on how to duplicate the problem? Cheers!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:15
Joined
Oct 29, 2018
Messages
21,358
Hi. Thanks. I see two forms. Which one should I open, and can you please give me a step by step instruction on how to duplicate the problem? Cheers!
Hi. I gave a quick try anyway. Please see attached and let's discuss it.
 

Attachments

  • Form Test.zip
    37.1 KB · Views: 279

B99

Member
Local time
Today, 17:15
Joined
Apr 14, 2020
Messages
30
Sorry! I got distracted and wasn't thinking. frmRegulation_x is the offender. Open that form, which is bound to tblRegulation. You can select a Regulation Set from the combo box, then enter anything in the fields for Regulation, Text and Reference, as long as the combination of Regulation Set and Regulation ID are unique (ID is a little confusing; it means the regulation code, not a DB ID). Once you have data entered, you can try saving with the button, then try navigating away from the record.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:15
Joined
Oct 29, 2018
Messages
21,358
Sorry! I got distracted and wasn't thinking. frmRegulation_x is the offender. Open that form, which is bound to tblRegulation. You can select a Regulation Set from the combo box, then enter anything in the fields for Regulation, Text and Reference, as long as the combination of Regulation Set and Regulation ID are unique (ID is a little confusing; it means the regulation code, not a DB ID). Once you have data entered, you can try saving with the button, then try navigating away from the record.
Okay, thanks. Sounds good. Please give the attached file I posted above a try and let me know how it goes.
 

B99

Member
Local time
Today, 17:15
Joined
Apr 14, 2020
Messages
30
That works! And I am acutely aware that I am in over my head and what I thought I knew was way off.

I thought the me.dirty property was only for existing records. So in a scenario like this, there is never a need to do a SQL insert?

I also tried the update and it didn't work either. It looks like it updates, but when I try to navigate away from the record, I get a write conflict error that another user has changed the record. I tried the me.dirty trick and it didn't work either.

I'm really confused because I spent a lot of time going through tutorials and thought I had modeled this the right way.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:15
Joined
Oct 29, 2018
Messages
21,358
That works! And I am acutely aware that I am in over my head and what I thought I knew was way off.

I thought the me.dirty property was only for existing records. So in a scenario like this, there is never a need to do a SQL insert?

I also tried the update and it didn't work either. It looks like it updates, but when I try to navigate away from the record, I get a write conflict error that another user has changed the record. I tried the me.dirty trick and it didn't work either.

I'm really confused because I spent a lot of time going through tutorials and thought I had modeled this the right way.
Hi. Basically, since you're using a bound form, there is no need to use an action query to add or edit the record, because the form is already doing it for you. You could use SQL statements to update or add records to related tables not bound to the same form. Hope that helps...
 

zeroaccess

Active member
Local time
Today, 16:15
Joined
Jan 30, 2020
Messages
671
As theDBguy alluded to, this approach is only needed for an unbound form, which is maybe not what you were going for. The first error is caused by trying to save the primary key to a record that already has a primary key - because of the default autosave functionality of Access. The second error (write conflict) is related in that the form received a save command after another save was already done, because "another user" (Access) saved the record first.
 

B99

Member
Local time
Today, 17:15
Joined
Apr 14, 2020
Messages
30
OK, that makes sense. Not sure how that didn't click earlier but maybe I'm not as far off as I feared. I was getting nervous when something so simple didn't work how I expected.

Thank you theDBGuy and zeroaccess!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:15
Joined
Oct 29, 2018
Messages
21,358
OK, that makes sense. Not sure how that didn't click earlier but maybe I'm not as far off as I feared. I was getting nervous when something so simple didn't work how I expected.

Thank you theDBGuy and zeroaccess!
Hi. You're welcome. We're happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom