Changes you requested to the table were not successful...

[PRMiller2]

Hi all,

I'm receiving the following error: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entried and try again."

I've checked my fields, removed indexes, and verified that I am not trying to modify the autonumber primary key of any table.

This happens on the button click event of an unbound form, "frm_ClientMaintenance." Here's the code:

Private Sub cmdSave_Click()
On Error GoTo cmdSave_Click_Err

    Dim datStartDate As Date
    Dim datEndDate As Date
    Dim db As DAO.Database
    Dim intActive As Integer
    Dim intClientID As Integer
    Dim rs As DAO.Recordset
    Dim strClientName As String
    Dim strNotes As String
    Dim strSQL As String

    Select Case g_intMenuOption
        Case 1 ' Add new
            Set db = CurrentDb
            
            datStartDate = Format(Me.txtStartDate, "mm/dd/yyyy")
            datEndDate = Format(Me.txtEndDate, "mm/dd/yyyy")
            intActive = Nz(Me.chkActive, "-1")
            strClientName = Me.txtClientName
            strNotes = Nz(Me.txtNotes, " ")
            
            strSQL = "INSERT INTO client" & _
                     " (clientName, start, end, active, notes, prospect)" & _
                     " Values ('" & strClientName & "', #" & datStartDate & "#, #" & datEndDate & "#," & _
                     " " & intActive & ", '" & strNotes & "', -1);"
                       
            DoCmd.RunSQL strSQL
            DoCmd.GoToRecord , , acFirst
            DoCmd.GoToRecord , , acLast
            
            g_intClientID = DMax("[clientID]", "client")
            
            strSQL = "INSERT INTO personClient" & _
                     " (contactID, clientID)" & _
                     " Values (" & g_intContactID & "," & g_intClientID & ");"
            
            DoCmd.RunSQL strSQL
            g_intMenuOption = 2
            
            MsgBox "Prospect details saved."
            DoCmd.Close
            
        Case 2 ' Edit existing
            DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
            MsgBox "Record saved.", vbOKOnly, " "
    End Select

cmdSave_Click_Exit:
    Exit Sub

cmdSave_Click_Err:
    MsgBox Error$
    Resume cmdSave_Click_Exit

End Sub

The case statement being executed, by design, is always case 1. I've added a breakpoint at the top and followed it all the way through, and nothing seems to error. This only occurs when I try to close the form, either through a button or just right-clicking to close the form. I have to hit <ESCAPE> to clear the fields.

Here are properties of the tables in question:

[b]client[/b]
[i]clientID[/i] AutoNumber, increment, Indexed Yes (No duplicates)
[i]start[/i] Date/Time, not indexed
[i]end[/i] Date/Time, not indexed
[i]active[/i] Yes/No, not indexed
[i]notes[/i] Text, not indexed
[i]prospect[/i] Yes/No, not indexed

[b]personClient[/b]
[i]pk_PersonClientID[/i] AutoNumber, increment, Indexed Yes (No duplicates)
[i]contactID[/i] Number, not indexed
[i]clientID[/i] Number, not indexed

I should mention that this is a database created by someone I hired. I had to let him go due to lack of progress, and I've since been overhauling and adding substantial additional functionality... hence the inconsistencies in naming conventions, which I hope to rectify after this last bit is functional.

Thanks!

 

[PRMiller2]

EDIT: I should mention that, even though I receive the error message, a manual check of each of the tables indicates that the records have, in fact, been inserted. I must be missing something obvious here, I'm just not sure what.

 

[PHV]

Seems like your form IS bound ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PRMiller2]

That does seem to be the only possibility. I do populate fields on the form from a table or two on load, depending on certain variables. However, I inserted this line

Me.RecordSource = ""

immediately after the DoCmd.RunSQL event, but that hasn't seemed to help. I also tried the method suggested here:

http://www.codeforexcelandoutlook.com/blog/2009/06/unbind-an-access-form/

but was again hit with the error.

Any suggestions?

 

[PRMiller2]

Disregard, got it solved. I failed to debug the code in the link I posted previously. If I had been prudent, I would have done so and realized it wasn't being called. I actually replaced it with a slightly more efficient solution, found here:

http://www.daniweb.com/code/snippet216301.html

Works like a charm!