Form on a loop? "duplicate values in the index, primary key, or relationship."

[mrcruz]

Form on a loop? "duplicate values in the index, primary key, or relationship."

Hi everyone, I am new to operating access. I have a form that appears to be on a loop of some sort. My form is linked to a table and query to search for previous participants. Before the form comes up a query comes up to search for the previous participant, but I am wanting to add a new participant so I have a button to select add new. Once on my main form I have it set to enter a new program participant ID and date of contact then select Add New Participant, and this is where access auto populates another participants information with a different number and data. I attempt to overwrite the old number and it doesn't work. Any thoughts?

Here is the code for creating a new record,

Private Sub cboSelectParticipant_AfterUpdate()
    Dim rs As Object
    If Me![cboSelectParticipant] = 0 Then
        DoCmd.GoToRecord , , acNewRec
    Else
        Set rs = Me.Recordset.Clone
        rs.findfirst "[VictimID] = " & Str(Me![cboSelectParticipant])
        Me.Bookmark = rs.Bookmark
    End If
End Sub

 

[theDBguy]

Re: Form on a loop? "duplicate values in the index, primary key, or relationship."

Hi. Welcome to AWF! I'm not sure there's enough information there to help us figure out what you're trying to do. Are you able to post a sample copy of your db?

 

[mrcruz]

Re: Form on a loop? "duplicate values in the index, primary key, or relationship."

No I am not able to post a sample copy. When I go into the back end to try and create a new participant manually I get this “The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship." But I am not sure why my participant table is not letting me create a new record.

I tried to compact & repair database and that did not work.

 

[theDBguy]

Re: Form on a loop? "duplicate values in the index, primary key, or relationship."

No I am not able to post a sample copy. When I go into the back end to try and create a new participant manually I get this “The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship." But I am not sure why my participant table is not letting me create a new record.

I tried to compact & repair database and that did not work.

Maybe post some screenshots then?

 

[CJ_London]

Re: Form on a loop? "duplicate values in the index, primary key, or relationship."

this doesn't look right

Set rs = Me.Recordset.Clone

should be

Set rs = Me.RecordsetClone

 

[isladogs]

Re: Form on a loop? "duplicate values in the index, primary key, or relationship."

I've never used the Str function in this way but it seems logical that you would need text delimiters assuming VictimID is a text field. I've made a few other changes as well. Try this

Private Sub cboSelectParticipant_AfterUpdate()
    Dim rs As DAO.Recordset
    If Me.cboSelectParticipant = 0 Then
        DoCmd.GoToRecord , , acNewRec
    Else
        Set rs = Me.RecordsetClone
        rs.findfirst "VictimID = '" & Str(Me.cboSelectParticipant) & "'"
        Me.Bookmark = rs.Bookmark
    End If
End Sub

However, if its a number field then why use Str?

 

[CJ_London]

Re: Form on a loop? "duplicate values in the index, primary key, or relationship."

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship." But I am not sure why my participant table is not letting me create a new record.

the message is telling you exactly why you can't create a new record. Either one or more of your fields has a 'no duplicates' index or the record is dependant on a field value in another table which does not exist.

Perhaps you have first name, last name fields and the last name has a no duplicates index. You already have a record for Fred Smith and now you are trying to add a John Smith,

Or perhaps you have a primary key made up of the participants initials - you already have a participant with the initials FS and now you are trying to add another one with the same initials

Can't really speculate any more without more information. Recommend you follow DBG's advice and post some screenshots of your table design and relationships.

 

[MajP]

Re: Form on a loop? "duplicate values in the index, primary key, or relationship."

this doesn't look right
Set rs = Me.Recordset.Clone
should be
Set rs = Me.RecordsetClone

Actually that is fine. All dao recordsets have a clone method, but a form has a recordsetClone property. So in this case either should work.