Sometimes it SQL Inserts, Sometimes it doesn't

[jclover]

Short version.

I have form that opens a linked form where data is populated automatically based on queries, and some data is entered. When this form closed I have SQL code that writes the various fields into the database, and then closes the form. The original for then recalcs so that the entered data now populated. Works grand...sometimes. Sometimes when you close the form, the data just doesn't write into the database due to a key violation (there's a field in the table that autopopulates so that each record has a unique ID). So you open the form again, re-enter your fields, and then it works. I can't figure out why it doesn't write every time.

Tell me what else I need to provide to help.

Update: Added Code

Dim strSQL As String
 
 
strSQL = "INSERT INTO tblMPR_Price([MPR_ID],[SKU]," & _
"[DeadOld],[DeadNew],[MAPOld],[MAPNew],[InvOld],[InvNew],[Rate],[Cost]) " & _
"VALUES (" & Me.[MPR_ID] & ",'" & Me.[SKU] & _
"','" & Me.[DeadOld] & "','" & Me.[DeadNew] & "','" & Me.[MAPOld] & _
"','" & Me.[MAPNew] & "','" & Me.[InvOld] & "','" & Me.[InvNew] & _
"','" & Me.[Rate] & "','" & Me.[Cost] & "');"
 
DoCmd.RunSQL strSQL

[jclover]

I figured out the problem. It was due to the relationship between the tables, with the main table linking to the other table with "one-to-many/Enforce Referential Integrity". I redefined the tables, as a one to one, no enforcing, and it solved it. Everything else runsfine still, but I don't enderstand the root of the problem, only that I fixed it.

Anyone want to take a quick minute to teach me what I did?

[Rabbit]

It sounds like when you tried to insert the record into the linked table, the record in the main table wasn't saved yet so there was no linked record. Enforcing a relationship will throw up an error if you try to save a linked record without a main record. So I usually toss in a save record before inserting a linked record.