Append error: table due to key violations

[Jeffrey Tan]

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

Private Sub Command67_Click()
On Error GoTo Err_Command67_Click
 
    DoCmd.GoToRecord , , acNewRec
    'Setting the open date to current date
    date_opened = date
 
    Call sqlstatement2("Ticket Created")
    'Setting the Ticket Status to Open
    Combo46 = "OPEN"
 
    [Forms]![User Problem Log]![Opened_By] = user_name
 
    Me!user.SetFocus
 
    If IsNull(user) Then
 
        Command67.Enabled = False
        'disables the add button if end user is not selected.
        'this is to prevent user from accidentally clicking add
        'button twice which creates two new records.
 
    Else
 
        Command67.Enabled = True 'enable the add button
 
    End If
 
Exit_Command67_Click:
    Exit Sub
 
Err_Command67_Click:
    MsgBox Err.Description
    Resume Exit_Command67_Click
 
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

Sub sqlstatement2(status As String)
 
    Dim strSQL As String
 
    strSQL = "INSERT INTO usr_problem_list "
    strSQL = strSQL & "(trouble_no, [date], user, notes) "
    strSQL = strSQL & "VALUES (" & Text30.Value & ", '" & Now() & "', '" & user_name & "', '" & status & "');"
 
    DoCmd.RunSQL strSQL
 

this is the code i have for form load

Private Sub Form_Load()
 
'********** Setting the form to load waiting for a new ticket to be entered **********
On Error GoTo Err_Command21_Click
 
    DoCmd.GoToRecord , , acNewRec
 
    If IsNull(Text30) Then
        Command58.Enabled = False
    End If
 
Exit_Command21_Click:
    Exit Sub
 
Err_Command21_Click:
    MsgBox Err.Description
    Resume Exit_Command21_Click
'********** Setting the form to load waiting for a new ticket to be entered **********
 
End Sub

now after i click the button, i get the append error.

[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.

[Jeffrey Tan]

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!