By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,225 Members | 1,434 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,225 IT Pros & Developers. It's quick & easy.

Append error: table due to key violations

P: 86
I am trying to insert a record into a table and when i try to do so im getting an append error:

'Database' set 0 field(s) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violations....etc

what i wanted to do was grab the autonumber (aka Trouble #) along with the date, username, and the string "Ticket Created" and insert it into the table.

my previous problem as discussed in this thread http://bytes.com/topic/access/answer...-sql-statement has been solved already with the use of [ ] around date.

I have the following code for a new record

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command67_Click()
  2. On Error GoTo Err_Command67_Click
  3.  
  4.     DoCmd.GoToRecord , , acNewRec
  5.     'Setting the open date to current date
  6.     date_opened = date
  7.  
  8.     Call sqlstatement2("Ticket Created")
  9.     'Setting the Ticket Status to Open
  10.     Combo46 = "OPEN"
  11.  
  12.     [Forms]![User Problem Log]![Opened_By] = user_name
  13.  
  14.     Me!user.SetFocus
  15.  
  16.     If IsNull(user) Then
  17.  
  18.         Command67.Enabled = False
  19.         'disables the add button if end user is not selected.
  20.         'this is to prevent user from accidentally clicking add
  21.         'button twice which creates two new records.
  22.  
  23.     Else
  24.  
  25.         Command67.Enabled = True 'enable the add button
  26.  
  27.     End If
  28.  
  29. Exit_Command67_Click:
  30.     Exit Sub
  31.  
  32. Err_Command67_Click:
  33.     MsgBox Err.Description
  34.     Resume Exit_Command67_Click
  35.  
  36. End Sub
sqlstatement2 is the following code
trouble_no is a number field
date is a date/time field
user is a string field
notes is a memo field

Expand|Select|Wrap|Line Numbers
  1. Sub sqlstatement2(status As String)
  2.  
  3.     Dim strSQL As String
  4.  
  5.     strSQL = "INSERT INTO usr_problem_list "
  6.     strSQL = strSQL & "(trouble_no, [date], user, notes) "
  7.     strSQL = strSQL & "VALUES (" & Text30.Value & ", '" & Now() & "', '" & user_name & "', '" & status & "');"
  8.  
  9.     DoCmd.RunSQL strSQL
  10.  
this is the code i have for form load
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. '********** Setting the form to load waiting for a new ticket to be entered **********
  4. On Error GoTo Err_Command21_Click
  5.  
  6.     DoCmd.GoToRecord , , acNewRec
  7.  
  8.     If IsNull(Text30) Then
  9.         Command58.Enabled = False
  10.     End If
  11.  
  12. Exit_Command21_Click:
  13.     Exit Sub
  14.  
  15. Err_Command21_Click:
  16.     MsgBox Err.Description
  17.     Resume Exit_Command21_Click
  18. '********** Setting the form to load waiting for a new ticket to be entered **********
  19.  
  20. End Sub
now after i click the button, i get the append error.
Jan 17 '12 #1

✓ answered by Rabbit

That error usually means that you're trying to insert a record with the same primary key as another record in the table. I'm going to guess that you made trouble_no the primary key on usr_problem_list. Which means trouble_no must be unique.

Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,421
That error usually means that you're trying to insert a record with the same primary key as another record in the table. I'm going to guess that you made trouble_no the primary key on usr_problem_list. Which means trouble_no must be unique.
Jan 17 '12 #2

P: 86
im an idiot. i think its pulling the previous record trouble #.

i have to save the ticket first before inserting into table.

i just tried it and it worked with no problems.

sigh, i knew it was something stupid i forgot to do :P

thanks Rabbit!
Jan 17 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.