Append query in VBA gets key violation (1 Viewer)

B

Bonhomme Septheure

Registered User.

Local time

Today, 15:18

Joined

Apr 27, 2012

Messages

14

• Nov 30, 2012

• #1

Hi

I'm trying to run an "insert into" query as a before update event in a form. The code is the following:

Code:Copy to clipboard

DoCmd.RunSQL "INSERT INTO tblProjectEntityLocation ( ProjectEntityID, EntityLocationID ) VALUES (" & Me![txtProjectEntityID] & "," & y & ");"

I get a key violation error when this runs. I tried running the same code with actual number values in place of Me![txtProjectEntityID] and the variable y and no key violation occured.

I know that Me![txtProjectEntityID] and the variable y have values because I have message boxes appear before running the sql statement to check for values.

Any ideas? By the way, the table I'm putting a new record into isn't the one the form is based on.

Thanks!

 

pr2-eugin

Super Moderator

Local time

Today, 20:18

Joined

Nov 30, 2011

Messages

8,494

• Nov 30, 2012

• #2

Try CurrentDB.Execute instead of DoCmd.RunSQL.. See what happens..

 

B

Bonhomme Septheure

Registered User.

Local time

Today, 15:18

Joined

Apr 27, 2012

Messages

14

• Nov 30, 2012

• #3

Hi, Paul

Thanks for the quick reply! I tried using CurrentDb.Execute instead of DoCmd.RunSQL. The good news is I didn't get the key violation error. The bad news is that no record got appended to the table when used Me![txtProjectEntityID] and the variable y in the SQL statement. When I replace these with actual number values in the SQL statement and use CurrentDb.Execute the record gets appended.

I've tried the SQL statement with just variables, and with just one variable for just one field and that doesn't work either.

Odd.

 

B

Bonhomme Septheure

Registered User.

Local time

Today, 15:18

Joined

Apr 27, 2012

Messages

14

• Nov 30, 2012

• #4

Hi,

I figured it out. The table I was trying to append to had both of the fields I was trying to write values to each linked to a specific field in each of two respective tables in relationships. I deleted one of the relationships and the query worked o.k.

 

jdraw

Super Moderator

Staff member

Local time

Today, 15:18

Joined

Jan 23, 2006

Messages

15,379

• Nov 30, 2012

• #5

What was the purpose of the relationship you deleted?
You may have caused more harm than you think.

 

B

Bonhomme Septheure

Registered User.

Local time

Today, 15:18

Joined

Apr 27, 2012

Messages

14

• Dec 3, 2012

• #6

Hi, jdraw

No harm done. Since deleting the relationship...and later restoring it....I realized that the error stemmed from running the insert into sql statement in the BeforeUpdate event. The key violation occurred because I was trying to append a record containing data in a field that had not yet been written to the key field in that table underlying my form with which I had set up the relationship we've been talking about.

I moved the append query to the AfterUpdate event, making sure that the key field in the table underlying my form contained the data I was putting into a second table with the append query, resulting in no more key violation errors.

I'm a bit embarrassed, but there it is.

 

jdraw

Super Moderator

Staff member

Local time

Today, 15:18

Joined

Jan 23, 2006

Messages

15,379

• Dec 3, 2012

• #7

Glad you have it resolved. You usually do the validation in the Before Update since that is the last place to do so before the record is written.