Getting a Key Violation Error when trying to use an Update String from a form

Question

I have a form that allows people to add new employees but they need to be able to edit or update the existing employees as well.

So I added a button to allow them to make changes right on the form, click the update button and the record they were working on would be updated right away.

When I tested it however the string runs and even pops up a warning letting you know you are about to permanently change a record. But then it throws up an error stating "did not update record due to Key Violation"

I have included my "On Click" Event code

DoCmd.RunSQL "UPDATE EntList " & _
    "SET EntList.BusinessUnit = '" & Me.cboBUnit & "', EntList.EntityName = '" & Me.txtEntName & "', EntList.Position = '" & Me.txtPos & "', EntList.Location = '" & Me.cboLoc & "', EntList.Client = '" & Me.cboClient & "', EntList.Dept = '" & Me.cboDept & "', EntList.DistKey = '" & Me.txtDistKey & "', EntList.Salary = '" & Me.txtSalary & "', Entlist.Currency = '" & Me.cboCurrency & "', EntList.[SG&A] = '" & Me.txtSG_A & "', EntList.BillRate = '" & Me.txtBillRate & "', EntList.[Util%] = '" & Me.txtUtil_ & "', EntList.MeritDate = '" & Me.txtMeritDate & "', EntList.[Merit%] = '" & Me.txtMerit_ & "' " & _
    "WHERE EntList.EntityID = '" & Me.txtEntID.Value & "';"

I am wondering what I am missing that is causing this error.

What is the primary key of `EntList`? You should check whether your update will create a record that shares the primary key with another existing row. — Mike, Dec 11 '13 at 18:50
Primary Key is the one listed in the WHERE Clause. I am trying to update an existing record — Mr. Finn, Dec 11 '13 at 19:08
Put a break in your code. Examine the value of `Me.txtEntID.Value`. Does it look correct to you? — Mike, Dec 11 '13 at 19:13
I am not sure I follow? its the value present in a particular field in the Form. Could you elaborate — Mr. Finn, Dec 11 '13 at 19:16
Does the `EntList` table include a unique requirement for any other field (or combination of fields) other than the primary key? I will guess the answer is yes. — HansUp, Dec 11 '13 at 19:19
Yes there are multiple Foriegn Keys Business Unit, Currency, Location, Client, Dept. DistKey all are linked to Foriegn Keys — Mr. Finn, Dec 11 '13 at 19:21
Do you think that the issue is with the data Value of one of those foreign keys? Would that generate this kind of issue where the sql code runs but then it throws back this kind of error? — Mr. Finn, Dec 11 '13 at 19:22
One possibility is the `UPDATE` attempts to alter values in an existing row, and one of the updated values matches the value stored in another existing row in a field which has a unique constraint. I suggest you use a variable, *strUpdate*, to hold your `UPDATE` statement text. Then `Debug.Print strUpdate`, run the code, go to the Immediate window with Ctrl+g, copy the statement text, open a new query in the query designer, switch to SQL View, and paste in the `UPDATE` statement text. Then test that query in the designer to narrow down which field value(s) trigger the key violation error. — HansUp, Dec 11 '13 at 19:28
Yes, you can also trigger a key violation error if one of the update values does not satisfy a relationship declared for that field. — HansUp, Dec 11 '13 at 19:29
All the foriegn Keys are simply drop down list tables so EntList has a many -1 relationship with all of those. But I think I might have found the culprit. all of those FK relationships enforce matching so you can not enter a value that does not appear on the associated lookup table. one of those tables I had forgotten to populate with data — Mr. Finn, Dec 11 '13 at 19:33
Ok I think I have the reason why. The syntax I am using for the sql is treating everything as a string value I think but some of the values are numbers. for example Salary is a number not a string, but the '" & me.txtSalary & "' is usually what is used for a string right? — Mr. Finn, Dec 11 '13 at 20:00

HansUp · Accepted Answer · 2013-12-11T20:35:32.177

If I followed the comments correctly, you have resolved the key violation error which occurred because one of the update values did not satisfy the requirement of a defined relationship which enforces referential integrity. In that situation, Access reports the relationship violation as key violation.

And, with that problem resolved, you're now facing a type mismatch between an update value and the destination field.

Your UPDATE included quotes around every value it supplied. Likely the current error is because a destination field is numeric data type instead of text.

So you can examine the data type of each destination field and make sure your UPDATE includes quotes around the values for text fields, # around the values for Date/Time fields, and no delimiters around the values for numeric fields.

While that is possible, it's also time-consuming and error-prone. A better approach is to use a parameter query so you needn't fiddle with delimiters.

Here is an abbreviated sample of the approach I'm suggesting. You will have to extend it to include the other fields I left out.

Dim strUpdate As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

strUpdate = "UPDATE EntList AS e" & vbCrlf & _
    "SET e.BusinessUnit = pBusinessUnit, " & _
    "e.EntityName = pEntityName" & vbCrLf & _
    "WHERE e.EntityID = pEntityID;"
Debug.Print strUpdate

Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strUpdate)
qdf.Parameters("pBusinessUnit") = Me.cboBUnit.Value
qdf.Parameters("pEntityName") = Me.txtEntName.Value
qdf.Parameters("pEntityID") = Me.txtEntID.Value 
qdf.Execute dbFailOnError
Set qdf = Nothing
Set db = Nothing

There are a couple of components to the strUpdate string that I do not understand. would you be able to explain them for me? Why is there a "p" infront of some of the entries and I am not familiar with "vbCrLf" Could you explain what those are doing? — Mr. Finn, Dec 11 '13 at 20:53
Would it be more simple to import the form values to my VBA as variables and build the update string off of that? — Mr. Finn, Dec 11 '13 at 21:12
Ok Tested and verified That Has worked! Thank you very very much everyone for all of your assistance — Mr. Finn, Dec 11 '13 at 21:43

Getting a Key Violation Error when trying to use an Update String from a form

1 Answers1