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

mrcruz

Registered User.
Local time
Today, 09:59
Joined
Aug 28, 2019
Messages
2
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,

Code:
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

I’m here to help
Staff member
Local time
Today, 10:59
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 09:59
Joined
Aug 28, 2019
Messages
2
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

I’m here to help
Staff member
Local time
Today, 10:59
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 17:59
Joined
Feb 19, 2013
Messages
16,553
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

MVP / VIP
Local time
Today, 17:59
Joined
Jan 14, 2017
Messages
18,186
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

Code:
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?
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 17:59
Joined
Feb 19, 2013
Messages
16,553
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

You've got your good things, and you've got mine.
Local time
Today, 13:59
Joined
May 21, 2018
Messages
8,463
Re: Form on a loop? "duplicate values in the index, primary key, or relationship."

Code:
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:59
Joined
Feb 19, 2002
Messages
42,971
Re: Form on a loop? "duplicate values in the index, primary key, or relationship."

I am confused by the question so I'm not sure if this is what you are looking for but I think you want to use the NotInList event of the combo. So on your form, you type into the combo and the item you are looking for is not in the list so the NotInList event activates. You would place code in that event to open the form where participants are added. You then add the participant and close the form. Access will then update the combo list and your new participant will be available.

I think the code you posted originally has nothing to do with how to solve this problem unless of course, I am the one who doesn't understand the problem:)
 

Users who are viewing this thread

Top Bottom